Complicated find :include problem

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 Prete

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)

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

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 Prete

Hi Tyler,

Hi Tyler,

Tyler Prete 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.

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 Prete

Tyler Prete wrote:

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 Prete

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

------=_Part_60640_30085674.1158285582666
Content-Type: text/html; charset=ISO-8859-1
X-Google-AttachSize: 4010

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?
<br><br>As for using :listing.created_on, :users.username.<br><br>&nbsp;This doesn't work because I don't want it sorted by the Listing users username.<br><br>Let me try to give an example to explain better.&nbsp; Say I have four users, Adam, Bill, Cody, and Dan.
<br>A request belongs to a listing and a user, and a listing belongs to a user.&nbsp; So lets say I have 3 requests<br>All 3 requests belong to Dan.&nbsp; But they all belong to seperate listings, which belong to Adam, Bill, and Cody, respectively.
<br>(Note, listings could also belong to Dan, they just don't to keep the example simple)<br><br>I have these requests displayed in a table with sortable headers.&nbsp; One of which is Request User, which are all Dan in this case.
<br>Another is Listing User.&nbsp; This is what I want to sort by.&nbsp; So it should either sort Adam, Bill, Cody, or<br>Cody, Bill, Adam, depending on whether its ascending or descending.<br>However, to make things easy on myself (I have a lot of headers),
<br>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.<br><br><div><span class="gmail_quote">Perhaps aliases would help?<br><br>Thanks for the help so far,
<br>Tyler Prete<br><br>On 9/14/06, <b class="gmail_sendername">Daniel N</b> &lt;<a href="mailto:has.sox@gmail.com">has.sox@gmail.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><br><br><div></div><div><span class="q"><span class="gmail_quote">On 9/15/06, <b class="gmail_sendername">Tyler Prete</b> &lt;<a href="mailto:psyonic@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
psyonic@gmail.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>I have 3 models, requests, listings, and users.<br><br>requests belong to listings and users<br>listings belong to users<br><br>I have the associations all properly set up, so that is not the problem<br><br>What I am trying to do is sort a list of requests by
requests.listings.users<br><br>My find (that doesn't work) is similar to this.<br><br>Request.find(:all, :include =&gt; [:listing, :user], :order =&gt; &quot;requests.listings.user.username&quot;, :limit =&gt; 20)</div></blockquote>

</span></div><div><div><br>
I think the include should be <br>
:include =&gt; { :listing =&gt; :user }<br>
</div><br>
I'm not 100% on that though.</div><div><span class="q"><br>
<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div>Some things I've thought about... listing and request both belong
to the same users table, so my include is probably fine.&nbsp; But I
know that my order statement is wrong... to sort by
requests.users.username I just use &quot;
users.username&quot;, but for requests.listings.users.username I can't
use &quot;listings.users.username&quot; because it doesn't work, and
&quot;users.username&quot; isn't sorting by the right thing.&nbsp; Any clues?</div></blockquote></span></div><div><div><br>
The order is just an sql order option.&nbsp; If you want it to sort by listings then usernames, use something like<br>
<br>
:order =&gt; &quot;listings.created_at, users.username&quot; <br>
</div><br>
Hope that helps<br></div></div><div><span class="e" id="q_10dae7251e6f3449_5"><br>
<br>
<br>
</span></div></blockquote></div><br>

------=_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.

johnson_d@cox.net
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