在 Rails 中 - 如何让一个查询包含多个查询？

发布于 2024-10-16 09:56:39 字数 788 浏览 2 评论 0原文

这里有 3 个模型：

项目
线程 (project_id)
thread_participations (thread_id, read boolean)

现在我有一个用户项目列表，该列表显示每个项目有多少线程未读取。这里的一个巨大问题是，如果用户有多个项目（所有用户都会这样做），则会导致数据库受到多个查询的影响，每个项目一个。

我想使用 Rails 构建一个查询，通过一次数据库命中，返回每个用户项目的未读计数。

这是我今天在视图中使用的内容：

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

在线程模型中：

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

关于如何执行此操作有什么建议吗？另外，这应该生活在哪个模型中？也许是用户的模型，因为它不是特定于项目或线程的？

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉生梦死 2024-10-23 09:56:40

有多种方法可以构建此查询，但这里是其中一种。

您可以在单个查询中执行此操作，然后循环结果。我首先会为某个用户的未读内容创建一个线程参与范围。然后使用范围并包含所有线程和项目，按项目 id 分组（以便您获得该项目的未读线程），然后通过计算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

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

回复收藏 0 原文

~没有更多了~