[Rails] Re: SQL query problem

On Dec 18, 12:10 am, Ease Bus <ease...@gmail.com> wrote:
> Please help me with finding a SQL select statement for the following  
> problems.
>
> I have two classes: Site and Link
>
> Table Link has two columns: linking_site_id, and linked_site_id
>
> I would like to find all the links in table Link where linking_site_id  
> = 3 or linked_site_id = 3 but only include a link where linked_site_id  
> = 3 if the linking_site_id in that link is not one of the  
> linked_site_id that site 3 links to.  For example, if the Link table  
> contains the following rows
>
> Linking_site            Linked_site
> 2                               1
> 3                               2
> 3                               4
> 5                               3
> 2                               3
>
> What SQL select statement would return only the following:
>
> 3                               2
> 3                               4
> 5                               3
>
> If SQL query alone cannot do this, what is the most efficient way to  
> do it via RoR?  Thanks.

Probably not the most efficient, but this will do what you're looking
for:

linking_sites = Link.find_by_linked_site_id(3).map { |l|
l.linking_site_id }
result = Link.find(:all, :conditions => ['linking_site_id = :base OR
(linked_site_id = :base AND linking_site_id NOT IN (:sites)', { :base
=> 3, :sites => linking_sites })

It has the advantage of being fairly close to the original text
description as well, so when you see it again in a year you'll know
what it does... :)

--Matt Jones

--

You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.