How can I find records by "count" of association using rails and mongoid?

Question

With these models:

class Week
  has_many :proofs
end
class Proof
  belongs_to :week
end

I want to do something like:

Week.where(:proof.count.gt => 0)

To find only weeks that have multiple proofs.

There is one answer that seems to address this:

Can rails scopes filter on the number of associated classes for a given field

But in this example, there is no such attribute as proof_ids in Week since the ids are stored with the proofs. This does not work for example:

Week.where(:proof_ids.gt => 0)

How is this query possible? Conceptually simple but I can't figure out how to do this with mongo or mongoid.

Similarly, I'd like to order by the number of proofs for example like:

Week.desc(:proofs.size)

But this also does not work.

I do realize that a counter-cache is an option to both my specific questions but I'd also like to be able to do the query.

Thanks in advance for any help.

Michi

Answer 1

With rails (and without counter_cache), you could do:

class Week < ActiveRecord::Base
  has_many :proofs

  def self.by_proofs_size
    sort_by { |week| week.proofs.size }
  end

  def self.with_at_least_n_proofs(n = 1)
    select { |week| week.proofs.size >= n }
  end
end

Even though each of those operations produces 2 queries, this is far from ideal.

The pair of queries is repeated (=> 4 queries for each operation) with scopes (bug?):

scope :with_at_least_n_proofs, -> (n = 1) { select { |w| w.proofs.size >= n } }
scope :by_proofs_size, -> { sort_by { |w| w.proofs.size } }

The ideal is probably to use counter_cache

scope :with_at_least_n_proofs, -> (n = 1) { where('proofs_count >= ?', n) }
scope :by_proofs_size, -> { order(proofs_count: :desc) }

Ouse

You can remove includes(:proofs) to increase performance by a ton. Currently, you're loading all of the data in :proofs, when all you really need is a quick count query. Try it out. – Briarwood

it will kill you application because you will receive all weeks with all proofs from database to memory of your application – Sibbie

@Sibbie That's why I mentioned using a counter_cache – Ouse

Answer 2

I don't know if this is the best solution, as it maps it through a array, but this does the job: (the other solutions mentioned here gives me exceptions)

class Week < ActiveRecord::Base

  scope :has_proofs, -> { any_in(:_id => includes(:proofs).select{ |w| w.proofs.size > 0 }.map{ |r| r.id }) }

end

Mulch

Answer 3

Pardon me if I'm way off - but would you be able to use a simple counter_cache in the weeks table? Then you could do something like week.proofs_count.

Dennison

Yeah, as I said in my question, I know a counter_cache is an option but I'd rather do a query if possible in this case. If that isn't going to work, then I'll definitely do the counter cache. – Michi