ArtLion ArtLion - 1 year ago 46
Ruby Question

How to write correct and efficient code in Ruby in Rails equal to SQL

I stucked and I don't know how to write correct query in RoR to achieve active record.

Relations between tables:

imed_patient has one imed_operator
imed_operator has many imed_operator_contacts
imed_patient has_many imed_operator_conatc through imed_operator

Definition of models :

class ImedOperator < ActiveRecord::Base
self.table_name = "imed_operator"
self.primary_key = "code"

has_one :imed_patient, foreign_key: 'r_opr_code'
has_many :imed_operator_contact, :foreign_key => "r_opr_code"

class ImedOperatorContact < ActiveRecord::Base
self.table_name = "imed_operator_contact"

class ImedPatient < ActiveRecord::Base
self.table_name = "imed_patient"
self.primary_key = "code"

has_many :visit, :foreign_key => "r_ptn_code"
belongs_to :imed_operator , foreign_key: 'r_opr_code'
has_many :imed_operator_contact, through: :imed_operator

Good SQL in PostgreSQL:

select * from imed_patient
INNER JOIN imed_operator ON imed_patient.r_opr_code = imed_operator.code
INNER JOIN imed_operator_contact ON imed_operator.code = imed_operator_contact.r_opr_code
where (imed_operator_contact.r_ct_id = 1 or imed_operator_contact.r_ct_id = 2) and imed_operator_contact.value = '501'

Code now (not working) is:

@pacjenci = ImedPatient.ImedOperatorContact.where('imed_operator_contact.r_ct_id = ? or imed_operator_contact.r_ct_id = ? and imed_operator_contact.value = ?',1,2,'+48501')

Error during run:

NoMethodError in PacjenciController#szukajpacjenttel undefined method
`ImedOperatorContact' for #

Anybody can help me ?


Thanks guys for inspiration. I solved it by:

@pacjenci = ImedPatient.joins(:imed_operator).joins(:imed_operator_contact).where('imed_operator_contact.value' => '+48501', 'imed_operator_contact.r_ct_id' => [1,2])

I used information from here point Joining Nested Associations (Multiple Level)

Special Thanks to Andy

Answer Source

Try adding this to your ImedPatient:

has_many :imed_operator_contacts, through: :imed_operator

With that you should be able to use any ActiveRecord/arel style query:

imed_patient.imed_operator_contacts.where(value: '501')