4

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?

neon
  • 2,811
  • 6
  • 30
  • 44

1 Answers1

7

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)

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

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.

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

Carlos Drew
  • 1,633
  • 9
  • 17
  • This is exactly what I was looking for, only one call to the db. Thanks! I was looking to query all posts (not just current_user.posts), so my final query starts with: "Post.joins(:communities)..." – neon Jul 07 '13 at 18:53
  • The one extension of this that I would really like to see is a query that would still include the posts where the number of common communities is zero at the end of the list. – BananaNeil May 25 '15 at 21:53
  • I've posted an ask requesting an answer to the extension I mentioned in my last comment here: http://stackoverflow.com/questions/30446662/ordering-by-number-of-associations-in-common-even-if-none-rails – BananaNeil May 26 '15 at 23:23