active record join two tables with where

Question

I have a model

class PatientProfile < ActiveRecord::Base
  has_many :friendships

  with_options through: :friendships, source: :friend do |model|
    model.has_many :friends, -> { where(friendships: { status: 'accepted'}) }
    model.has_many :friend_requests, -> { where(friendships: { status: 'requested'}) }
    model.has_many :requested_friendships, -> { where(friendships: { status: 'requestor'}) }
  end

uids = ["123", "456"]

I'd like to query PatientProfile to find all records where uid is in the uids array and there are no records of friendships between the two users.

This should be possible with a join but I'm getting tripped up in the documentation

EDIT:

uid is on PatientProfile

On the Friendships table there are two columns, friend_id and patient_profile_id. I only want the PatientProfile's where friend is not in the list of uids and patient_profile_id is not, say, 1

Manuel van Rijn Manuel van Rijn · Accepted Answer · 2016-02-02T14:53:00

We're missing some context about what you mean with no records of friendships, but if you're looking for the records, having 0 friendships with the uids it would be:

PatientProfile.includes(:friendships).where(uid: [123, 456], friendships: {id: nil})

update

Or if you prefer a scoped version:

scope :no_friendships, -> { includes(:friendships).where(friendships: {id: nil}) }

So now you can do:

PatientProfile.no_friendships.where(uid: [123, 456])

active record join two tables with where

1 Answers