Skip to content

Instantly share code, notes, and snippets.

@jackross
Forked from joevandyk/gist:4531663
Last active August 29, 2015 14:10
Show Gist options
  • Save jackross/54a9ab5e94ec2a7c69ed to your computer and use it in GitHub Desktop.
Save jackross/54a9ab5e94ec2a7c69ed to your computer and use it in GitHub Desktop.
begin;
/*
Problem: I don't want the application to really care about how the data is
structured in the database. The application / view code needs to work
with the data in a sane format, without worrying about normalization, joins,
eager fetching, etc.
DB Schema Overview:
orders
line_items
shipments (shipments can belong to one or more line_item)
shipment_events
Tables:
orders(order_id)
line_items(line_item_id, order_id)
line_items_shipments(line_item_id, shipment_id)
shipments(shipment_id, tracking_number)
shipment_events(shipment_id, created_at, event_name)
I want to generate this JSON.
{
"id": 1,
"line_items": [{
"line_item_id": 1,
"shipments": [{
"shipment_id": 1,
"tracking_number": 123456,
"events": [{
"created_at": "2013-01-14 10:32:51.590231-08",
"event_name": "picked_up" }, {
"created_at": "2013-01-15 10:32:51.590231-08",
"event_name": "delivered"
}]
}]
},
{ "line_item_id": 2,
"shipments": [
{"shipment_id": 1,
"tracking_number": 123456,
"events": [{
"created_at": "2013-01-14 10:32:51.590231-08",
"event_name": "picked_up" }, {
"created_at": "2013-01-15 10:32:51.590231-08",
"event_name": "delivered"
}]},
{"shipment_id": 2,
"tracking_number": 567890,
"events": [{
"created_at": "2013-01-13 10:32:51.590231-08",
"event_name": "delivered"
}]
}]
}]
}
*/
create schema test;
set search_path = test;
create table orders(order_id integer primary key);
create table line_items(line_item_id integer primary key,
order_id integer references orders not null);
create table shipments(shipment_id integer primary key, tracking_number text);
create table line_items_shipments(
line_item_id integer references line_items not null,
shipment_id integer references shipments not null
);
create table shipment_events (
shipment_id integer references shipments not null,
created_at timestamp with time zone not null default now(),
event_name text not null
);
/* These types are how our application accesses that data. */
create type shipment_event_details as (
created_at timestamp with time zone,
event_name text
);
create type shipment_details as (
shipment_id integer,
tracking_number integer,
events shipment_event_details[]
);
create type line_item_details as (
line_item_id integer,
shipments shipment_details[]
);
create type order_details as (
id integer,
line_items line_item_details[]
);
/* Given a shipment_id, retrieve the shipment_details */
create function shipment_details(shipment_id integer) returns shipment_details as $$
select (
shipment_id,
tracking_number,
array_agg((created_at, event_name)::shipment_event_details)
)::shipment_details
from shipments
left join shipment_events events using (shipment_id)
where shipment_id = $1
group by shipment_id
$$ language sql;
/* Given a line_item_id, retrieve the line_item_details */
create function line_item_details(line_item_id integer) returns line_item_details as $$
select
line_item_id,
array_agg(shipment_details(shipment_id))
from line_items
left join line_items_shipments lis using (line_item_id)
where line_item_id = $1
group by line_item_id;
$$ language sql;
/* Given an order_id, retrieve the order_details */
create function order_details(order_id integer) returns order_details as $$
select
order_id,
array_agg(line_item_details(line_item_id))
from orders
left join line_items using (order_id)
group by order_id
$$ language sql;
/* Populate Data */
insert into orders values (1);
insert into line_items values (1, 1), (2, 1);
insert into shipments values (1, '123456'), (2, '567890');
insert into line_items_shipments values (1, 1), (2, 1), (2, 2);
insert into shipment_events values
(1, now(), 'picked_up'),
(1, now() + interval '1 day', 'delivered'),
(2, now() - interval '1 day', 'delivered')
;
/* Access the json */
select row_to_json(order_details(order_id)) from orders;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment