Union all

Rails
1

Hi there.

I have two models (and two tables, of course), let’s call them Room and
Reserved, with similar fields but with the last one with some extra
info, and need to get data from both using a “union all”. Let’s think on
a hotel where I need to “glue” data to take a snapshot from the
occupation like:

Room (number,date,ocupied_by)
1,‘2006/06/01’,John
2,‘2006/06/01’,Paul

Reserved (number,reserved_on,reserved_by,expire_on)
3,‘2006/06/01’,George,‘2006/06/02 14:00’
4,‘2006/06/01’,Ringo ,‘2006/06/02 14:00’

select number, date, ocupied_by
from room
where date=‘2006/06/01’
union all
select number, reserved_on as date, reserved_by as ocupied_by
from reserved
where reserved_on=‘2006/06/01’
order by 1

How can I do that with ActiveRecord/Rails? Ok, I’m not so sure it’s a
good example/tables to illustrate the question, but the idea is using an
“union all” on the data from two tables as on regular SQL queries.

Thanks!

2

Matthew P. wrote:

Hey Matt!

Now, if what you need to do is really union two tables (eg legacy
database, or STI just doesn’t do it for you), then you can either just
do
two separate queries and add the two resultsets together:

Yes, that’s my case.

all_of_it = Room.find(:all) + Reservation.find(:all)

I was thinking doing that way but really needed to be sure that there
wasn’t a better way, thanks for your answer! I’ll make this way, so I
can get all the data from both. The reservation table really have a lot
of other fields that the room table don’t need, so there will be a lot
of empty null fields there. I will keep it separated from each other.

Thanks again!

Best regards,

3

On Thu, Jun 01, 2006 at 10:09:37PM +0200, Eustáquio Rangel wrote:

I have two models (and two tables, of course), let’s call them Room and
Reserved, with similar fields but with the last one with some extra
info, and need to get data from both using a “union all”. Let’s think on

This looks like a job for… <bom BOM!> Single Table Inheritance.

CREATE TABLE rooms (
id INTEGER PRIMARY KEY,
type VARCHAR(255) NOT NULL,
number INTEGER NOT NULL,
date DATE NOT NULL,
occupant VARCHAR(255) NOT NULL,
expiry DATE
);

class RoomOrReservation < ActiveRecord::Base
end

class Room < RoomOrReservation

class Reservation < RoomOrReservation
end

Find just the current room allocations

rooms = Room.find :all

Find just the reservations

reservations = Reservation.find :all

Get everything

all_of_it = RoomOrReservation.find :all

This way, you only need one table, and AR takes care of all of the
messiness
of the whole thing.

Now, if what you need to do is really union two tables (eg legacy
database, or STI just doesn’t do it for you), then you can either just
do
two separate queries and add the two resultsets together:

all_of_it = Room.find(:all) + Reservation.find(:all)

or, if you have a deep and abiding need to effectively “cast” all of the
objects to one class, you can just use find_by_sql to do it:

all_of_it = Room.find_by_sql [“SELECT a, b FROM table1 where x=? UNION
ALL
blah blah blah”, x]

But if you’ve got the option of using STI, I would definitely recommend
it
– it’s just a huge chunk of hotness.

  • Matt