I need this SQL query Rails-friendly:
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 query up to now:
@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 the IF
conditional in order to manage the stores availability_offset
in case its value is NULL.
implement the DATE_ADD()
function to be able to add the availability_offset
value to the date release date
.
How can I do that without using SQL sintax, just ActiveRecord? Thank you!
You just copy your select of SQL into Rails ActiveRecord select. 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
statement in select, I use alias column into store_availability_offset
. I hope this help you.
I would place the if
conditional and the date_add()
in the Product class, override the release_date
attribute and keep the ActiveRecord query as you wrote it:
class Product < ActiveRecord::Base
def release_date
read_attribue(:release_date) + (availability_offset ||0)
end
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With