Given the model:

• Member -> belongs_to :category
• Category -> has_many :members

I would like to know for every category whether or not it has members, in the style of:

----------------------
| name | has_members |
======================
| cat1 | true        |
| cat2 | false       |
| cat3 | true        |
----------------------

The following solutions are way too slow to achieve this:

• Category.eager_load(:members).map{|s| s.members.any?}
• Category.includes(:members).map{|s| s.members.any?}

This would be a use case for a counter cache, but I wonder whether this could be solved by some smart ARel or SQL.

What would be the fastest way to achieve this?

CodePudding user response：

What if you inverse the problem like this :

Member.includes(:category).order(:category_id).distinct.map{ |m| m.category !=nil ? m.category.name : "" }

CodePudding user response：

This query should return a hash of values with the name and the count of members.

Category.includes(:members).distinct.select('categorys.name, COUNT(members.*)').group('category.name').count(:members)

If you wanted JUST the Categories that have more than zero members you can do:

Category.joins(:members).distinct.select('categorys.name, COUNT(members.*)').group('category.name').count(:members)

Which will ignore the Categories where members = 0.

The query itself is probably not what's slowing you down, it's the fact that you then pass them all to .map which is a ruby method, not SQL. I think for anything faster than what I've given you, you'll have to go straight to the DB with a counter cache. Also in a pure SQL query you could add a CASE statement to swap true/false for numbers > or < 0.