How do I group and order by a model without loosing includes() method benefits

rubyonrails-talk
1

I have a Project and Bid model.

Project has_many :bids

Bid belongs_to project

I have now

relation = Project.includes(:bids, :user).references(:users, :bids)

What I now want is : select projects by ordering them with their bids count. How can I write this query?

2

relation = Project.   joins(     <<-SQL       LEFT JOIN (         SELECT           bids.project_id,           COUNT(bids.id) AS bid_count         FROM bids         GROUP BY bids.project_id       ) bid_counts         ON bid_counts.project_id = projects.id     SQL   ).   order('bid_counts.bid_count').   includes(:bids, :user).   references(:users, :bids)

3

Have you tried adding a ‘where’ condition to your query?

E.G., relation = Project.includes(:bids, :user).references(:users, :bids).where(bids > 0).sort.reverse

4

This probably works:

relation = Project.joins(:bids).group(Arel.sql(‘projects.id’)).order(Arel.sql(‘count(bids.id) DESC’))

However, you might have to play around with it a bit if you need to put the .includes(:bids, :user) back in (or take the 1+N hit)

-Rob