CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
chrismanderson
chrismanderson

Reputation: 4813

Properly format an ActiveRecord query with a subquery in Postgres

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?

Upvotes: 1

Views: 1082

Answers (1)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2791

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

Upvotes: 2

Related Questions