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

[Arup Rakshit]

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?

[Sampson Crowley]

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)

[McNeal Maddox]

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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAKxHnE1VBowD-dGZTFBgFBDVrznRj29HVBYprSphyJzxtGdD0w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Biedenharn]

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