# Can this 1337 PostgreSQL Subquery Union be done in AR?

**URL:** <https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414>\
**Category:** Rails\
**Created:** [September 20, 2006, 10:26pm UTC](https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414 "2006-09-20T22:26:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [September 20, 2006, 10:26pm UTC](https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414/1 "2006-09-20T22:26:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [September 21, 2006, 5:08am UTC](https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414/2 "2006-09-21T05:08:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [September 21, 2006, 6:24am UTC](https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414/3 "2006-09-21T06:24:15Z")

</div>

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 😉

Joe

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [September 21, 2006, 10:33am UTC](https://www.ruby-forum.com/t/can-this-1337-postgresql-subquery-union-be-done-in-ar/69414/4 "2006-09-21T10:33:05Z")

</div>

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