Forum: Ruby on Rails order in find

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Craig W. (Guest)
on 2007-08-02 02:53
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Rob B. (Guest)
on 2007-08-02 04:10
(Received via mailing list)
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. <removed_email_address@domain.invalid>

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
removed_email_address@domain.invalid
Dondi S. (Guest)
on 2007-08-02 04:13
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.
Craig W. (Guest)
on 2007-08-02 04:30
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Rob B. (Guest)
on 2007-08-02 06:37
(Received via mailing list)
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
>>>
<repeated below>
>>> 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. <removed_email_address@domain.invalid>

@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
removed_email_address@domain.invalid
Craig W. (Guest)
on 2007-08-03 01:04
(Received via mailing list)
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. <removed_email_address@domain.invalid>
This topic is locked and can not be replied to.