0

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

Community
  • 1
  • 1
BananaNeil
  • 10,322
  • 7
  • 46
  • 66

1 Answers1

1

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

Additional Info

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.

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 is the same as LEFT OUTER JOIN (more info).

Community
  • 1
  • 1
cschroed
  • 6,304
  • 6
  • 42
  • 56
  • Woah neat - I need to try out your solution before I mark your answer as correct, but could you possibly explain how a `LEFT JOIN` is different from a `JOIN`? And do you happen to know if there are any rails convince methods to do a ``LEFT JOIN`? – BananaNeil May 28 '15 at 04:29
  • Also, is a `LEFT JOIN` different from a `LEFT OUTER JOIN`? – BananaNeil May 28 '15 at 04:33
  • Looks like your answer works just fine. Thanks for the help! If you have the means, it would be excellent if you could expand your answer to answer my last two comments as well. – BananaNeil May 28 '15 at 09:10
  • 1
    Added additional info to answer. – cschroed May 28 '15 at 11:45