count model association in an sql friendly way

[Ohad Levy]

Hi,

when I have a model, and I would like to count how many associations belongs to each model record, it generates model.size +1  sql queries, e.g.:

Mode.find_each { |m|  m.hosts.count}
but this generates Model.size + 1 sql queries

any better way to do this?

thanks,
Ohad

[Ohad Levy]

ah, nevermind, I missed the group option - thanks :)

[Kates Gasis]

you can use activerecord magic columns with regards to the association. in your case it'll be host_count which will be incremented whenever an new host record is added.

[Ohad Levy]

true, but this doesn't help in this specific case where I need to count hosts based on different conditions.

on the same matter, it was quite easy to count with :group on associations like has_many, but it seems not to work on habtm relationships, e.g.

user has and belongs to many groups

what do I need to do to know how many users belongs to each group?
my current code (which works but really ugly) is something like this:

User.count_habtm "group"

def self.count_habtm association
    output = {}
    self.count(:include => association.pluralize, :group => "#{association}_id").to_a.each do |a|
      #Ugly Ugly Ugly - I guess I'm missing something basic here
      label = eval(association.camelize).send("find",a[0].to_i).to_label if a[0]
      output[label] = a[1]
    end
    output
  end

Thanks,
Ohad