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

Question

我需要这个对 Rails 友好的 SQL 查询:

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');

到目前为止，这是我的 Rails 查询:

@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]")

我想做的事:

实现 IF有条件才能管理店铺availability_offset如果它的值为 NULL。

实现 DATE_ADD()函数能够添加availability_offset值到日期 release date .

如果不使用 SQL sintax，只使用 ActiveRecord，我该如何做到这一点？谢谢!

Answer 1

您只需将您的 SQL 选择复制到 Rails ActiveRecord 选择中。尝试这样做:

@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]")

为您 if select中的语句，我使用别名列进入 store_availability_offset .我希望这对你有帮助。