Forum: Ruby on Rails find with belongs_to -> belongs_to -> belongs_to

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
21c26386eabccd11f40c029b0ebf94e7?d=identicon&s=25 Brian Ablaza (zakifataya)
on 2006-05-21 02:06
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?
Ae82cad40a0caca9c932d45c7a9eb3cd?d=identicon&s=25 Michael Glaesemann (Guest)
on 2006-05-21 03:26
(Received via mailing list)
On May 21, 2006, at 9:06 , Brian Ablaza 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 Glaesemann
grzm seespotcode net
Ae82cad40a0caca9c932d45c7a9eb3cd?d=identicon&s=25 Michael Glaesemann (Guest)
on 2006-05-21 03:39
(Received via mailing list)
On May 21, 2006, at 10:25 , Michael Glaesemann 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 Glaesemann
grzm seespotcode net
21c26386eabccd11f40c029b0ebf94e7?d=identicon&s=25 Brian Ablaza (zakifataya)
on 2006-05-21 17:35
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 Glaesemann 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 ...
This topic is locked and can not be replied to.