Order in find

Rails
1

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 W. [email protected]

2

Craig W. 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.

3

On Aug 1, 2007, at 6:52 PM, Craig W. wrote:

AS t1_r0, placements.“intake_date” AS t1_r1,
placements.“discharge_address” AS t1_r16, placements.“transfer_to”

Why would the ‘placement’ (client_slot belongs_to :placement) work
in ‘select’ but not ‘order’?

–
Craig W. [email protected]

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 B. http://agileconsultingllc.com
[email protected]

4

On Wed, 2007-08-01 at 20:02 -0400, Rob B. wrote:

client_slots.“handicapped_audible_visual” AS t0_r7, placements.“id”
placements.“recommendation” AS t1_r15,
My controller code looks like this…

   :conditions => cond.to_sql,

I assume that you have models something like (based on the *_id
belongs_to :case_manager
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 W. [email protected]

5

On Aug 1, 2007, at 8:29 PM, Craig W. wrote:

On Wed, 2007-08-01 at 20:02 -0400, Rob B. wrote:

On Aug 1, 2007, at 6:52 PM, Craig W. wrote:

Trying to order my find by a related table through a related
table…and
I get this error

>>> If I remove the ':order =>' part, it works fine but of course it's >> using Ruby or SQL >> I'll let you look at the docs because it's your problem, not mine). >> >> I have no clue what you're expecting the :select option to do (is > > > 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).

placements.“referral_date” AS t1_r2,
AS t1_r17, placements.“transfer_address” AS t1_r18,

Craig W. [email protected]

@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 B. http://agileconsultingllc.com
[email protected]

6

On Wed, 2007-08-01 at 22:36 -0400, Rob B. wrote:

   :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 W. [email protected]