0

How can I use a joins to query an associated model in rails 5? What I've tried is DoctorLocation.joins(:location).where(:location => {:confidence => 2}) after looking at the documentation and this question. What am I doing wrong? My query isn't returning any results.

pry(main)> DoctorLocation.joins(:locations).where(:locations => {:confidence => 2})
=> #<DoctorLocation::ActiveRecord_Relation:0x3ff735a09d8c>

class DoctorLocation
   belongs_to :location

end

class Location
   has_many :doctor_locations, dependent: :destroy

end

Migration

   create_table :doctor_locations do |t|
      t.integer :doctor_profile_id
      t.integer :location_id
      t.text :uuid
      t.timestamps null: false
    end


 create_table :locations do |t|
      t.string   :address
      t.integer  :ordinal, :default => 1
      t.integer  :doctor_profile_id
      t.text     :uuid
      t.timestamps
    end

 add_column :locations, :confidence, :integer
user2954587
  • 4,661
  • 6
  • 43
  • 101
  • Please share your migrations for ```doctor_locations``` and ```locations``` tables. Also, what's wrong with your query? Does it return incorrect results? – Ilya Konyukhov Oct 12 '18 at 15:15
  • @IlyaKonyukhov updated with output. The migrations are old and the tables have changed a bit. Anything specific you'd like to see? – user2954587 Oct 12 '18 at 15:19
  • I don't see a field named ```confidence``` in locations table, so your condition ```:location => {:confidence => 2}``` makes no sense here – Ilya Konyukhov Oct 12 '18 at 15:29
  • @IlyaKonyukhov i added it in a later migration – user2954587 Oct 12 '18 at 15:31
  • If that integer field was eventually added to ```locations``` table later. My last suggestion is that location with ```confidence: 2``` is missing, that's why your request returns empty results. – Ilya Konyukhov Oct 12 '18 at 15:31
  • i have locations with confidence 2. what's really strange is that no SQL is even generated and returning 0 results. It just returns the empty array – user2954587 Oct 12 '18 at 15:34
  • Does ```DoctorLocation.find_by_sql 'select doctor_locations.* from doctor_locations join locations on locations.id = doctor_locations.location_id where locations.confidence = 2'``` return any results (non-empty array)? – Ilya Konyukhov Oct 12 '18 at 15:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/181764/discussion-between-user2954587-and-ilya-konyukhov). – user2954587 Oct 12 '18 at 15:38
  • yep it returns results – user2954587 Oct 12 '18 at 15:39

2 Answers2

1

You actually have the right query. Rather its your testing method that is broken.

class DoctorLocation < ApplicationRecord
  belongs_to :location
  def self.with_confidence(c)
    DoctorLocation.joins(:location).where(locations: { confidence: c })
  end
end

This passing spec confirms that it works as expected:

require 'rails_helper'

RSpec.describe DoctorLocation, type: :model do

  after(:each) { DoctorLocation.destroy_all }
  it "includes locations with the correct confidence" do
    dl = DoctorLocation.create!(location: Location.create!(confidence: 2))
    DoctorLocation.create!(location: Location.create!(confidence: 1))
    expect(DoctorLocation.with_confidence(2)).to include dl
    expect(DoctorLocation.with_confidence(2).count).to eq 1
  end
  it "does not include rows without a match in the join table" do
    dl = DoctorLocation.create!(location: Location.create!(confidence: 1))
    expect(DoctorLocation.with_confidence(2)).to_not include dl
  end
end
max
  • 96,212
  • 14
  • 104
  • 165
  • You should probably start by checking that you actually have any records in the db that match the criteria. – max Oct 12 '18 at 15:59
  • that worked! but i don't get why it worked? what do you mean the testing method? can I not call it directly? – user2954587 Oct 12 '18 at 18:52
  • I mean that the problem was not really this code per say rather the circumstances surrounding it. Usually this boils down to not properly isolating the issue or not testing it properly - like just monkeying in the console instead of setting up real automated tests. – max Oct 12 '18 at 18:54
  • I figured out what the issue is - it's adding an `s` in the where clause. – user2954587 Oct 12 '18 at 18:59
  • `DoctorLocation.joins(:location).where(locations: { confidence: 2 })` works b/c there is a s in `.where(locations:`. Wow – user2954587 Oct 12 '18 at 19:00
  • I think you may actually have created an additional table named `location` by misstake. Otherwise that query should give an error `missing FROM-clause entry for table "location"`. – max Oct 12 '18 at 19:10
0

In joins and where (hashed arguments) you need to declare name of association (location), not table name (locations): 

DoctorLocation.joins(:location).where(location: { confidence: 2 })

It's often a source of confusion.

Ilya Konyukhov
  • 2,666
  • 1
  • 12
  • 21