Rails - Sep 2006 - Can this 1337 PostgreSQL Subquery Union be done in AR?

Just wondering if it''s possible, without resorting to find_by_sql.

select id, title, datetime, type from (
select id, title, added as datetime, ''items'' as type from
items
union
select id, headline as title, datetime, ''news'' as type from
news
) as items order by datetime desc limit 50;

I''m also curious if it''s possible in MySQL.

Joe

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

I would think that in both db''s you''d be best off by creating
a view
from the subquery and then modelling it with an AR class. You can then 
access your data from the view with the AR model as if it was a table, 
without having to resort to find_by_sql.

c.


Joe Ruby MUDCRAP-CE wrote:
> Just wondering if it''s possible, without resorting to find_by_sql.
> 
> select id, title, datetime, type from (
> select id, title, added as datetime, ''items'' as type from
items
> union
> select id, headline as title, datetime, ''news'' as type
from news
> ) as items order by datetime desc limit 50;
> 
> I''m also curious if it''s possible in MySQL.
> 
> Joe

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

Cayce Balara wrote:
> I would think that in both db''s you''d be best off by
creating a view
> from the subquery and then modelling it with an AR class. You can then 
> access your data from the view with the AR model as if it was a table, 
> without having to resort to find_by_sql.
Yeah, but creating a model just for it is overkill IMO. And I don''t 
think either database supports updateable views (I don''t know if MySQL 
has views (or subqueries or union) now, as I haven''t paid attention to 
it for quite a while). Also, migrations don''t work with views.

Thanks though ;)

Joe

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

> > I would think that in both db''s you''d be best off by
creating a view
> > from the subquery and then modelling it with an AR class. You can then
> > access your data from the view with the AR model as if it was a table,
> > without having to resort to find_by_sql.
>
> Yeah, but creating a model just for it is overkill IMO. And I
don''t
> think either database supports updateable views (I don''t know if
MySQL
> has views (or subqueries or union) now, as I haven''t paid
attention to
> it for quite a while). Also, migrations don''t work with views.
Naturally, views aren''t updateable, but in PostgreSQL it is easily
achievable nonetheless. Simply create a rule that defines what to do
when an insert/delete/update operation is attempted on the view -
something like this:

CREATE RULE myview_insert AS ON INSERT TO myview DO INSTEAD
    INSERT INTO mytable (id, name, phone)
    VALUES (new.id, new.name, new.phone);


--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---