Context
I am running a variant of the vehicle routing problem for a site. The layout is this. You have pick up points, trucks and items being picked up. The real trick is that a customers don't run the same number of shifts in a day, so that is customizable. So I will know this at runtime but not in advance. I am trying to create query that returns a result with rows for each bus stop, columns for each shift and json arrays of the ids of each item, like this:
id | address | city | postalcode | shift1 | shift2 | shift3
-----+------------------+-----------+------------+----------+--------+--------
220 | 1471 N Lymestone | Anytown | 12345 | [14, 16] | [12] | [14]
221 | 108 D Ave | Anytown | 12345 | [15, 17] | | [15,16]
222 | 1434 Bryan Ave | Anytown | 12345 | [16] | [1,19] |
Table Structure
I have three relevant tables here; the stops table that just has geographic locations of stops, the items table that has ids of pickup items, the shifts table that has the shifts and the itemstopassignemnt table (please forgive the naming, I inherited it) that has an stop_id shift_id and item_id assigning an item to be picked up at that stop for that shift.
Query
So after fishing around on SO, I came up the following query:
SELECT * FROM crosstab(
$$SELECT stop_id,
'shift' || shift_id,
json_agg(item_id)
FROM itemstopassignemnt
GROUP BY stop_id, shift_id
ORDER BY 1,2$$
) AS (stop_id INTEGER, shift1 JSON, shift2 JSON, shift3 JSON);
This works for the three shift case. And I can put this in function generate the sql programmatically so it constructs it dynamically for however many shifts the user has added. The function then would have to return setof record or table(), I think. Here are my questions:
select
stop_id,
jsonb_object_agg('shift' || shift_id, items) as shift_items
from (
select
stop_id,
shift_id,
coalesce (
array_agg (item_id) filter (where item_id is not null),
array[]::int[]
) as items
from
itemstopassignemnt
right join
(
select shift_id, stop_id
from
(select distinct shift_id from itemStopAssignemnt) a
cross join
(select distinct stop_id from itemStopAssignemnt) b
) r using (stop_id, shift_id)
group by 1,2
) s
group by 1
;
stop_id | shift_items
---------+--------------------------------------------------------
220 | {"shift1": [14, 16], "shift2": [12], "shift3": [14]}
221 | {"shift1": [15, 17], "shift2": [], "shift3": [15, 16]}
222 | {"shift1": [16], "shift2": [1, 9], "shift3": []}
I did not join it to stops to shorten it, but should be trivial.