Rails - How to force associations to use alias table name

Question

p = Patient.find(30)

p.patient_problems

The above code generates the following query

SELECT `patient_problem`.* FROM `patient_problem` WHERE `patient_problem`.`patient_id` = 30 AND (`patient_problem`.`record_status_id` = 1)

But is there any way to assign/use alias table_name like

p.patient_problems(:alias=>'p1') # just for Ex.. This code will not work
p.patient_problems(:alias=>'p2') # just for Ex.. This code will not work

So it will generate the following queries

SELECT `p1`.* FROM `patient_problem` AS `p1` WHERE `p1`.`patient_id` = 30 AND (`p1`.`record_status_id` = 1)

SELECT `p2`.* FROM `patient_problem` AS `p2` WHERE `p2`.`patient_id` = 30 AND (`p2`.`record_status_id` = 1)

Additional Info

My problem is when I try to use joins

p.patient_problems(:all,:joins=>joins)

I get this error

ActionView::Template::Error (Mysql2::Error: Not unique table/alias: 'patient_problem': SELECT `patient_problem`.* FROM `patient_problem` LEFT OUTER JOIN party on party.id = patient_problem.patient_id 
        LEFT OUTER JOIN party_identifier on party.id = party_identifier.party_id
        LEFT OUTER JOIN blood_type on blood_type.id = party.blood_type_id
        LEFT OUTER JOIN education_level on education_level.id = party.education_level_id
        LEFT OUTER JOIN religion on religion.id = party.religion_id
        LEFT OUTER JOIN living_arrangement on living_arrangement.id = party.living_arrangement_id
      LEFT OUTER JOIN patient_problem patient_problem on patient_problem.patient_id = party.id and patient_problem.record_status_id = 1 
          left join (select user_type,username,user_id,auditable_id from (select MAX(id) id from audits where audits.auditable_type = 'PatientProblem' and user_type is not null group by auditable_id ) t inner join audits v on v.id=t.id ) entered_by1 on entered_by1.auditable_id = patient_problem.id
          left outer join user user1 on entered_by1.user_id = user1.id
          left outer join party as party_user1 on party_user1.id = user1.person_id
         LEFT OUTER JOIN patient_patient_search patient_patient_search1  on patient_patient_search1.patient_id = party.id
         left join search search1 on patient_patient_search1.patient_search_id = search1.id
         and patient_patient_search1.patient_search_id = '75' WHERE `patient_problem`.`patient_id` = 45 AND (`patient_problem`.`record_status_id` = 1) AND (  (patient_problem.occurrence_date > '2013-01-01 00:00:00' and patient_problem.occurrence_date < '2013-06-30 23:59:59' and   patient_problem.patient_problem_status_id in (5) and  patient_problem.code is not null and  patient_problem.code in ('10725009') )  and   (  patient_patient_search1.patient_search_id in (75.0) )  ))

Ofcourse I could do some string manipulation on the generated joins query and set alias to patient_problem. But I thought setting alias for associations would be more cleaner since the joins query generated are unpredictable(in my scenario)

Why do you want to alias the table name? There must be a better a approach. — Sully, Dec 23 '13 at 06:01
Because I'm gonna append a huge joins query(they also have patient_problem resulting in `unique table/alias` error ) to the above one. — Siva, Dec 23 '13 at 06:17
I do not see any joins. Is it a one-to-many? Why is there id=71 in the generated query? Explain what you are trying to do. — Sully, Dec 23 '13 at 07:16
My apologies id=71 is a typo. I've edited what I am trying to do — Siva, Dec 23 '13 at 07:27

score 4 · Answer 1 · answered Dec 23 '13 at 07:49

4

I am not sure what the variable joins is or how it was constructed. To alias tables in a join build your query like

Rails 3

PatientProblem.joins("as p1 OUTER JOIN patient_problem as p2 on ...")

or

PatientProblem.find(:all, :joins => "as p1 OUTER JOIN patient_problem as p2 ON ...")

answered Dec 23 '13 at 07:49

Sully

14,672
5
54
79

This just gives me ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "as" – Wylliam Judd Jul 26 '16 at 18:07
1

@WylliamJudd, for Rails 4 syntax check http://stackoverflow.com/a/19700056/643500 – Sully Jul 29 '16 at 01:04
I've found: "OUTER JOIN pateint_problem p1 on p1.item_id = items.id" – Wylliam Judd Jul 29 '16 at 21:52

score 0 · Answer 2 · answered Dec 23 '13 at 06:20

0

you can make singleton methods for that and write the query one time and use may time like

def self.p1
#your active record query here.
end

and call like

PatientProblem.p1

answered Dec 23 '13 at 06:20

Bharat soni

2,686
18
27

could do better with `find_by_sql` but this has nothing to do with aliasing associations – Siva Dec 23 '13 at 06:43
`PatientProblem.p1` is totally diffrent from `p.patient_problems` and also I'm looking for how to set alias table name for associations in run time – Siva Dec 23 '13 at 07:31

TrevTheDev · Answer 3 · 2013-12-24T07:34:28.320

-2

Update

You can simply change the table name in your code:

Patient.table_name="p2"

I'm not sure if this would break anything else though ... so good luck!

Orignal Answer

One solution may be to define a separate model for each type of patient_problem and then do something like this:

class PatientProblem2 < ActiveRecord::Base
 self.set_table_name "p2"
 ...
end

Another solution may be to use the ActiveRecord query interface which will allows for significant query flexibility: http://guides.rubyonrails.org/active_record_querying.html

Perhaps you can be more specific on the nature problem you are trying to solve.

edited Dec 24 '13 at 07:34

answered Dec 23 '13 at 05:59

TrevTheDev

2,616
2
18
36

The table name is still `patient_problem` he just wants to alias it – Sully Dec 23 '13 at 06:01
`p2` is alias name not table name – Siva Dec 23 '13 at 06:06
This will cause SQL to complain that there is no table named p2. – Diogenes Creosote Feb 13 '15 at 04:18

Rails - How to force associations to use alias table name

3 Answers3