Using includes / joins

Question

I have a Proposal model and a Signature model.

Proposal has many signatures and Signature belongs to proposal

My relationships work fine when I select a single Proposal, but when I do a Proposal.search and return multiple proposals, I can't access signatures.

I want to sort my proposals by signatures count, so I tried this:

Proposal.find(:all, :limit => 3, :include => [:signatures])

Now when I debug(@proposals) I do see the corresponding signatures. How do I access them and write an :order => signatures.count ?

I'm not very familiar with includes / joins ...so let me know if I'm going about this the wrong way. Thanks.

Answer 1

To get you familiar with using includes vs. joins, have a look at the Railscast that's dedicated to it. The reason you can't order by signatures.count is because you're not selecting a column (or virtual column) that contains that information.

You're going to want a combination of :select and :join to get what you want.

Answer 2

I'd suggest using ActiveRecord's counter cache feature. Add a signatures_count field to your Proposal model and then you can order doing:

Proposal.find(:all, :limit => 3, :include => [:signatures], :order => 'signatures_count DESC')

Check out the belongs_to API documentation linked above for more details. There's an older Railscasts episode on this here.

Answer 3

:include is used when you will need to access the signatures later. If all you need is the count of associated signatures for sorting, then you do not need :include.

I don't especially like the following, but it seems to work:

Proposal.find(:all,
              :limit => 3,
              :joins => :signatures,
              :group => 'proposals.id',
              :order => 'count(proposals.id) desc')

First, the :joins parameter means that for each Proposal record you will get another row in the output for each associated Signature. The :group parameter combines those rows into one, but only after the :order parameter sorts based on the number of rows for each proposal.id (and hence number of signatures).

I suspect there is a better way, but this seems to work.