Can this 1337 PostgreSQL Subquery Union be done in AR?

Rails
1

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

2

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 R. 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

3

Cayce B. 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 :wink:

Joe

4

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);