Cacher is the code snippet organizer for pro developers

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;