In Rails - How to have one query that has multiple queries?

In have 3 models here:

• projects
• threads (project_id)
• thread_participations (thread_id, read boolean)

Right now I have a list of the user's projects, and the list shows how many threads are unread per project. The huge problem here is that if the user has several projects (which all users do) it causes the DB to get hit with several queries, one per project.

I would like to use Rails to build a query, that with one DB hit, returns an unread count for each of the user's project.

Here's what I use today in the view:

<% @projects.each_with_index do |project, i| %>
<%=project %>: <%= Thread.unread(current_user,project).count %>
<% end %>

And in the thread M开发者_高级运维odel:

  scope :unread, lambda { |user,project| 
      includes(:project,:thread_participations).where(:project_id => project.id, :thread_participations => {:read => false, :user_id => user.id})
  }

Any suggestions on how to do this? Also which model should this live in? Maybe the user's model since it is not project or thread specific?

Thanks

There are a couple of ways to structure this query, but here is one.

You can perform this in a single query and then loop over the results. I would first create a scope on thread participations for unread for a certain user. Then use the scope and include all threads and projects, group by the project id (so that you are getting unread threads for that project) and then count the number of unread threads by counting threads.id:

class ThreadParticipations
  scope :unread, lambda{ |user| where(user_id: user.id, read: false) }
end

ThreadParticipations
  .unread(current_user)
  .includes(:thread => :project)
  .group('projects.id')
  .count('threads.id')

=> { 10 => 15, 11 => 10 }
# project(10) has 15 unread threads and project(11) has 10 unread threads