rails: count and group with joined tables

Question

I have the following models:

Car: 
has_many :car_classes  

CarClass:
belongs_to :Car
belongs_to :CarMainClass

CarMainClass:
has_many :car_classes

What I want to do is to count the amount of cars in CarClass grouped by the car_main_class_id but then linked to the main_class_symbol which is in CarMainClass.

The query I have now is:

CarClass.group(:car_main_class_id).count(:car_id) => {43=>79, 45=>4 ...}

Which is almost what I want, except that I end up only with the :car_main_class_id which I to be the :main_class_symbol from CarMainClass:

{"A1"=>79, "A2"=>4 ...}

I tried joining the tables and custom select options, but they didn't work.

Can this be done in a query in which I don't have to iterate through the main classes again?

Many thanks for your help!

Your model names are not good. Could CarMainClass be better named as CarCompany (or something similar)? — Tom, Jul 31 '13 at 22:26
Hey Tom, I know haha thanks for the advice. I actually renamed them in this question to make them a bit more generic. — Sobremesa, Jul 31 '13 at 23:45

score 1 · Accepted Answer · answered Jul 31 '13 at 22:49

Instead of having a SQL approach and using a "count/group by", you should look to a very simple feature of Rails ActiveRecords : the counter_cache column.

For example, you can add a column "car_classes_count" in the CarMainClass, and in CarClass class, you do like this :

CarClass:
belongs_to :car
belongs_to :car_main_class, :counter_cache => true

You can do the same with a column "car_class_count" in Car.

I don't know if it can help, but I had the same kind of problems when I started to develop with Rails. I tried to do some unsuccessful crazy SQL queries (queries that worked w/ sqlite, but did not w/ postgres) and I finally choose an other approach.

Thanks man. I have indeed just started with rails, I didn't know I could use it for this purpose! — Sobremesa, Jul 31 '13 at 23:44

score 0 · Answer 2 · answered Jul 31 '13 at 23:36

0

Try this:

CarClass.includes(:car_main_class => :car_classes)
 .group(:car_main_class_id).map { |cc| 
   { cc.car_main_class.main_class_symbol => cc.car_main_class.cars.size }
 }

Although this is quite ugly - I agree with @Tom that you should try to think of more meaningful class names.

answered Jul 31 '13 at 23:36

Thilo

17,565
5
68
84

Thanks Thilo! this will work! I realize now that there is no query possible in which you don't loop through the results. Counter cashing seems a good answer. – Sobremesa Jul 31 '13 at 23:50

rails: count and group with joined tables

2 Answers2