ActiveRecord model that uses two tables (kind of like UNION

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

You an always do the SQL and get back an array of hashes. Then use
those hashes as you see fit. It is not “Object Oriented” but it is
much faster and clearer (if you know SQL). Use AR for what it is good
for (CRUD and simple navigation), use SQL for what it is good for
(complex queries, subqueries, etc). Do not be afraid to use multiple
tools. Not everything is a nail. (Not that you were suggesting
such).

Michael

Thanks for that very pragmatic answer Michael! After you replied I
went to dig into the Agile Rails book, and DHH says the same thing -
and I didn’t remember it. Hahah!
I’ve now created an extension that does these types of queries. Works
like a charm!

Thanks!

/JS

JS wrote:

Thanks for that very pragmatic answer Michael! After you replied I
went to dig into the Agile Rails book, and DHH says the same thing -
and I didn’t remember it. Hahah!
I’ve now created an extension that does these types of queries. Works
like a charm!

Thanks!

/JS

Hi, can you post the way you made that extension ? I mean, did you
created a new model, or it’s inside /lib or is a plugin ?

Thanks!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs