在常见的，即使没有（导轨）协会的数量订购 [英] Ordering by number of associations in common even if none (rails)

问题描述

我的工作具有非常相似的困境这一问一个项目： <一href="http://stackoverflow.com/questions/17199596/ordering-by-number-of-associations-in-common-rails">Ordering通过协会的共同数量（Rails）的

I am working on a project which has a very similar quandary to this ask: Ordering by number of associations in common (Rails)

的提问者说问（霓虹灯）写道：

The asker of said ask (Neon) writes,

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

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)

不幸的是，该解决方案仅包括具有至少一个公共社区帖子。我奎德里需要包括订购普通社区数目的所有帖子。

Unfortunately, the solution only includes posts that have at least one common community. My quandry needs to include all posts ordered by the number of common communities.

EXTENDED目的：结果必须符合的所有的帖子订购的普通社区的人数，包括职位与普通零社区用户（帖子的AREL对象与常见更多的社区是更高了）。

EXTENDED OBJECTIVE: The result must be an AREL object with all posts ordered by the number of common communities, including posts with zero communities in common with the user (posts with more communities in-common being higher up).

推荐答案

如果你需要包括零社区的共同点，你可以使用左用户帖子JOIN 。消毒所发送的加入情况的参数，我们可以在一个类的方法定义该使 sanitize_sql_array 方法是可用的：

If you need to include posts with zero communities in common with the user you can use a LEFT JOIN. To sanitize the parameters that are sent in a join condition, we can define this in a class method so that the sanitize_sql_array method is available:

# Post class
def self.community_counts(current_user)
  current_user.posts.joins(sanitize_sql_array(["LEFT JOIN community_posts ON community_posts.post_id = posts.id AND community_posts.community_id IN (?)", current_user.community_ids])).select("posts.*, COUNT(DISTINCT community_posts.community_id) AS community_count").group("posts.id").order("community_count DESC")
end

---

其他信息

这是 INNER JOIN 返回两个表之间的交集。 A LEFT JOIN 返回左表中的所有行（在这种情况下，职位），并从右侧返回匹配的行表（ community_posts ），但它也返回 NULL 右侧当没有比赛（没有社区职位，配合用户的社区）。请参见这个答案一个例证。

An INNER JOIN returns the intersection between two tables. A LEFT JOIN returns all rows from the left table (in this case posts) and returns the matching rows from the right table (community_posts) but it also returns NULL on the right side when there is no match (posts with no communities that match the user's communities). See this answer for an illustration.

据我所知Rails没有提供任何辅助方法，以产生一个 LEFT JOIN 。我们必须写出来的SQL这些。

As far as I know Rails doesn't provide any helper methods to produce a LEFT JOIN. We have to write out the SQL for these.

LEFT JOIN 是一样的 LEFT OUTER JOIN （ 更多信息）。

LEFT JOIN is the same as LEFT OUTER JOIN (more info).

这篇关于在常见的，即使没有（导轨）协会的数量订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！