CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4activerecord
Neologis
Neologis

Reputation: 157

Not able to select distinct row from each group using ActiveRecord with PostgreSQL in Rails

I have a small problem with my ActiveRecord query.
I have a Product model, which looks like:

#<Product id: nil, name: nil, price: nil, order_id: nil, created_at: nil, updated_at: nil, restaurant_id: nil>

Now I want to select DISTINCT on all names, and get all Product's attributes back.

I tried:

@products = Product.where(restaurant_id: !nil).group("products.name").order("name")

but I got this error:

PG::GroupingError: ERROR:  column "products.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  "products".* FROM "products"  WHERE "products"."rest...
                ^
: SELECT  "products".* FROM "products"  WHERE "products"."restaurant_id" = 1 GROUP BY products.name

Ok, then I added product_id to my query:

@products = Product.where(restaurant_id: !nil).group("products.name, products.id").order("name")

but this query returns Products with duplicated names. So I tried this too:

@products = Product.where(restaurant_id: !nil).select("DISTINCT(NAME)").order("name")

but in return I got only Product record with id and name only (it's obvious), so If this query returned correct set, I added attributes which I need later:

@products = Product.where(restaurant_id: !nil).select("DISTINCT(NAME), restaurant_id, price").order("name")

And it returns also duplicated names.

Do you have any solution or idea, how to fix this query for PostgreSQL?
I'm used to writting query like this (on MySQL) and it's correct:

@products = Product.where(restaurant_id: !nil).select("DISTINCT(NAME), restaurant_id, price").order("name")

Why does PostreSQL not accept that query?

Upvotes: 3

Views: 4083

Answers (1)

Arup Rakshit
Arup Rakshit

Reputation: 118299

You should write the query as :-

Product.where
       .not(restaurant_id: nil)
       .select("DISTINCT ON(name) name, restaurant_id, price, updated_at")
       .order("updated_at, name")

As per the official documentation SELECT DISTINCT ON ( expression [, ...] )

keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:

SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;

retrieves the most recent weather report for each location. But if we had not used ORDER BY to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.

Upvotes: 4

Related Questions