I have table transaction and transactionaction with the following columns. transactionid in transactionaction is a foreign key to transaction.

I need to find every transaction with contains all actions passed by user. It's important to note that some actions can be filtered only based on actionType (actions 2, 3) and some also on value (action 1). So if user wants to find transaction with actions 2,3 he should get transaction 1 and 2. For this case, with help of this answer https://stackoverflow.com/a/41140629/12035106, I created this query.

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(actiontype) @> array[2,3]
)

However, action 1 need to take value into consideration because action 1 with value == null is different than action with value != null. In this case, I cannot really use the previous query. I came up with this query.

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(actiontype) @> array[2,3]
) AND transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    WHERE actiontype = 1 AND value is not null
)

This one work, as a result, I will get only transaction 2, but I feel I overcomplicated it because this query is looping through the same table multiple times. I created an index on actiontype, so the query plan looks better, but maybe there is an easier way to achieve the same result?

CodePudding user response：

I consider that you have an initial design problem, even so I will try to help you with the query.

You can work with a subquery and know if the transaction has an actionType 1 different from null (by means of an aggregation function and an "over" to avoid the "group by"), with this you know you can filter the actionType 2 and 3

I try to simulate your scenario

drop table if exists transaction;
create temp table transaction(
    transactionId   bigint,
    name            varchar
) on commit drop;
insert into transaction(transactionId, name) values(1, 'Trans 1');
insert into transaction(transactionId, name) values(2, 'Trans 2');

drop table if exists transactionAction;
create temp table transactionAction(
    actionId        bigint,
    transactionId   bigint,
    actionType      bigint,
    value           bigint
) on commit drop;
insert into transactionAction(actionId, transactionId, actionType, value) values(1, 1, 1, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(2, 1, 2, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(3, 1, 3, null);

insert into transactionAction(actionId, transactionId, actionType, value) values(4, 2, 1, 1);
insert into transactionAction(actionId, transactionId, actionType, value) values(5, 2, 2, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(6, 2, 3, null);

the query would be

select
    distinct
    t.*
from
    (
        select
            ta.actionid,
            ta.transactionId,
            ta.actionType,
            ta.value,
            /*sum only if action type 1 is different from null*/
            sum(case when ta.actionType = 1 and not ta.value is null then 1 else 0 end) over(partition by ta.transactionId) haveActionOne
        from
            transactionAction ta
    ) a
    inner join transaction t
        on a.transactionId = t.transactionId
where   
        /*indicates if the transaction in its actions has type 1 different from null in its value*/
        a.haveActionOne = 1
    and a.actionType in (2,3);

Regards,

CodePudding user response：

as an option it would be elegantly to move the condition for action (1) in the Where clause thus this approach eliminates any subqueries.

Select Transaction.transactionid, Max(Transaction.name)
From Transaction Inner Join Transactionaction On (Transaction.transactionid=Transactionaction.transactionid)
Where (actiontype=1 And value Is Not Null) Or actiontype<>1 
Group by Transaction.transactionid
Having array_agg(actiontype Order by actiontype) = array[1,2,3]