Active Record Query for subset conditions

Question

In my application, staff users create patient records. The patient model has a HABTM relationship with an insurance model to record the insurance/s the patient is covered by.

Staff users create referral requests that belong to patients. Staff users also belong to universities, which in turn belong to markets. For example, staff user Jim might belong to Harvard University, and Harvard University in turn belongs to the market Boston.

Clinician users belong directly to a market.

Clinician users each have a clinician_profile, and their profiles have a HABTM relationship with the insurance model to record the insurances/ the clinician accepts. ClinicianProfile belongs to user where role: :clinician.

The roles are defined with an enum:

  enum role: { staff: 0, clinician: 1, admin: 2 }

A staff user creates a patient record, which flows directly into creating a referral request, which flows directly into dispatches/new which I intend to display a list of all of the clinicians that are in their market and accept at least one of the insurances that the patient is covered by. This is on the path to figuring out how to actually send the referral request and relevant patient details to each of these clinicians. I've commented below what I'm trying to do with each step and what is not working - any advice on how to do this would be greatly appreciated.

Dispatches Controller New Action:

def new
#This works
@patient = @referral_request.patient
#This works
@user = current_user

#This works - returns a list of clinician users who match the current user's university's market.
@market_matched_clinicians = User.clinician.where(market: @user.university.market)

#This doesn't work -My intention is for this to return a list of clinician profiles that share at least one insurance with the patient.
@matched_clinician_profiles = ClinicianProfile.where(insurances: [@patient.insurances])

#Once I have all of the matched clinician profiles, how can I use that to return the corresponding clinician users?
# @matched_clinicians = ???


@dispatch = @referral_request.dispatches.new
end

Models:

class User < ApplicationRecord
  include Clearance::User
  include StaffUser
  include ClinicianUser
  include AdminUser

module ClinicianUser
  extend ActiveSupport::Concern

  included do
    has_one :clinician_profile
    has_many :lists
    has_many :universities, through: :lists
    has_many :dispatches
    has_many :referral_requests, through: :dispatches
    after_create :create_clinician_profile
    belongs_to :market
    validates :market_id, presence: true, if: :clinician?
  end

class ClinicianProfile < ApplicationRecord
  belongs_to :user, -> { where role: :clinician }
  has_and_belongs_to_many :languages
  has_and_belongs_to_many :races
  has_and_belongs_to_many :insurances
  has_and_belongs_to_many :genders
end

class Patient < ApplicationRecord
  belongs_to :author, -> { where role: :staff }, class_name: 'User', foreign_key: 'user_id'
  has_and_belongs_to_many :genders
  has_and_belongs_to_many :concerns
  has_and_belongs_to_many :insurances
  has_and_belongs_to_many :races
  has_many :referral_requests
  belongs_to :staff_doctor, class_name: 'User', foreign_key: 'staff_doctor_id'

  validates :staff_doctor_id, presence: true

class ReferralRequest < ApplicationRecord
  belongs_to :user, -> { where role: :staff }
  belongs_to :patient
  has_many :dispatches
  has_many :clinicians, through: :dispatches
  has_and_belongs_to_many :languages
  has_and_belongs_to_many :races
  has_and_belongs_to_many :genders
  validates :user_id, presence: true

  enum status: { created: 0, sent: 1, shared: 2, closed_under_care: 3, closed_not_seeking_care: 4, closed_unresponsive: 5 }
end

Here is the view where I am trying to render these results:

<% provide(:title, "New Dispatch") %>

<h2> These clinicians match your market and your patient's insurance </h2>

     <table class="table table-striped">
      <thead>
      <tr>
          <th>Provider ID</th>
          <th>Name</th>
          <th>Provider Market</th>
          <th>Insurances Accepted</th>
          <th>Gender/s</th>
          <th>Race/Ethnicity</th>
          <th>Languages</th>

      </tr>
    </thead>
    <tbody>

    <% @users.each do |user| %>
      <tr>
        <td><%= user.id %></td>
        <td><%= user.name %></td>
        <td><%= user.market.name %></td>
        <td><%= user.clinician_profile.insurances.map(&:name).to_sentence %></td>
        <td><%= user.clinician_profile.genders.map(&:name).to_sentence %></td>
        <td><%= user.clinician_profile.races.map(&:name).to_sentence %></td>
        <td><%= user.clinician_profile.languages.map(&:name).to_sentence %></td>
      </tr>
    <% end %>
    </tbody>
    </table>

Answer 1

To get ClinicianProfiles that match at least one insurance:

@profiles = ClinicianProfile.joins(:insurances)
                            .where(insurances: { id: @patient.insurances })
                            .group('clinician_profiles.id')
                            .having("count(*) = 1")

You can then fetch the users by joining clinician_profiles:

@users = User.joins(:clinician_profile)
             .where(
               market: @user.university.market,
               clinician_profile: { id: @profiles }
             )

Answer 2

Do it in Rails way. I assume you defined association with ClinicianProfile in Insurances Model. Add has_many :clinician_profiles, through: insurances to Patient and call @patient.clinician_profiles.

class Patient < ApplicationRecord
  belongs_to :author, -> { where role: :staff }, class_name: 'User', foreign_key: 'user_id'
  has_and_belongs_to_many :genders
  has_and_belongs_to_many :concerns
  has_and_belongs_to_many :insurances
  has_and_belongs_to_many :races
  has_many :clinician_profiles, through: insurances
  has_many :referral_requests
  belongs_to :staff_doctor, class_name: 'User', foreign_key: 'staff_doctor_id'

  validates :staff_doctor_id, presence: true