CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordactivemodel
AnApprentice
AnApprentice

Reputation: 110960

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

In have 3 models here:

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 Model:

  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

Upvotes: 2

Views: 384

Answers (1)

Pan Thomakos
Pan Thomakos

Reputation: 34350

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

Upvotes: 1

Related Questions