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

i'm building dating-style app users can approve other users. use approval model track these relationships. each approval has user_id , approved_id -- user id of approved user. has rejected_at, datetime indicating 1 user has rejected another.

to present eligible users current_user, must query users there either

• no approval relationship
• approval relationship approved_id current_user.id (meaning eligible user approves current_user there's no relationship other way around
• exclude users have approval non-nil rejected_at attribute approved_id user or user_id current_user.

how can craft activerecord query find eligible users? understand can joins on approval want account there being no approval relationship between users! i'm thinking may make more sense make 2 separate queries i'd know if it's possible combine one..

the behavior want left outer join, include rows users whether or not there matching rows in approvals. way, either user has issued no approval target user, or 1 has , can filter rejection.

the query like

-- looking users either have no opinion or have approved user 1 select * users left outer join approvals   on users.id = approvals.user_id   , approvals.approved_id = 1 -- filter approvals of user 1 users.id != 1 -- ignore user 1   , (     approvals.approved_id null -- approving user has no opinion of user 1     or approvals.rejected_at null -- approving user has not rejected user 1   ) ; 

in pieces,

• users left outer join approvals considers users, if have no approvals
• on users.id = approvals.user_id pairs users approvals created
• on ... , approvals.approved_id = 1 considers approvals user 1
• where users.id != 1 considers other users
• where ... approvals.approved_id null takes users have not created approvals pertaining user 1.
• where ... approvals.rejected_at null takes users did create approval user 1, , not rejection

activerecord doesn't particularly pretty when translate this, works:

class user < activerecord::base   def eligible_partners     user.joins( # join did before       <<-sql         left outer join approvals         on  users.id = approvals.user_id         , approvals.approved_id = #{self.id}       sql     ).where.not(id: id) # ignore ourselves      .where( # filter no approvals or positive approval        <<-sql          approvals.approved_id null          or approvals.rejected_at null        sql      )   end end 

if want more readable, can gather ids of every user has rejected given person, , of other users. on 1 hand, it's 2 queries; on other, may less expensive join once tables big, , it's way easier understand.

  def other_eligible_partners     rejecter_ids = approval       .where(approved_id: id) # ourselves       .where.not(rejected_at: nil) # rejected       .pluck(:user_id)     user.where.not(id: rejecter_ids + [id]) # people didn't reject   end