Forum: Ruby on Rails Union all

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Eustáquio R. (Guest)
on 2006-06-02 00:09
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!
Matthew P. (Guest)
on 2006-06-02 03:24
(Received via mailing list)
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
Eustáquio R. (Guest)
on 2006-06-02 17:25
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,
This topic is locked and can not be replied to.