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?
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
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
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 …