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

Question

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!

I also need a DATE_ADD in Ruby (not Rails though) - bounty added — Some Guy
– Some Guy, Commented Oct 26, 2015 at 20:02
Have you considered storing 0 instead of NULL in 'store.availability_offset' ? — Nox
– Nox, Commented Nov 2, 2015 at 16:32

akbarbin · Accepted Answer · 2015-10-27 01:59:06Z

1

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.

answered Oct 27, 2015 at 1:59

akbarbin

5,1051 gold badge31 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Renaud Kern · Accepted Answer · 2015-11-02 13:15:47Z

1

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

edited Nov 2, 2015 at 13:15

answered Nov 2, 2015 at 13:09

Renaud Kern

1,15610 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related