0

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'
  • 2
    The same as on your previous post on Stack Overflow applies: You should illustrate your question by editing it and adding the relevant schema as CREATE TABLE statements, sample data as INSERT INTO statements and the desired result the sample data should give. Just in case, knowing the version of Postgres this is for, won't harm either. – sticky bit 2 days ago
  • @stickybit added :) – Reeebuuk 2 days ago
  • How is the query you posted related to the user "reee"? Do you mean the user "jaila"? – Lennart 2 days ago
  • @Lennart fixed :) using reee now. – Reeebuuk 2 days ago
  • 3
    Unrelated, but: you should really avoid names that require double-quotes to use them. Those will give you much more problems in the long run than they are worth it. – a_horse_with_no_name 2 days ago
0

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.

  • Excellent, seems to be working as expected. CROSS JOIN LATERAL, nice one :) – Reeebuuk 2 days ago

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.