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.
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'