# Find with belongs\_to -\> belongs\_to -\> belongs\_to

**URL:** <https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177>\
**Category:** Rails\
**Created:** [May 21, 2006, 2:06am UTC](https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177 "2006-05-21T02:06:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [May 21, 2006, 2:06am UTC](https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177/1 "2006-05-21T02:06:03Z")

</div>

I have 3 tables

keywords (keywords attached to a file)  
files (info about the file)  
paths (path for the file)

[This is a legacy database, so I can’t change this]

keyword belongs\_to file and file belongs\_to path. Users need to be able  
to search the keywords, but they only have access to certain volumes, so  
the results need to be limited by the path. I also need to show paging,  
like “Results 11 - 20 of 315”, and possible sort the results.

I guess I can do a search on the keywords and compare the results  
against the accessible paths, but (1) it seems like this would make  
paging hard, and (2) wouldn’t it be more efficient to let MySQL do the  
work if possible?

Can this be done with some kind of find statement? If all else fails,  
can this be done with a raw SQL query?

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [May 21, 2006, 3:26am UTC](https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177/2 "2006-05-21T03:26:46Z")

</div>

On May 21, 2006, at 9:06 , Brian A. wrote:

> I have 3 tables
> 
> keywords (keywords attached to a file)  
> files (info about the file)  
> paths (path for the file)

> Can this be done with some kind of find statement? If all else fails,  
> can this be done with a raw SQL query?

From your description, users probably have many paths as well (to  
show which ones they have access to). If so, I’d think something like

@files = Users.paths.files.keywords.find\_all(:conditions =\> “keywords  
in (‘keyword1’,‘keyword2’)”)

would be what you want to look for. I really don’t know if that  
syntax is anything near right, but that’s where I’d start.

From a raw SQL standpoint, I’d do something like:

sql = \<\<-SQL  
select \*  
from files  
join (  
select file\_id  
from files  
join paths using (path\_id)  
join files\_keywords using (file\_id)  
join keywords using (keyword\_id)  
join users using (path\_id)  
where keyword in (‘keyword1’,‘keyword2’)  
) as found\_files  
order by …  
SQL  
@files = File.find\_by\_sql([sql,…])

Hope this helps.

Michael G.  
grzm seespotcode net

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [May 21, 2006, 3:39am UTC](https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177/3 "2006-05-21T03:39:05Z")

</div>

On May 21, 2006, at 10:25 , Michael G. wrote:

> From a raw SQL standpoint, I’d do something like:

Minor correction (forgot to add a join clause, using users\_paths  
rather than users, added user\_id restriction)

sql = \<\<-SQL  
select \*  
from files  
join (  
select file\_id  
from files  
join paths using (path\_id)  
join files\_keywords using (file\_id)  
join keywords using (keyword\_id)  
join users\_paths using (path\_id)  
where keyword in (‘keyword1’,‘keyword2’)  
and user\_id = ?  
) as found\_files using (file\_id)  
order by …  
SQL  
@files = File.find\_by\_sql([sql,…])

And if you need to compare the path explicitly (i.e., you _don’t_  
have a users/path), you could do

select \*  
from files  
join (  
select file\_id  
from files  
join paths using (path\_id)  
join files\_keywords using (file\_id)  
join keywords using (keyword\_id)  
where keyword in (‘keyword1’,‘keyword2’)  
and path = ?  
) as found\_files using (file\_id)  
order by …

and use LIKE or some other comparison method (e.g., regex matching)  
for a little added flexibility.

Michael G.  
grzm seespotcode net

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [May 21, 2006, 5:35pm UTC](https://www.ruby-forum.com/t/find-with-belongs-to-belongs-to-belongs-to/61177/4 "2006-05-21T17:35:40Z")

</div>

Thanks. I hadn’t thought about taking the level of abstraction back to  
users; that’s cool. But the problem is, while I can establish a user  
has\_many paths relationship, the picture is complicated by groups. Users  
can have paths, but can also belong to groups that have paths.

So it looks like I’ll be trying your SQL. Thanks so much.

Michael G. wrote:

> And if you need to compare the path explicitly (i.e., you _don’t_  
> have a users/path), you could do
> 
> select \*  
> from files  
> join (  
> select file\_id  
> from files  
> join paths using (path\_id)  
> join files\_keywords using (file\_id)  
> join keywords using (keyword\_id)  
> where keyword in (‘keyword1’,‘keyword2’)  
> and path = ?  
> ) as found\_files using (file\_id)  
> order by …