title: Set intersection or Join more efficient? Ruby on rails
body: <p>So my Group model has a bunch of Members, some members are friends with one another, some aren't. I'd like to create a controller action that a user can make a request to via my app, that gives back a hash like the following:</p>
  
  <pre><code>{ group : { members: { friends: group_friends, others: group_others } } }
  </code></pre>
  
  <p>I've accomplished the following via a set intersection in ruby:</p>
  
  <pre><code>group_members = group.all_members
  user_friends = current_user.all_friends
  user_group_friends = group_members &amp; user_friends
  user_group_others = group_members - user_group_friends
  </code></pre>
  
  <p>My question is, being that this is ruby - would a database query utilizing a join be a better option here in terms of performance? Or should I just leave this as is....</p>
  
  <p>I'm also curious as to what a query would look like for this...</p>
  
  <p>FYI: These are the relationships I'm working with:</p>
  
  <pre><code>  Group
      has_many :members, class_name: "User"
      has_many :inverse_members....
      # all_members retrieves members who have added, been added
  
    User
      has_many :user_groups
      has_many :groups, :through =&gt; :user_groups
      has_many :friendships
      has_many :friends, :through =&gt; friendships
      has_many :inverse_friends....
      ....
      # all_friends retrieves all friends who have added, been added
  
    User_Groups
      belongs_to :user
      belongs_to :group
  
    Friendship
      belongs_to :user
      belongs_to :friend
  </code></pre>
  
  <h2>Queries so far:</h2>
  
  <h2>#1</h2>
  
  <pre><code>group_members = group.all_members # Instance method uses returns associated members
  group_members_friends = group_members.where("? in friends OR ? in inverse_friends", current_user, current_user)
  </code></pre>
  
  <p>Gives this error</p>
  
  <pre><code>     # --- Caused by: ---
   # SQLite3::SQLException:
   #   no such table: friends
  
   ActiveRecord::StatementInvalid:
     SQLite3::SQLException: no such table: friends: SELECT "users".* FROM "users" INNER JOIN "user_groups" ON "users"."id" = "user_groups"."user_id" WHERE "user_groups"."group_id" = ? AND (1 in friends OR 1 in inverse_friends)
  </code></pre>
  
  <p>because the join that allows group.all_members to happen (User_Groups) does not contain the field friends (obviously via the above associations)... How do I get access to the each member(User) in question and check their friends??</p>
  
  <h2>#2</h2>
  
  <pre><code>group_members = group.all_members # Instance method uses returns associated members
  group_members_friends = group_members.where("id in ?", current_user.all_friends)
  </code></pre>
  
  <p>Gives this error:</p>
  
  <pre><code> # --- Caused by: ---
   # SQLite3::SQLException:
   #   near "2": syntax error
  
   ActiveRecord::StatementInvalid:
     SQLite3::SQLException: near "2": syntax error: SELECT "users".* FROM "users" INNER JOIN "user_groups" ON "users"."id" = "user_groups"."user_id" WHERE "user_groups"."group_id" = ? AND (user.id in 2,3)
  </code></pre>