Hi,
I’m sitting here pondering about how to solve this DB/AR related
problem. Being new to RoR it’s kind of difficult as I DO know how to
use the tools below the surface, but not all of the ActiveRecord Super
Powers.
What I’d like to do, is search several tables using a finder. I just
want to search the title field in this case. In SQL I would do it like
this:
SELECT msn.title AS title, ms.id, ‘MS’ as type FROM ms, msn WHERE
msn.id = ms.id AND title LIKE ‘%Yorkshire%’
UNION
SELECT jsn.title AS title, js.id, ‘JS’ as type FROM js, jsn WHERE
jsn.id = js.id AND title LIKE ‘%Yorkshire%’
ORDER BY title ASC;
(
titles are abstracted to a separate names tables, because names can
change over time, without that abstraction the SQL would look like
this:
SELECT ms.title AS title, ms.id, ‘MS’ as type FROM ms, msn WHERE
title LIKE ‘%Yorkshire%’
UNION
SELECT ms.title AS title, js.id, ‘JS’ as type FROM js, jsn WHERE
title LIKE ‘%Yorkshire%’
ORDER BY title ASC;
)
Giving me this result:
±---------------------------------------------------±----±-----+
| title | id | type |
±---------------------------------------------------±----±-----+
| Archaeology in South Yorkshire: | 526 | JS |
| South Yorkshire Industrial History Socitey Journal | 270 | JS |
| Yorkshire Archaeol Soc Lib Archiv Newsl | 514 | JS |
| Yorkshire Archaeological Journal: | 66 | JS |
| Yorkshire Archaeological Reports series | 142 | MS |
| Yorkshire Archaeological Society Record Series | 228 | MS |
| Yorkshire Buildings | 543 | JS |
±---------------------------------------------------±----±-----+
Ideally I’d like it to look something like this in the code:
result = MyCoolObject.find_by_title(“Yorkshire”)
I’ve toyed with the idea of creating a view, but then I’d be sticking
logic into the DB, and I’d rather not do this.
Another way is using Ferret, but it seems like a bit of an overkill
for this task.
Another way I can see this happening is to use a blank non-AR model
that access’ both of the other models MS/JS in this case, and sorting
the result in Ruby rather than in the DB.
Does anyone have a good Rails way of doing this?
Thanks!
/JS