Ordering by number of associations in common (Rails)

Question

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?

Answer 1

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.