Fetch data from heterogenous tables(Rails SQL views, SQL Union)

Let’s say you have a ‘cars’ table and a ‘bikes’ table, and the schema can not be changed. Some fields are the same, and you want to display ( paginate, search etc) a list with products that are both cars and bikes (from both the car and the bike models), sorted by their creation date.

This post is about how this can be accomplished using sql views.

You can create an SQL view mapping the common attributes from both tables. Below is the code for a  MySQL view.
class CreateProducts < ActiveRecord::Migration
def self.up
sql = "
CREATE ALGORITHM = MERGE VIEW `products` (
id,
name,
class_name,
seller_type,
seller_id,
total_price,
created_at,
updated_at
) AS (
SELECT cars.id id, cars.name name,
'car' type,
cars.seller_type seller_type, cars.seller_id seller_id,
cars.total_price,
cars.created_at created_at, cars.updated_at updated_at
FROM cars
)
UNION (
SELECT bikes.id id, bikes.name name,
'bike' type,
bikes.seller_type seller_type, bikes.seller_id seller_id,
bikes.unit_price,
bikes.created_at created_at, bikes.updated_at updated_at
FROM bikes
)
ORDER BY updated_at
"
execute sql
end
def self.down
execute "DROP VIEW `products`"
end
end
After this, you can create the
class Product < ActiveRecord::Base
end
model and use it (but only for fetching) just as it would have been a regular model, mapped on a single table. The only (big) exception is that you can not INSERT/UPDATE/DELETE records.
Of course, there are other solutions for this problem of heterogeneous tables, involving creating/merging tables, stored procedures, etc.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: