Trying to order my find by a related table through a related table...and
I get this error
PGError: ERROR: schema "placement" does not exist
: SELECT client_slots."id" AS t0_r0, client_slots."name" AS
t0_r1, client_slots."facility_id" AS t0_r2,
client_slots."phone_number" AS t0_r3,
client_slots."placement_id" AS t0_r4, client_slots."gender"
AS t0_r5, client_slots."handicapped_wheelchair" AS t0_r6,
client_slots."handicapped_audible_visual" AS t0_r7,
placements."id" AS t1_r0, placements."intake_date" AS t1_r1,
placements."referral_date" AS t1_r2,
placements."packet_received_date" AS t1_r3,
placements."acceptance_date" AS t1_r4,
placements."admission_date" AS t1_r5,
placements."discharge_date" AS t1_r6,
placements."processor_name" AS t1_r7,
placements."discharged_to" AS t1_r8, placements."client_id"
AS t1_r9, placements."facility_id" AS t1_r10,
placements."case_manager_id" AS t1_r11,
placements."packet_requested_date" AS t1_r12,
placements."accepted" AS t1_r13, placements."move_in_date"
AS t1_r14, placements."recommendation" AS t1_r15,
placements."discharge_address" AS t1_r16,
placements."transfer_to" AS t1_r17,
placements."transfer_address" AS t1_r18, placements."pltype"
AS t1_r19, placements."estimated_discharge_date" AS t1_r20,
placements."client_slot_id" AS t1_r21,
placements."outpatient_client_slot_id" AS t1_r22 FROM client_slots
LEFT OUTER JOIN placements ON placements.id = client_slots.placement_id WHERE
((placement_id IS NOT NULL)) ORDER BY placement.client.lastname
My controller code looks like this...
@client_slots = ClientSlot.find(:all,
:conditions => cond.to_sql,
:include => ''placement'',
:select => ''placement.client.lastname,
placement.client.gov_id'',
:order => ''placement.client.lastname'')
If I remove the '':order =>'' part, it works fine but of
course it''s not ordered. When I add the '':order''
part, I get the error as indicated above.
Why would the ''placement'' (client_slot belongs_to :placement)
work in ''select'' but not ''order''?
--
Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Aug 1, 2007, at 6:52 PM, Craig White wrote:> Trying to order my find by a related table through a related > table...and > I get this error > > PGError: ERROR: schema "placement" does not exist > : SELECT client_slots."id" AS t0_r0, client_slots."name" AS t0_r1, > client_slots."facility_id" AS t0_r2, client_slots."phone_number" AS > t0_r3, client_slots."placement_id" AS t0_r4, client_slots."gender" > AS t0_r5, client_slots."handicapped_wheelchair" AS t0_r6, > client_slots."handicapped_audible_visual" AS t0_r7, placements."id" > AS t1_r0, placements."intake_date" AS t1_r1, > placements."referral_date" AS t1_r2, > placements."packet_received_date" AS t1_r3, > placements."acceptance_date" AS t1_r4, placements."admission_date" > AS t1_r5, placements."discharge_date" AS t1_r6, > placements."processor_name" AS t1_r7, placements."discharged_to" AS > t1_r8, placements."client_id" AS t1_r9, placements."facility_id" AS > t1_r10, placements."case_manager_id" AS t1_r11, > placements."packet_requested_date" AS t1_r12, placements."accepted" > AS t1_r13, placements."move_in_date" AS t1_r14, > placements."recommendation" AS t1_r15, > placements."discharge_address" AS t1_r16, placements."transfer_to" > AS t1_r17, placements."transfer_address" AS t1_r18, > placements."pltype" AS t1_r19, > placements."estimated_discharge_date" AS t1_r20, > placements."client_slot_id" AS t1_r21, > placements."outpatient_client_slot_id" AS t1_r22 FROM client_slots > LEFT OUTER JOIN placements ON placements.id = > client_slots.placement_id WHERE ((placement_id IS NOT NULL)) ORDER > BY placement.client.lastname > > My controller code looks like this... > > @client_slots = ClientSlot.find(:all, > :conditions => cond.to_sql, > :include => ''placement'', > :select => ''placement.client.lastname, placement.client.gov_id'', > :order => ''placement.client.lastname'') > > If I remove the '':order =>'' part, it works fine but of course it''s > not ordered. When I add the '':order'' part, I get the error as > indicated above. > > Why would the ''placement'' (client_slot belongs_to :placement) work > in ''select'' but not ''order''? > > -- > Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>You have to pay attention to the pluralization and to whether you''re using Ruby or SQL @client_slots = ClientSlot.find(:all, :conditions => cond.to_sql, :include => :placement, # changed to a symbol just because it''s more idiomatic :select => ''placement.client.lastname, placement.client.gov_id'', :order => ''placements.client.lastname'') I think that :select might be ignored in the face of :include (but I''ll let you look at the docs because it''s your problem, not mine). In any case, you need to have SQL fragments in there, but you have Ruby (ActiveRecord) syntax. I assume that you have models something like (based on the *_id columns present in the query): class ClientSlot has_many :placements belongs_to :facility belongs_to :placement class Placement belongs_to :facility belongs_to :client_slot belongs_to :case_manager belongs_to :client Now this doesn''t completely make sense so you probably should show your actual models. You have both client_slots.placement_id and placements.client_slot_id columns. I have no clue what you''re expecting the :select option to do (is placement.client.gov_id a key to the govs table or something like a United States Social Security number?). Give us better information and you''ll likely get better assistance. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Craig White wrote:> My controller code looks like this... > > @client_slots = ClientSlot.find(:all, > :conditions => cond.to_sql, > :include => ''placement'', > :select => ''placement.client.lastname, placement.client.gov_id'', > :order => ''placement.client.lastname'') >The ActiveRecord::Base documentation for "find" says that the option :order is an SQL fragment. Same with the :select option, which would be why it doesn''t seem to have any effect in your example. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Wed, 2007-08-01 at 20:02 -0400, Rob Biedenharn wrote:> On Aug 1, 2007, at 6:52 PM, Craig White wrote: > > Trying to order my find by a related table through a related > > table...and > > I get this error > > > > PGError: ERROR: schema "placement" does not exist > > : SELECT client_slots."id" AS t0_r0, client_slots."name" AS t0_r1, > > client_slots."facility_id" AS t0_r2, client_slots."phone_number" AS > > t0_r3, client_slots."placement_id" AS t0_r4, client_slots."gender" > > AS t0_r5, client_slots."handicapped_wheelchair" AS t0_r6, > > client_slots."handicapped_audible_visual" AS t0_r7, placements."id" > > AS t1_r0, placements."intake_date" AS t1_r1, > > placements."referral_date" AS t1_r2, > > placements."packet_received_date" AS t1_r3, > > placements."acceptance_date" AS t1_r4, placements."admission_date" > > AS t1_r5, placements."discharge_date" AS t1_r6, > > placements."processor_name" AS t1_r7, placements."discharged_to" AS > > t1_r8, placements."client_id" AS t1_r9, placements."facility_id" AS > > t1_r10, placements."case_manager_id" AS t1_r11, > > placements."packet_requested_date" AS t1_r12, placements."accepted" > > AS t1_r13, placements."move_in_date" AS t1_r14, > > placements."recommendation" AS t1_r15, > > placements."discharge_address" AS t1_r16, placements."transfer_to" > > AS t1_r17, placements."transfer_address" AS t1_r18, > > placements."pltype" AS t1_r19, > > placements."estimated_discharge_date" AS t1_r20, > > placements."client_slot_id" AS t1_r21, > > placements."outpatient_client_slot_id" AS t1_r22 FROM client_slots > > LEFT OUTER JOIN placements ON placements.id = > > client_slots.placement_id WHERE ((placement_id IS NOT NULL)) ORDER > > BY placement.client.lastname > > > > My controller code looks like this... > > > > @client_slots = ClientSlot.find(:all, > > :conditions => cond.to_sql, > > :include => ''placement'', > > :select => ''placement.client.lastname, placement.client.gov_id'', > > :order => ''placement.client.lastname'') > > > > If I remove the '':order =>'' part, it works fine but of course it''s > > not ordered. When I add the '':order'' part, I get the error as > > indicated above. > > > > Why would the ''placement'' (client_slot belongs_to :placement) work > > in ''select'' but not ''order''? > > > > -- > > Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> > > You have to pay attention to the pluralization and to whether you''re > using Ruby or SQL > > @client_slots = ClientSlot.find(:all, > :conditions => cond.to_sql, > :include => :placement, # changed to a symbol > just because it''s more idiomatic > :select => ''placement.client.lastname, placement.client.gov_id'', > :order => ''placements.client.lastname'') > > I think that :select might be ignored in the face of :include (but > I''ll let you look at the docs because it''s your problem, not mine). > In any case, you need to have SQL fragments in there, but you have > Ruby (ActiveRecord) syntax. > > I assume that you have models something like (based on the *_id > columns present in the query): > > class ClientSlot > has_many :placements > belongs_to :facility > belongs_to :placement > > class Placement > belongs_to :facility > belongs_to :client_slot > belongs_to :case_manager > belongs_to :client > > Now this doesn''t completely make sense so you probably should show > your actual models. You have both client_slots.placement_id and > placements.client_slot_id columns. > > I have no clue what you''re expecting the :select option to do (is > placement.client.gov_id a key to the govs table or something like a > United States Social Security number?). > > Give us better information and you''ll likely get better assistance.---- sorry...I try to keep it short. class ClientSlot belongs_to :facility belongs_to :placement class Placement belongs_to :client belongs_to :facility has_one :client_slot class Client has_many :placements (I''m not including various other relations such as CaseManager which aren''t part of the issue) Thus, I am finding specific ''ClientSlots'' each of which belongs to one placement each of which belongs to one Client and that is the order I am looking for. :order => ''placements.client.lastname'') returns a similar error as the singular version (in fact, I had tried the plural version before asking the list) PGError: ERROR: schema "placements" does not exist : SELECT client_slots."id" AS t0_r0, client_slots."name" AS t0_r1, client_slots."facility_id" AS t0_r2, client_slots."phone_number" AS t0_r3, client_slots."placement_id" AS t0_r4, client_slots."gender" AS t0_r5, client_slots."handicapped_wheelchair" AS t0_r6, client_slots."handicapped_audible_visual" AS t0_r7, placements."id" AS t1_r0, placements."intake_date" AS t1_r1, placements."referral_date" AS t1_r2, placements."packet_received_date" AS t1_r3, placements."acceptance_date" AS t1_r4, placements."admission_date" AS t1_r5, placements."discharge_date" AS t1_r6, placements."processor_name" AS t1_r7, placements."discharged_to" AS t1_r8, placements."client_id" AS t1_r9, placements."facility_id" AS t1_r10, placements."case_manager_id" AS t1_r11, placements."packet_requested_date" AS t1_r12, placements."accepted" AS t1_r13, placements."move_in_date" AS t1_r14, placements."recommendation" AS t1_r15, placements."discharge_address" AS t1_r16, placements."transfer_to" AS t1_r17, placements."transfer_address" AS t1_r18, placements."pltype" AS t1_r19, placements."estimated_discharge_date" AS t1_r20, placements."client_slot_id" AS t1_r21, placements."outpatient_client_slot_id" AS t1_r22 FROM client_slots LEFT OUTER JOIN placements ON placements.id = client_slots.placement_id WHERE ((placement_id IS NOT NULL)) ORDER BY placements.client.lastname Thanks -- Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Aug 1, 2007, at 8:29 PM, Craig White wrote:> On Wed, 2007-08-01 at 20:02 -0400, Rob Biedenharn wrote: >> On Aug 1, 2007, at 6:52 PM, Craig White wrote: >>> Trying to order my find by a related table through a related >>> table...and >>> I get this error >>><repeated below>>>> >>> My controller code looks like this... >>> >>> @client_slots = ClientSlot.find(:all, >>> :conditions => cond.to_sql, >>> :include => ''placement'', >>> :select => ''placement.client.lastname, >>> placement.client.gov_id'', >>> :order => ''placement.client.lastname'') >>> >>> If I remove the '':order =>'' part, it works fine but of course it''s >>> not ordered. When I add the '':order'' part, I get the error as >>> indicated above. >>> >>> Why would the ''placement'' (client_slot belongs_to :placement) work >>> in ''select'' but not ''order''? >>> >>> -- >>> Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> >> >> You have to pay attention to the pluralization and to whether you''re >> using Ruby or SQL >> >> @client_slots = ClientSlot.find(:all, >> :conditions => cond.to_sql, >> :include => :placement, # changed to a symbol >> just because it''s more idiomatic >> :select => ''placement.client.lastname, >> placement.client.gov_id'', >> :order => ''placements.client.lastname'') >> >> I think that :select might be ignored in the face of :include (but >> I''ll let you look at the docs because it''s your problem, not mine). >> In any case, you need to have SQL fragments in there, but you have >> Ruby (ActiveRecord) syntax. >> >> I assume that you have models something like (based on the *_id >> columns present in the query): >> >> class ClientSlot >> has_many :placements >> belongs_to :facility >> belongs_to :placement >> >> class Placement >> belongs_to :facility >> belongs_to :client_slot >> belongs_to :case_manager >> belongs_to :client >> >> Now this doesn''t completely make sense so you probably should show >> your actual models. You have both client_slots.placement_id and >> placements.client_slot_id columns. >> >> I have no clue what you''re expecting the :select option to do (is >> placement.client.gov_id a key to the govs table or something like a >> United States Social Security number?). >> >> Give us better information and you''ll likely get better assistance. > ---- > sorry...I try to keep it short. > > class ClientSlot > belongs_to :facility > belongs_to :placement > > class Placement > belongs_to :client > belongs_to :facility > has_one :client_slot > > class Client > has_many :placements > > (I''m not including various other relations such as CaseManager which > aren''t part of the issue) > > Thus, I am finding specific ''ClientSlots'' each of which belongs to one > placement each of which belongs to one Client and that is the order > I am > looking for. > > :order => ''placements.client.lastname'')You''re using Ruby syntax here, but this is an SQL fragment that goes directly into the "ORDER BY" clause. That''s why it thinks "placements" is a schema (and "client" is a table and "lastname" is a column).> returns a similar error as the singular version (in fact, I had tried > the plural version before asking the list) > > PGError: ERROR: schema "placements" does not exist > : SELECT client_slots."id" AS t0_r0, client_slots."name" AS t0_r1, > client_slots."facility_id" AS t0_r2, client_slots."phone_number" AS > t0_r3, client_slots."placement_id" AS t0_r4, client_slots."gender" > AS t0_r5, client_slots."handicapped_wheelchair" AS t0_r6, > client_slots."handicapped_audible_visual" AS t0_r7, placements."id" > AS t1_r0, placements."intake_date" AS t1_r1, > placements."referral_date" AS t1_r2, > placements."packet_received_date" AS t1_r3, > placements."acceptance_date" AS t1_r4, placements."admission_date" > AS t1_r5, placements."discharge_date" AS t1_r6, > placements."processor_name" AS t1_r7, placements."discharged_to" AS > t1_r8, placements."client_id" AS t1_r9, placements."facility_id" AS > t1_r10, placements."case_manager_id" AS t1_r11, > placements."packet_requested_date" AS t1_r12, placements."accepted" > AS t1_r13, placements."move_in_date" AS t1_r14, > placements."recommendation" AS t1_r15, > placements."discharge_address" AS t1_r16, placements."transfer_to" > AS t1_r17, placements."transfer_address" AS t1_r18, > placements."pltype" AS t1_r19, > placements."estimated_discharge_date" AS t1_r20, > placements."client_slot_id" AS t1_r21, > placements."outpatient_client_slot_id" AS t1_r22 FROM client_slots > LEFT OUTER JOIN placements ON placements.id = > client_slots.placement_id WHERE ((placement_id IS NOT NULL)) ORDER > BY placements.client.lastname > > Thanks > > -- > Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>@client_slots = ClientSlot.find(:all, :conditions => cond.to_sql, :include => { :placement => :client }, :order => ''clients.lastname'') You don''t give your conditions, so I can''t comment on cond.to_sql except to say that "placement_id IS NOT NULL" is rather useless. You have a join on client_slots.placement_id = placements.id which is (I presume) a primary key and cannot ever be NULL. :select => ''placement.client.lastname, placement.client.gov_id'', I still don''t know what you''re hoping this does (as I said before, the :include option probably trumps it). -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Wed, 2007-08-01 at 22:36 -0400, Rob Biedenharn wrote:> @client_slots = ClientSlot.find(:all, > :conditions => cond.to_sql, > :include => { :placement => :client }, > :order => ''clients.lastname'') > > You don''t give your conditions, so I can''t comment on cond.to_sql > except to say that "placement_id IS NOT NULL" is rather useless. You > have a join on client_slots.placement_id = placements.id which is (I > presume) a primary key and cannot ever be NULL. > > :select => ''placement.client.lastname, placement.client.gov_id'', > I still don''t know what you''re hoping this does (as I said before, > the :include option probably trumps it). >---- Wanted to say thanks Rob - I simplified things a bit because it was so frustrating. I ended up adding a belongs_to relationship from client_slots to clients even though the notion of ''through'' placements, there is no singular notion of ''through'' as it is only available on has_many relationships. So for the price of some pain to make certain via controller code, that when the placement is altered in client_slots model, so too is the client_id (in the client_slots), which of course, having a direct relationship between client_slots and clients allows me to order the returned values Thanks -- Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---