Rails, find results based on shared association using Active Record Query or AREL

Question

I'm working on a rails project where I have a User, that has_many Teams through a Membership model. The User has a privacy setting which can be (public, private or protected)

On the current user's homepage, I want to display all of the users who have their profiles set to public, but also the users who have their profile set to protected and share a team with the current user.

I could do this as two separate queries and then combine the resulting arrays but I'm assuming it's 'better' to keep it as one - I think it will also then behave better with will_paginate.

I thought I might need to use Arel because of the .or condition but just can't get my head around the joins needed to work out shared teams.

I'm new to AREL, SQL and Stackoverflow for that matter, so apologies if this doesn't make much sense.

Answer 1

I'm assuming the following setup:

class User < AR
  has_many :memberships
  has_many :teams, through: :memberships
end

class Membership < AR
  belongs_to :user
  belongs_to :team
end

class Team < AR
  has_many :memberships
  has_many :teams, through: :memberships
end

Then this should work:

sql = <<QUERY
SELECT users.* FROM users WHERE users.privacy_setting = 'public'
UNION
SELECT users.* FROM users JOIN memberships ON users.id = memberships.user_id
WHERE memberships.team_id IN (#{current_user.team_ids})
AND users.privacy_setting = 'protected'
QUERY

# use the paginated find_by_sql method (provided by will_paginate)
User.paginate_by_sql(sql, page: params[:page], per_page: 50)

Of course the column names, etc depend on your setup...

PS: No need for AREL, I think...