CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
John Allen
John Allen

Reputation: 93

Rails query based on results count of has_many model column

Customer has_many Shifts through Jobs
Shifts belong_to Truck
Trucks belong_to Fleet

I'm trying to gauge the top 5 most used fleets that a customer has booked shifts with, as well as the number of shifts. Ideally, I would get Fleet A: X shifts and so on.

I tried using customer.shifts.joins(:fleets).select('*, count(shifts.fleet_id) as fleet_count').order('fleet_count desc') but I don't really know where to go other than manually looping through all of a customer's shifts in order to find the most utilized fleets.

Thanks in advance for any insights!

Upvotes: 2

Views: 114

Answers (2)

John Allen
John Allen

Reputation: 93

So, working through this with my colleagues, we seemed to have found the answer.

Customer.find(8).shifts.joins(:fleet).group('fleets.id').select('fleets.name as fleet_name, count(*) as shift_count').order('shift_count desc').limit(5).collect{|f|[f.fleet_name, f.shift_count]}

Grouping the Shifts into the Fleets by fleets.id structured the rest of the query better and allowed me to collect the data into a meaningful hash.

Upvotes: 0

Segfault
Segfault

Reputation: 8290

So, this is answer focuses on the SQL, I'm not sure the best way to translate that back to active record methods. It's a query that is focused on the Shifts table, and not really concerned with the Customer table, you don't even really need to join in the Customer table. Join Shifts, Trucks, and Fleets. Filter the results with a where clause on shifts.customer_id that matches the customer you're interested in. Select count(*) and use group by fleet.id. Order by the count and limit the results to 5, and you'll have your result set.

select count(*) as shift_count, fleets.id from shifts
  inner join trucks on shifts.truck_id = trucks.id
  inner join fleets on trucks.fleet_id = fleets.id
  where shifts.customer_id = 1
  group by fleets.id
  order by shift_count DESC
  limit 5;

To translate this back into active record code, I think the main thing you can do is find a way to include the group by clause into what you already have in the Customers class.

Upvotes: 2

Related Questions