Rails - May 2006 - find with belongs_to -> belongs_to -> belongs_to

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?

-- 
Posted via http://www.ruby-forum.com/.

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

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

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 ...
-- 
Posted via http://www.ruby-forum.com/.