Properly format an ActiveRecord query with a subquery in Postgres

Question

I have a working SQL query for Postgres v10.

SELECT * 
FROM 
    (
    SELECT DISTINCT ON (title) products.title, products.* 
    FROM "products" 
) subquery
WHERE subquery.active = TRUE AND subquery.product_type_id = 1
ORDER BY created_at DESC

With the goal of the query to do a distinct based on the title column, then filter and order them. (I used the subquery in the first place, as it seemed there was no way to combine DISTINCT ON with ORDER BY without a subquery.

I am trying to express said query in ActiveRecord.

I have been doing

Product.select("*")
    .from(Product.select("DISTINCT ON (product.title) product.title, meals.*"))
    .where("subquery.active IS true")
    .where("subquery.meal_type_id = ?", 1)
    .order("created_at DESC")

and, that works! But, it's fairly messy with the string where clauses in there. Is there a better way to express this query with ActiveRecord/Arel, or am I just running into the limits of what ActiveRecord can express?

Ilya Konyukhov · Accepted Answer · 2018-10-26 02:13:38Z

I think the resulting ActiveRecord call can be improved.

But I would start improving with original SQL query first.

Subquery

SELECT DISTINCT ON (title) products.title, products.* FROM products

(I think that instead of meals there should be products?) has duplicate products.title, which is not necessary there. Worse, it misses ORDER BY clause. As PostgreSQL documentation says:

Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first

I would rewrite sub-query as:

SELECT DISTINCT ON (title) * FROM products ORDER BY title ASC

which gives us a call:

Product.select('DISTINCT ON (title) *').order(title: :asc)

In main query where calls use Rails-generated alias for the subquery. I would not rely on Rails internal convention on aliasing subqueries, as it may change anytime. If you do not take this into account you could merge these conditions in one where call with hash-style argument syntax.

The final result:

Product.select('*')
       .from(Product.select('DISTINCT ON (title) *').order(title: :asc))
       .where(subquery: { active: true, meal_type_id: 1 })
       .order('created_at DESC')

Collectives™ on Stack Overflow

Properly format an ActiveRecord query with a subquery in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related