SQL query to Rails 4

Question

I'm having a hard time to convert this query in rails. I have two models Category and Product. Each category has many products. We can get the category of a particular product by product.category_id. Can someone help me to convert this query to rails 4? Thanks.

---

select * from category
where not exists 
(
 select from product
 where product.category_id = category.id 
 and product.validity_time >= now() - interval '1 month'
)

Answer 1

Rails convenience query methods are great for a lot of use cases but they do not provide exposure to the full scope of what any given database can do. The Rails team has continued to improve this top level DSL to offer better visibility for the more used functionality (For example Rails 5 implemented where.not and Rails 6 added where.missing).

That being said for more complex queries you will need to dive a little deeper than the convenience methods provided by ActiveRecord::QueryMethods to achieve your goal.

To implement this type of query you are going to have to use a String or use Arel (The underlying query assembler for rails). I have a distaste for String SQL in rails so I will provide the Arel solution here.

If you would like the exact SQL:

product_table = Product.arel_table
category_table = Category.arel_table
Category.where(
  Arel::Nodes::Not.new(
    Arel::Nodes::Exists.new(
      product_table.project(Arel.star).where(
        product_table[:category_id].eq(category_table[:id]).and(
          product_table[:validity_time].gteq(
            Arel::Nodes::Subtraction.new(
              Arel::Nodes::SqlLiteral.new('now()'),
              Arel::Nodes::SqlLiteral.new("interval '1 month'")
        )))
      )))
)

OTHER OPTIONS: These options should produce the same result (with different SQL) and the implementation and assembly is a bit simpler and easier to comprehend.

1. WHERE NOT IN ()

product_table = Product.arel_table
category_table = Category.arel_table
Category.where(
  category_table[:id].not_in(
    product_table.project(product_table[:category_id])
      .where(
          product_table[:validity_time].gteq(Time.now - 1.month)
      )
  )
)

The SQL that is produced should be akin to:

SELECT categories.*
FROM categories
WHERE 
  categories.id NOT IN ( 
     SELECT products.category_id
     FROM products
     WHERE 
       products.validity_time >= [RUN TIME - 1 MONTH]
  )

2. Multi-conditional Join

product_table = Product.arel_table
category_table = Category.arel_table
join = category_table.join(product_table).on(
  products_table[:category_id].eq(category_table[:id]).and(
    product_table[:validity_time].gteq(
      Arel::Nodes::Subtraction.new(Arel::Nodes::SqlLiteral.new('now()'),
        Arel::Nodes::SqlLiteral.new("interval '1 month'")))))
Category.joins(join.join_sources).where(products: {id: nil }).distinct

SQL

SELECT DISTINCT categories.*
FROM 
  categories
  LEFT OUTER JOIN products ON products.category_id = categories.id
    AND products.validity_time >= now() - interval '1 month'
WHERE 
  products.id IS NULL