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?