Complicated find :include problem

Rails
1

I have 3 models, requests, listings, and users.

requests belong to listings and users
listings belong to users

I have the associations all properly set up, so that is not the problem

What I am trying to do is sort a list of requests by
requests.listings.users

My find (that doesn’t work) is similar to this.

Request.find(:all, :include => [:listing, :user], :order => "
requests.listings.user.username", :limit => 20)

Some things I’ve thought about… listing and request both belong to the
same users table, so my include is probably fine. But I know that my
order
statement is wrong… to sort by requests.users.username I just use "
users.username", but for requests.listings.users.username I can’t use "
listings.users.username" because it doesn’t work, and “users.username”
isn’t
sorting by the right thing. Any clues?

Thanks in advance,
–Tyler P.

2

On 9/15/06, Tyler P. [email protected] wrote:

My find (that doesn’t work) is similar to this.

Request.find(:all, :include => [:listing, :user], :order => "
requests.listings.user.username", :limit => 20)

I think the include should be
:include => { :listing => :user }

I’m not 100% on that though.

Some things I’ve thought about… listing and request both belong to the

same users table, so my include is probably fine. But I know that my order
statement is wrong… to sort by requests.users.username I just use "
users.username", but for requests.listings.users.username I can’t use "
listings.users.username" because it doesn’t work, and “users.username”
isn’t sorting by the right thing. Any clues?

The order is just an sql order option. If you want it to sort by
listings
then usernames, use something like

:order => “listings.created_at, users.username”

Hope that helps

3

Well here is the thing… the conditions of this find statement are
such
that sometimes it might need to include the requests users, and
sometimes it
might need the listings users, so I think I might need :include => [{
:listing => :user }, :user]. That leaves me with a problem though…
how do
I differentiate between the two when giving an order statement? And does
:listing => :user also include the listing data?

As for using :listing.created_on, :users.username.

This doesn’t work because I don’t want it sorted by the Listing users
username.

Let me try to give an example to explain better. Say I have four users,
Adam, Bill, Cody, and Dan.
A request belongs to a listing and a user, and a listing belongs to a
user.
So lets say I have 3 requests
All 3 requests belong to Dan. But they all belong to seperate listings,
which belong to Adam, Bill, and Cody, respectively.
(Note, listings could also belong to Dan, they just don’t to keep the
example simple)

I have these requests displayed in a table with sortable headers. One
of
which is Request User, which are all Dan in this case.
Another is Listing User. This is what I want to sort by. So it should
either sort Adam, Bill, Cody, or
Cody, Bill, Adam, depending on whether its ascending or descending.
However, to make things easy on myself (I have a lot of headers),
all of these have to fit into the same find statement, with only the
order
different, which is why the :include must be the same.

Perhaps aliases would help?

Thanks for the help so far,
Tyler P.

4

Hi Tyler,

Hi Tyler,

Tyler P. wrote:

I have the associations all properly set up,
so that is not the problem

Please don’t take this the wrong way, but I couldn’t help but respond
when I
saw this. I can’t begin to count the number of times I’ve said
something
like that and then lived to regret it. My first CS prof. taught us a
‘dittie’ that’s served me well.

I hate this damned computer.
I think I’m gonna sell it.
It never does just what I want.
Only what I tell it.

Best regards,
Bill

5

BIll,

That made me laugh :slight_smile: I appreciate the humor, and I get your point.
There
is always a chance that I screwed up somewhere before and its finally
come
back to haunt me. In this case, however, that doesn’t seem (to me at
least)
to be the problem.

Thanks for the laugh, perhaps it will help clear my head!
Tyler P.

6

Tyler P. wrote:

username.
I have these requests displayed in a table with sortable headers. One of
Thanks for the help so far,

requests belong to listings and users
requests.listings.user.username", :limit => 20)
statement is wrong… to sort by requests.users.username I just use "
Hope that helps


As for using :listing.created_on, :users.username.

 This doesn’t work because I don’t want it sorted by the Listing users username.

Let me try to give an example to explain better.  Say I have four users, Adam, Bill, Cody, and Dan.



“users.username” isn’t sorting by the right thing.  Any clues?


The order is just an sql order option.  If you want it to sort by listings then usernames, use something like



:order => “listings.created_at, users.username”

Hope that helps




------=_Part_60640_30085674.1158285582666–

I would drop into straight SQL at this point so that there was no
ambiguity. Rails is slick, but it’s not magic. If the concept is
easier to express in SQL use that.

It also sounds like you have two distinct pieces of business, requiring
two distinct “find” statements. Conditional logic at this level is
generally a sign of a misunderstanding, in my experience.

Without understanding the business problem driving this possibly
conditional query, I find it difficult to offer a concrete solution.
Can you please explain what you want in business (not technical) terms,
submit the DDL for the tables and explain the relationships? We might
be able to offer better advice.

7

[email protected] said:
I would drop into straight SQL at this point so that there was no

ambiguity. Rails is slick, but it’s not magic. If the concept is

easier to express in SQL use that.

I agree here. Sometimes, when SQL statements get too complex,
find_by_sql
is your best bet. Anytime I have a query that runs often and/or has
several
joins, I use find_by_sql. Rails’ SQL builder isn’t always the most
efficient.

ed