# Order in find **URL:** \ **Category:** Rails\ **Created:** [August 2, 2007, 12:53am UTC](https://www.ruby-forum.com/t/order-in-find/111028 "2007-08-02T00:53:36Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 12:53am UTC](https://www.ruby-forum.com/t/order-in-find/111028/1 "2007-08-02T00:53:36Z")
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](mailto:removed_email_address@domain.invalid) ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 2:13am UTC](https://www.ruby-forum.com/t/order-in-find/111028/2 "2007-08-02T02:13:54Z")
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. ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 2:10am UTC](https://www.ruby-forum.com/t/order-in-find/111028/3 "2007-08-02T02:10:41Z")
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](mailto: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](http://agileconsultingllc.com) removed\_email\_address@domain.invalid ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 2:30am UTC](https://www.ruby-forum.com/t/order-in-find/111028/4 "2007-08-02T02:30:42Z")
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](mailto:removed_email_address@domain.invalid) ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 4:37am UTC](https://www.ruby-forum.com/t/order-in-find/111028/5 "2007-08-02T04:37:54Z")
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. [removed\_email\_address@domain.invalid](mailto: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](http://agileconsultingllc.com) removed\_email\_address@domain.invalid ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [August 2, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/order-in-find/111028/6 "2007-08-02T23:04:20Z")
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](mailto:removed_email_address@domain.invalid)