JS
2007-May-31 18:30 UTC
ActiveRecord model that uses two tables (kind of like UNION in SQL)
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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
MichaelLatta
2007-May-31 19:10 UTC
Re: ActiveRecord model that uses two tables (kind of like UNION in SQL)
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 On May 31, 11:30 am, JS <the.jo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
JS
2007-Jun-01 17:09 UTC
Re: ActiveRecord model that uses two tables (kind of like UNION in SQL)
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 On May 31, 8:10 pm, MichaelLatta <lat...-ee4meeAH724@public.gmane.org> wrote:> 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 > > On May 31, 11:30 am, JS <the.jo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Benoror Benoror
2009-Jul-21 18:50 UTC
Re: ActiveRecord model that uses two tables (kind of like UN
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! > > /JSHi, 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! -- Posted via http://www.ruby-forum.com/.