Rails - Jun 2006 - Union all

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!

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

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

Matthew Palmer 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,

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