How to query to return the most common foreign key in a join table with rails

Question

I have 3 models. Project, ProjectMaterial, and Material

A Project has_many ProjectMaterials and many Materials through ProjectMaterials.

This is bidirectional, with ProjectMaterial acting as a join table with user-submittable attributes.

I'd like to query the ProjectMaterial model to find the most frequent value of material_id. This way I can use it to find the most frequently used material.

Any help with a query would be greatly appreciated. I'm stuck. Thanks in advance!

Answer 1

One way could be pluck ids to get the array, then count the most frequent.

ids = ProjectMaterial.pluck[:material_id]

For example: Ruby: How to find item in array which has the most occurrences?

Or better, by query to get a hash with counts:

counts = ProjectMaterial.group(:material_id).count

Once you know that you get a hash, you can sort by any ruby method, picking the most frequent or the n most frequent. Example of sorting:

counts.sort_by { |_, v| v }

Answer 2

You can chain group, count and sort methods on your ActiveRecord query like this:

ProjectMaterial.group(:material_id).count.values.sort.last

The first part ProjectMaterial.group(:material_id).count gives you the hash of each {material_id0 => rows_count0, material_id1 => rows_count1, ...}. Then, you can just get the values of the hash in an array, sort it and get the last item.