Ruby on rails: execute SQL function DATE_ADD () and IF conditional

Question

I need this Rails-friendly SQL query:

SELECT *, IF (store.availability_offset IS NULL, DATE_ADD(product.release_date, INTERVAL 0 DAY), DATE_ADD(product.release_date, INTERVAL store.availability_offset DAY)) FROM products JOIN stores ON product.store_id = stores.id WHERE (DATE_ADD(products.release_date, INTERVAL stores.availability_offset DAY) BETWEEN '2013-06-12' AND '2014-07-12' OR DATE_ADD(products.release_date, INTERVAL 0 DAY) BETWEEN '2013-06-12' AND '2014-07-12'); 

This is my Rails request so far:

 @products = Product.joins(:store).where("products.release_date >= :start_date AND products.publishing_date <= :end_date)", :start_date => params[:search][:start_date], :end_date => params[:search][:end_date]") 

What I want to do:

• implement a conditional IF to manage availability_offset repositories if its value is NULL.

• implement the DATE_ADD() function to add the availability_offset value to the release date .

How can I do this without using SQL sintax, just ActiveRecord? Thanks!

Answer 1

You simply copy your SQL selection into Rails ActiveRecord. Try to do this:

 @products = Product.select('products.*, IF(store.availability_offset IS NULL, DATE_ADD(products.release_date, INTERVAL 0 DAY), DATE_ADD(products.release_date, INTERVAL stores.availability_offset DAY)) AS store_availability_offset') .joins(:store).where("products.release_date >= :start_date AND products.publishing_date <= :end_date)", :start_date => params[:search][:start_date], :end_date => params[:search][:end_date]") 

For your if in select, I use the alias column in store_availability_offset . Hope this helps you.

Answer 2

I would put if conditional and date_add() in the Product class, overriding the release_date attribute and saving the ActiveRecord request when you wrote it:

 class Product < ActiveRecord::Base def release_date read_attribue(:release_date) + (availability_offset ||0) end end