Group with ActiveRecord and Postgres

Question

I've seen several answers, but couldn't find anything that helps me

I have a Model which has a country column and a city column.

doing some SQL query, I want to add to that query a group by first country and for each country to group by city

Model.where("name LIKE ?", "%#{query}%").group(["country", "city"])

but I get

PG::GroupingError: ERROR:  column "models.id" must appear in the GROUP BY clause or be used in an aggregate function

and I am not sure what to do, since I don't want to group by the ID, just by country and city

I am sure I am missing something here, not sure what

using the fields, as suggested with

 Model.where("name LIKE ?", "%#{query}%").group("country, city").select("country, city")

still gives the same error

Error is saying - what you should do. Try this Model.where("name LIKE ?", "%#{query}%").group("country, city").select("name, city"). While you will be using group by, in any DB, you must need to use those columns which you have used in your group by clause, into the select clause too. — Arup Rakshit
– Arup Rakshit, Commented Nov 9, 2014 at 15:50
I get the same error even if I do Model.where("name LIKE ?", "%#{query}%").group(["country", "city"]).select("id") — Nick Ginanto
– Nick Ginanto, Commented Nov 9, 2014 at 15:52
sorry typo.. It will be select("country, city") instead of select("name, city"). — Arup Rakshit
– Arup Rakshit, Commented Nov 9, 2014 at 15:57

Arup Rakshit · Accepted Answer · 2014-11-09 18:10:09Z

4

You should select before grouping

Model.where("name LIKE ?", "%#{query}%")
     .select("country, city")
     .group("country, city")

Arup Rakshit

119k30 gold badges270 silver badges328 bronze badges

answered Nov 9, 2014 at 16:40

Ahmad Elassuty

4,7442 gold badges16 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alejandro Babio · Accepted Answer · 2014-11-13 13:10:00Z

0

If you still have troubles with:

Model.where("name LIKE ?", "%#{query}%").group("country, city").select("country, city")

I guess you have defined a default scope, and you must do this way:

Model.unscoped.where("name LIKE ?", "%#{query}%").group("country, city").select("country, city")

Edited:

If it didn't works, you can do this and publish the output:

Model.where("name LIKE ?", "%#{query}%").group(["country", "city"]).to_sql

answered Nov 10, 2014 at 17:05

Alejandro Babio

5,23920 silver badges28 bronze badges