排序方式协会共同数量（滑轨） [英] Ordering by number of associations in common (Rails)

问题描述

背景：我的帖子和用户，都有许多社区

BACKGROUND: I have Posts and Users, and both have many Communities.

目的：对于任何给定的用户，我想回到文章的集合，订购多少社区后有共同的用户（帖子配有常见的是更高更多的社区高达）

OBJECTIVE: For any given User I'd like to return a collection of Posts, ordered by how many communities the post has in common with the user (posts with more communities in-common being higher up)

我目前的尝试（使用排序方法）的作品：

My current attempt (using the sort method) works:

Post.includes(:community_posts).where(community_posts: { community_id: current_user.community_ids }).sort{ |x,y| (y.community_ids & current_user.community_ids).length <=> (x.community_ids & current_user.community_ids).length }

但有一个更好/更有效的方法来做到这一点？

but is there a better/more efficient way to do this?

推荐答案

我更好/更有效的理解是，要执行在数据库中的排序，而不是红宝石。

My understanding of better/more efficient is that you want to execute the sort in database, rather than Ruby.

下面是一个（简单？）查询只返回与在普通社区的帖子与本站无关。

Here is a (simpler?) query to return only the posts with communities in common with the user.

current_user.posts.joins(:communities).merge(current_user.communities)

在合并过滤加入，我最喜欢的新的（对我来说）<一个href="http://blog.mitchcrowe.com/blog/2012/04/14/10-most-underused-activerecord-relation-methods/">ActiveRecord招数的。

The merge filters the joins, one of my favorite new (to me) ActiveRecord tricks.

好了，我怎么能申请类似的方法，通过在普通社区的数量排序，而不是只过滤？ 在这里，这将做：

Okay, so how can I apply a similar method to ordering by the number of communities in common, instead of just filtering? Here, this'll do:

current_user.posts.joins(:communities).where(communities: {id: current_user.community_ids}).select('posts.*, COUNT(distinct communities.id) AS community_count').group('posts.id').order('community_count DESC')

在加入创建一个单独的帖子结果每个communities_posts，然后我们使用组和计数来组合这些结果，在数据库中，由用户与柱之间不同的匹配的社区。

The joins creates a separate Post result for each communities_posts, then we use the group and COUNT to group those results, in database, by distinct matching communities between the user and post.

值得注意的是，因为选择，返回的每条记录会看起来像一个帖子也给 post.community_count 。

Of note, because of the select, each record returned will look like a Post but also respond to post.community_count.

这篇关于排序方式协会共同数量（滑轨）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！