2

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

AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012

1 Answers1

1

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
Pan Thomakos
  • 34,082
  • 9
  • 88
  • 85
  • Thanks, I'm not sure I'm following 100%, so let me ask... Why have the unread scope in ThreadParticipations and not Thread like I have it now? Just curious. Also, the 2nd block (ThreadParticipations .unread(current_user...) where is that living? the controller? thxs – AnApprentice Feb 05 '11 at 22:31
  • One more thing, the scope unread is not filtering by project_id? – AnApprentice Feb 05 '11 at 22:32
  • The reason you aren't filtering by project_id is that you want to include all the user's projects in your query. If you filter by project_id then you are back to your original problem of having to do multiple queries (one for each project). – Pan Thomakos Feb 05 '11 at 22:34
  • The second block can live anywhere you want to make the call - it's just there in my code as an example. – Pan Thomakos Feb 05 '11 at 22:34
  • I moved it to thread participations because that is what the scope is querying. It's querying user and unread - both on thread participations, not on thread. – Pan Thomakos Feb 05 '11 at 22:36
  • @pan that's a darn good point :) I'm going to try that out now. will report back shortly. thanks! – AnApprentice Feb 05 '11 at 22:38
  • Update, hit several road blocks. First, with the scope it errors, I believe the where should be where(:user_id => user.id, :read => false) And then in the block, .group('project.id') that errors with PGError: ERROR: syntax error at or near "group" LINE 1: ...thread_participations"."read" = 'f') GROUP BY project.id – AnApprentice Feb 05 '11 at 22:54
  • Hey, I updated my post with the user.id suggestion. Also, you should be grouping by projects.id not project.id - that was my bad. – Pan Thomakos Feb 05 '11 at 22:58
  • @Pan, trying now... But in the lambda, you have user_id: user.id, shouldn't it be with a => – AnApprentice Feb 05 '11 at 23:01
  • @Pan, still getting a PGError: ERROR: syntax error at or near "group" – AnApprentice Feb 05 '11 at 23:05
  • I think I got it to work by just doing .count, versus .count('threads.id'). But what do you do with this results? I get, 1, 1] [2, 3], the idea is to show a list of all groups and their unread count, so I'm not sure what these results are? Also, is there a way to get a 0 for a group with 0 records versus nothing? Maybe an example showing how to make use of this query? – AnApprentice Feb 05 '11 at 23:11
  • My guess is that you are using PostgreSQL - I would suggest checking the SQL syntax that is generated and making sure it looks correct. You might need to change the names of some of the attributes or something like that. I don't have full access to your models so I can't assit your further. This solution works in MySQL. – Pan Thomakos Feb 05 '11 at 23:15
  • In my solution the => { 10 => 15, 11 => 10 } indicates a sample output. What it usually means is the project_id and the unread count. In your output the project_id is probably 1 and then 2 and the unread count 1 and then 3 respectively. – Pan Thomakos Feb 05 '11 at 23:17
  • I don't know what you mean by getting a 0 for groups with 0 records. – Pan Thomakos Feb 05 '11 at 23:17
  • @pan, so the goal is to output a list of all the user's projects, with the user's project's unread count next to each project. I'm trying to learn how this query turns into that output. And the query seems to ignore the project if there is a count of 0. Does that make sense? Thanks for your help walking through this by the way! – AnApprentice Feb 05 '11 at 23:22
  • It does ignore projects that don't have any threads. You can get around this by using a left join, like this: .joins('LEFT JOIN threads on threads.id = thread_participations.thread_id LEFT JOIN projects on projects.id = threads.project_id') instead of the include statement. – Pan Thomakos Feb 05 '11 at 23:29
  • Thanks tried that while it worked, the output was the same. IDK, maybe there's some other ideas out there to get this working. – AnApprentice Feb 05 '11 at 23:35
  • Lets say that did work, how to you get the goal of outputting a list of every group regardless and then that group's count for the user? Given that the query is returning an array? – AnApprentice Feb 05 '11 at 23:36
  • It might be interesting to query for all the user's projects and include a field that is a COUNT of the user's unread. versus starting at ThreadParticipations. With SQL that was pretty easy with a subquery, or query within query, but with rails, I haven't figured out how that magic works yet. – AnApprentice Feb 05 '11 at 23:37
  • 1
    You'll have to do some investigation on your own to get further with your particular case. There are many variations on this query, but the core concept is the same: include the tables you want, group by the attribute you want to associate with and then count. My solution should provide you with the basics of performing a single count query rather than one for each project though. As far as I see it you can't get projects that a user is related to that don't have threads because there is nothing in your original post indicating how you would associate a user to a project without threads. – Pan Thomakos Feb 06 '11 at 00:11
  • Thanks Pan. Just one last question here. so given that the query above sends back a [1, 1] [2, 3], [5,1] where [X,Y] x is the project_id and y is the unread-count. How do you do anything with that data? Do you some how do a find so I can say find group_id 7 in X? If not found show 0? Really struggling to connect the dots here. Would hugely appreciate the knowledge on how to make use of the query's result set. Thanks! Or at least a point in the right direction. thanks again! – AnApprentice Feb 06 '11 at 03:53
  • 1
    If you want the projects that that user has not read yet, you could use your array to get all the projects in a single query: Project.find([[1,1],[2,3],[5,1]].map{ |k| k[0] }). You could also use a list of a user's projects to populate the unread count by referencing this array and filling in an unread_count variable on each project for the related project id. How you use the array is up to you, but if you want to display additional project information besides id and unread_count you'll have to do some additional processing of the array to get it into the format you want. – Pan Thomakos Feb 06 '11 at 04:17
  • @Pan, thanks. I'm thinking it might be better to return the array as a JSON object and let jQuery take care of it. – AnApprentice Feb 06 '11 at 04:27