Блог

У меня есть таблица, которая описывает список продуктов, изготовленных для данного заказа, в которой каждая строка в этой таблице должна содержать идентификатор продукта и причину, по которой он был приобретен,

Я хотел бы создать ответ в формате json, который представляет собой массив местоположений, для которых предназначен заказанный продукт, внутри этого массива уникальных кодов продукта и с этим массивом причин, почему этот уникальный продукт был заказан.

Мне удалось определить только самую верхнюю часть моего запроса, но характер соединения и подвыбора запроса на самом деле ставит меня в затруднительное положение. Действительно ли такое возможно в plpgsql?

Кроме того, я хотел бы присоединиться к product_order.reason на product_order_reason.id и извлекаем longform_text внутри таблицы, связанной с этой строкой, но я полагаю, что важнее вообще получить возврат, и вот тут я оказался в тупике.

 product
id      |name                    |cost  |cost_rate|
--------|------------------------|------|---------|
WALLC   |Wall Clock              | 15.00|SINGLE   |
MIRR    |Mirror                  | 25.00|SINGLE   |
KEY     |Door Keys               |  5.00|SINGLE   |
KEYFOB  |Key Fob                 | 40.00|SINGLE   |



product_order
product_id|quantity|location  |quote_detail_quote_id               |is_primary_order|reason|
----------|--------|----------|------------------------------------|----------------|------|
MIRR      |       2|floor_0   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     1|
KEYF      |       3|floor_0   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     2|
WALLC     |       3|floor_1   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     1|
WALLC     |       3|floor_1   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     3|
product_order_reason
------------------------------------------------
id (varchar, pk) | shortform_text(varchar) | longform_text(varchar)
------------------------------------------------


id|shortform_text                       |longform_text                                        |
--|-------------------------------------|-----------------------------------------------------|
 1|Employee Room                        |Standard employee room with no window                |
 2|Meeting Room                         |Standard Meeting Room                            |
 3|Mirror                               |Additional Mirror Request         |


  

 create
or replace
function get_breakdown_v1_0_0(p_quote_id character varying,
p_location character varying,
p_product_code character varying) returns json language plpgsql as $function$ declare row_count smallint := 0;
begin
raise notice 'Location: %',
p_location;

raise notice 'Product: %',
p_product_code;
-- Perform santiy check on quote_id so that the json does not include a null result.
 select
    count(*) into
        strict row_count
    from
        quote_detail
    where
        quote_id = p_quote_id;

if row_count = 0 then raise 'Quote ID % not found',
p_quote_id
    using ERRCODE = '02000';
-- SQL standard no_data
 elseif row_count > 1 then raise 'Too many rows returned for ID %',
p_quote_id
    using ERRCODE = 'P0003';
-- PL/pgSQL too_many_rows
end if;
-- Returns an object comprised of unique values for locations, where not null and their associated products
 return (
select
    jsonb_build_object ('locations',jsonb_agg( jsonb_build_object( 'area', location, 'items', items)))
from
    (
    select
        location,
        jsonb_agg(jsonb_build_object ('code', product_id, 'reasons', reason)) as items 
    from
        product_order
    where
        (quote_detail_quote_id = p_quote_id)
        and (location = p_location
        or p_location is null)
        and (product_id = p_product_code
        or p_product_code is null)
    group by
        location) a );
end $function$ ;
  

Желаемый ответ;

         {
            "area": "floor_0",
            "items": [
                {
                    "code": "WALLC",
                    "reasons": [
                        {
                            "quantity": 2,
                            "reason_code": "Standard Employee Room"
                        },
                        {
                            "quantity": 2,
                            "reason_code": "Standard Cubicle"
                        }
                    ]
                },
                {
                    "code": "MIRR",
                    "reasons": [
                        {
                            "quantity": 3,
                            "reason_code": "Meeting Room"
                        }
                    ]
                }
            ]
        }]
  

Хорошо, я думаю, у меня есть кое-что для вас. Идея состоит в том, чтобы создавать по одному из массивов за раз и передавать необходимую оставшуюся информацию во внешние запросы для дальнейшего построения массива. Вы можете добавить свои ограничения для quote_detail_quote_id , location и product_id к WHERE предложению самого внутреннего запроса.

SQLFiddle, чтобы показать это в действии.

Это может потребовать некоторого изучения:

 SELECT json_build_object('area', t3.location, 'items', t3.code_json)
FROM
(
    SELECT t2.location
           , array_to_json(array_agg(jsonb_build_object('code', t2.product_id, 'reasons', t2.qty_reason_json))) AS code_json
    FROM
    (
      SELECT t.location
             , t.product_id
             , array_to_json(array_agg(jsonb_build_object('quantity', t.quantity, 'reason_code', t.longform_text))) AS qty_reason_json
      FROM
      (
          SELECT po.product_id
                 , po.quantity
                 , po.location
                 , po.reason
                 , por.longform_text
          FROM product_order po
          JOIN product_order_reason por ON (por.id = po.reason) 
          WHERE quote_detail_quote_id = 'C7D33FED'
      ) t
      GROUP BY t.location, t.product_id
    ) t2
    GROUP BY t2.location
) t3
;