Rails | PG::GroupingError

Question

I have cars and car_cities models. I would like to get the cities that have at least one car.

The association;

Cars
belongs_to :car_city

CarCities
has_many :cars

The join query;

CarCity.joins(:cars).group("cars.car_city_id").having("count(cars.id) > 0")

That works on my local environment. But not working on production.

The error;

An ActionView::Template::Error occurred in search#search_city:

PG::GroupingError: ERROR:  column "car_cities.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT “car_cities".* FROM "car_cities” INNER JOIN "cars"…

^
: SELECT “car_cities".* FROM “car_cities” INNER JOIN “cars” ON “cars"."car_city_id” = “car_cities"."id” WHERE "car_cities"."car_country_id” = $1 GROUP BY cars.car_city_id HAVING count(cars.id) > 0

app/views/search/_search_filters.html.erb:88:in `block in _app_views_search__search_filters_html_erb__3215729544581234245_70286113854020'

Why don't you group based on car_cities.id rather than cars.car_city_id. Both are actually the same records right? — Shabini Rajadas, Jul 17 '17 at 07:39

score 1 · Accepted Answer · answered Jul 17 '17 at 07:40

1

Try this on the production console if possible..

CarCity.joins(:cars).group("car_cities.id").having("count(cars.id) > 0")

answered Jul 17 '17 at 07:40

Md. Farhan Memon

6,055
2
11
36

score 1 · Answer 2 · answered Jul 17 '17 at 07:43

Can you please try this query,

CarCity.joins(:cars).group("car_cities.id").having("count(cars.id) > 0")

grouping by cars.car_city_id and car_cities.id, both corresponds to the same records. And also when using an INNER JOIN with 'Car' model, records from cars will be selected only where the car count is more than 1.

Rails | PG::GroupingError

2 Answers2