Rails3 Scope for Count of Children in Has_Many Relationship

scope for count children has_many relation

Your without_answer scope is very close, but needs an outer join like this:

scope :without_answer,
   joins('LEFT OUTER JOIN answers ON answers.question_id = questions.id').
   select('questions.id').
   group('questions.id').
   having('count(answers.id) = 0')

Then, you can get the count with length:

Question.without_answer.length

Note: if you want without_answer to be the same as no_answer (i.e. return actual Question objects), you would need to remove the select.

A simpler and faster way to count the unanswered questions is like this:

Question.joins('LEFT OUTER JOIN answers ON answers.question_id = questions.id').
         where('answers.id' => nil).count

Also, this will return the same as no_answer as-is, simply use all instead of count.

Rails - named scope issue (depends on number of children in a many_to_many relationship)

I didn't spec out your models in my own console, but wouldn't something along these lines work?

Group.joins(:applications).group('groups.id').having('COUNT(*) > 10').where(["applications.pending = ?", false])

Basically, once you include GROUP BY conditions in the underlying SQL, you can use HAVING on the aggregate results. The WHERE simply limits it to those results that you're looking for. You can achieve the same result using straight SQL:

Group.find_by_sql(["SELECT * FROM groups INNER JOIN applications ON applications.group_id = groups.id WHERE applications.pending = ? GROUP BY groups.id HAVING COUNT(*) > ?", false, 10])

Kind of a huge query to include in a named scope. You might want to consider breaking this into pieces - it might save lots of headaches later...

Using named_scope with counts of child models

class Foo < ActiveRecord::Base
  has_many :bars

  # I don't like having the number be part of the name, but you asked for it.
  named_scope :with_one_bar, :joins => :bars, :group => "bars.foo_id", :having => "count(bars.foo_id) = 1"

  # More generically...
  named_scope :with_n_bars, lambda {|n| {:joins => :bars, :group => "bars.foo_id", :having => ["count(bars.foo_id) = ?", n]}}
  named_scope :with_gt_n_bars, lambda {|n| {:joins => :bars, :group => "bars.foo_id", :having => ["count(bars.foo_id) > ?", n]}}

end

Called like so:

Foo.with_n_bars(2)

Filtering child objects in a has_many :through relationship in Rails 3

I believe I was going about this the wrong way, specifying conditions on company_memberships instead of users, which was what I actually wanted (a list of Users, not a list of CompanyMemberships). The solution I think I was looking for is:

users.where(:company_memberships => {:admin => true})

which generates the following SQL (for company with ID of 1):

SELECT "users".* FROM "users"
  INNER JOIN "company_memberships"
    ON "users".id = "company_memberships".user_id
  WHERE (("company_memberships".company_id = 1))
    AND ("company_memberships"."admin" = 't')

I'm not sure yet if I'll need it, but the includes() method will perform eager loading to keep down the number of SQL queries if necessary:

Active Record lets you specify in
advance all the associations that are
going to be loaded. This is possible
by specifying the includes method of
the Model.find call. With includes,
Active Record ensures that all of the
specified associations are loaded
using the minimum possible number of
queries.queries. ^{RoR Guides: ActiveRecord Querying}

(I'm still open to any suggestions from anyone who thinks this isn't the best/most effective/right way to go about this.)

---

Related Topics