PostgreSQL – Loop-Like SQL Join Techniques

postgresql

Would like to build a materialised view in postgres but have only one issue.

I have a query which works if I specify a user directly. So this query would return me the results for user == 'reee'

SELECT 
   productAggregate.outstandingAmount,
   productAggregate.client AS client
FROM 
 (
 SELECT 
     SUM(CASE
            WHEN orders.order_type = 'Client Sell' 
            THEN orders.size_traded * '-1'::integer::numeric
            ELSE orders.size_traded
         END) AS outstandingAmount,
     CASE
        WHEN count(DISTINCT orders.client_id) > 1 THEN 'Mixed'
        ELSE COALESCE(MAX(orders.client_id), '')
     END AS client,
     orders.product_id AS productId
 FROM  "my-product_orders" orders 
 WHERE 
      orders.sales_credit_recipient_id in (SELECT uc."child_user_id" 
                                       FROM   "my-product_user_child" as uc 
                                       WHERE  uc."user_id" = 'reee') 
      OR orders.sales_credit_recipient_id = 'reee'
 GROUP BY orders.product_id
 ) productAggregate,
 "my-product_products" products
WHERE productAggregate.productId = products.id;

And I have another query which gives me a list of distinct userIds.

So I would like to execute the first query for each user (first query will return multiple rows so cannot use the double select. Need it all in one query for the view.

For normal join I would assume I need the userId column in first query but don't have it just because I would need to groupBy userId as well which is not what I want.

Example

CREATE TABLE public."my-product_orders" (
    order_id character varying NOT NULL,
    product_id character varying NOT NULL,
    sales_credit_recipient_id character varying,
    order_type character varying NOT NULL,
    size_traded numeric(21,2) NOT NULL,
    client_id character varying,
);

CREATE TABLE public."my-product_products" (
    id character varying NOT NULL,
    isin character varying
);

CREATE TABLE public."my-product_user_child" (
    user_id character varying NOT NULL,
    child_user_id character varying NOT NULL
);


INSERT INTO public."my-product_products" VALUES ('PRODUCT1', 'ISIN1');
INSERT INTO public."my-product_products" VALUES ('PRODUCT2', 'ISIN2');

INSERT INTO public."my-product_orders" VALUES ('ORDER1', 'PRODUCT1', 'USER1', 'Client Sell', 100.00, '781');
INSERT INTO public."my-product_orders" VALUES ('ORDER2', 'PRODUCT1', 'USER2', 'Client Buy', 150.00, '386');
INSERT INTO public."my-product_orders" VALUES ('ORDER3', 'PRODUCT2', 'USER1', 'Client Buy', 50.00, '268');

INSERT INTO public."my-product_user_child" VALUES ('USER1', 'USER2');

Result of a view:

ISIN, USER, OUTSATNDING, CLIENT
'ISIN1', 'USER1', '50.00', 'Mixed'
'ISIN1', 'USER2', '150.00', '386'
'ISIN2', 'USER1', '50.00', '286'

Best Answer

I'm not sure I understand what problem you are trying to solve, but I'll guess something like:

SELECT users.user_id
     , SUM(CASE WHEN orders.order_type = 'Client Sell' 
                THEN orders.size_traded*'-1'::integer::numeric
                ELSE orders.size_traded
           END) AS outstandingAmount
     , CASE WHEN count(DISTINCT orders.client_id) > 1 
            THEN 'Mixed'
            ELSE COALESCE(MAX(orders.client_id), '')
       END AS client
     , orders.product_id AS productId
FROM ( [your select of distinct users] ) as users (user_id)
CROSS JOIN LATERAL (
    SELECT child_user_id 
    FROM my-product_user_child uc
    WHERE uc.user_id = users.user_id 
    UNION ALL
    SELECT users.user_id
) T (user_id)
JOIN my-product_orders AS orders
    ON orders.sales_credit_recipient_id = T.user_id
GROUP BY users.user_id, orders.product_id

will give you the outstanding amount per user in your select. I'm not sure what the purpose of the outer select is, so I'll leave that.

Related Question