CODEWITHSUNDEEP
ruby-on-railsactiverecord
AMB
AMB

Reputation: 335

Can I specify a where query on the table i am going through using ActiveRecord?

Say I had 3 tables

  1. doctors,
  2. doctor_relationships &

  3. users

    • A user has many doctors through doctor_relationships
    • A doctor has many users through doctor_relationships
    • A user has many doctor_relationships
    • A doctor has many doctor_relationships
    • doctor_relationship belongs to a doctor
    • doctor_relationship belongs to a user

There is a column in doctor_relationships, :visited? a boolean value

What is the best way to Query all of the Users who have visited a certain doctor

I tried 

@doctor=doctor.first
doctor.doctor_relationships.where(visited: true)

Which gets me the doctor relationships I want, but then i can't call .users on that active record relation. Is the only way to do it to loop through each relationship in the active record relation and call .user on it, or is there a more ActiveRecord friendly way? ex.

doctor.doctor_relationships.where(visited: true).each do |relationship|
   relationship.user
end

Upvotes: 0

Views: 64

Answers (2)

locriani
locriani

Reputation: 5055

In these kinds of situations, I find it useful to invert the relationship in my head. What is the thing you want many of? Start with that model and the query becomes a bit easier to think through.

That gives us:

User.joins(doctor_relationships: :doctor).where(doctor_relationships: {visited: true, doctor: @doctor})

Although that will get you exactly what you're looking for, it's ugly. Let's improve it!

Right now I'm assuming your classes look like this:

class User < ActiveRecord::Base
  has_many :doctor_relationships
  has_many :doctors, through: :doctor_relationships
end

class DoctorRelationship < ActiveRecord::Base
  belongs_to :user
  belongs_to :doctor
end

class Doctor < ActiveRecord::Base
  has_many :doctor_relationships
  has_many :users, through: :doctor_relationships
end

I think it'd be a lot nicer if you could query for this case with something like:

 doctor.visits
 doctor.patients

To do this, you'll need to modify the models as follows:

class Doctor < ActiveRecord::Base
  has_many :doctor_relationships
  has_many :visits, -> { where(visited: true) }, class_name: 'DoctorRelationship'
  has_many :patients, through: :visits, source: :user
  has_many :users, through: :doctor_relationships
end

What we're doing here is telling ActiveRecord that we have an association, visits, that only exists when the visited field is true on DoctorRelationship. We are then using that association to define another association, patients, which will only return those users who have actually visited the doctor.

The benefit of this approach (besides readable code) is that it also generates efficient queries:

SELECT "users".* FROM "users" INNER JOIN "doctor_relationships" ON "users"."id" = "doctor_relationships"."user_id" WHERE "doctor_relationships"."doctor_id" = ? AND "doctor_relationships"."visited" = 't'

which happens to be the exact same query that the more verbose ActiveRecord code at the start gave us. Score!

Upvotes: 2

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42799

Well start the query from the users model then, i believe something like this should work

@doctor = whatever
User.joins(:doctor_relationships)
    .where(doctor_relationships: {visited: true, doctor: @doctor})

Upvotes: 0

Related Questions