order in find

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'?

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@AgileConsultingLLC.com

> 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@tobyhouse.com>

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.

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@tobyhouse.com>

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@tobyhouse.com>

@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@AgileConsultingLLC.com

@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).