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

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?

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)

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

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