How to get a list (of IDs) of grouped records using Rails+MySQL?

Question

I can group records with the count of grouped records like this:

@cars = Car.group(:manufacturer).select("id, manufacturer, COUNT(id) AS total_count)

And the output will be something like this;

Porsche - 5
Renault - 3
Mercedes - 1
Audi - 2

I am trying to figure out now how to also get a list of the grouped records (their IDs) - like for Porsche, I would like to know the 5 grouped cars (their IDs). Is there any way to do it - ideally with using MySQL (in the select passage)?

Answer 1

One way would be using GROUP_CONCAT which, to quote the docs, "returns a string result with the concatenated non-NULL values from a group." It would go something like this:

@cars = Car.group(:manufacturer)
          .select("id, manufacturer, COUNT(id) AS total_count, GROUP_CONCAT(id) AS car_ids")

@cars.first.car_ids # => "1,2,5,9"
@cars.first.car_ids.split(',') # => [ "1", "2", "5", "9" ]

I should caution, though, that having manufacturer as a text attribute of Car is going to become a headache pretty quickly. You'd be better off with a Manufacturer model that has_many :cars. This will allow you to do much smarter queries, e.g. Manufacturer.includes(:cars).all. That's why we use relational databases in the first place.