DatingApp programming: ActiveRecord association for finding Users where there are no Approvals or one 1 way approvals

Question

I'm building a dating-style app where Users can approve other users. I use an Approval model to track these relationships. Each Approval has a user_id and an approved_id -- the User id of the approved User. It also has rejected_at, a datetime for indicating one User has rejected another.

To present eligible Users to the current_user, I must query for Users for which there are either

• No Approval relationship
• Approval relationship only with the approved_id as the current_user.id (meaning the eligible User approves the current_user but there's no relationship the other way around
• Exclude Users that have an Approval with a non-nil rejected_at attribute where approved_id is the User or user_id is the current_user.

How can I craft an ActiveRecord query to find eligible Users? I understand I can do a joins on Approval but I also want to account for there being NO Approval relationship between Users! I'm thinking it may make more sense to just make 2 separate queries but I'd like to know if it's possible to combine into one..

Answer 1

The behavior you want is a LEFT OUTER JOIN, which will include rows from users whether or not there are any matching rows in approvals. That way, you get either a User that has issued no Approval about your target User, or one who has and we can filter for rejection.

The query would look like

-- Looking for users who either have no opinion or have approved user 1
SELECT *
FROM users
LEFT OUTER JOIN approvals
  ON users.id = approvals.user_id
  AND approvals.approved_id = 1 -- Filter for only approvals of user 1
WHERE users.id != 1 -- Ignore user 1
  AND (
    approvals.approved_id IS NULL -- Approving user has no opinion of user 1
    OR approvals.rejected_at IS NULL -- Approving user has not rejected user 1
  )
;

In pieces,

• users LEFT OUTER JOIN approvals considers all users, even if they have no approvals
• ON users.id = approvals.user_id pairs users with approvals they created
• ON ... AND approvals.approved_id = 1 considers only approvals for User 1
• WHERE users.id != 1 considers only other users
• WHERE ... approvals.approved_id IS NULL takes users that have not created any approvals pertaining to User 1.
• WHERE ... approvals.rejected_at IS NULL takes users that did create an Approval for User 1, and it was not a rejection

ActiveRecord doesn't do anything particularly pretty when we translate this, but it works:

class User < ActiveRecord::Base
  def eligible_partners
    User.joins( # Join like we did before
      <<-SQL
        LEFT OUTER JOIN approvals
        ON  users.id = approvals.user_id
        AND approvals.approved_id = #{self.id}
      SQL
    ).where.not(id: id) # Ignore ourselves
     .where( # Filter for no approvals or a positive approval
       <<-SQL
         approvals.approved_id IS NULL
         OR approvals.rejected_at IS NULL
       SQL
     )
  end
end

If you want something more readable, you can gather up the IDs of every User that has rejected a given person, and then get all of the other Users. On one hand, it's two queries; on the other, it may be less expensive than a JOIN once your tables get big, and it's way easier to understand.

  def other_eligible_partners
    rejecter_ids = Approval
      .where(approved_id: id) # About ourselves
      .where.not(rejected_at: nil) # Rejected us
      .pluck(:user_id)
    User.where.not(id: rejecter_ids + [id]) # People who didn't reject us
  end