Matching array of rows with multiple conditions

Question

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

transactionid	name
1	Trans 1
2	Trans 2
3	Trans 3
4	Trans 4

actionid	transactionid	actiontype	value
1	1	1	null
2	1	2	null
3	1	3	null
4	2	1	1
5	2	2	null
6	2	3	null

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?

cristroyer · Accepted Answer · 2021-10-13 19:08:50Z

0

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,

answered Oct 13, 2021 at 19:08

cristroyer

411 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

MarBas Over a year ago

This is looking good. Your answer gives me a good idea (I hope). I store every actiontype in the system in the enum. Some actions have their representation in the database (like action 1) and some actions exist only for filtering (passed from front-end), for example, action 6 corresponds to action 1 with value == null, but would never be stored in the database. I am thinking to use a query like this (query below) This way I can use the first query that I showed, aggregation on filteraction column (having array_agg(filteraction) @> array[1,2,6])

MarBas Over a year ago

select  ta.sessionactionid, ta.value, COALESCE((case when ta.actionType = 1 and ta.value is not null then 5 when ta.actionType = 1 and ta.value is null then 6 else null end), ta.actionType) filteraction from sessionaction ta

Anton Grig · Accepted Answer · 2021-10-13 19:23:33Z

0

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]

edited Oct 13, 2021 at 19:23

answered Oct 13, 2021 at 18:37

Anton Grig

1,71910 silver badges12 bronze badges

2 Comments

MarBas Over a year ago

Thank you for your suggestion, looks like there is small typo, because I think to this query work correctly it should be Having array_agg(actiontype Order by actiontype) @> array[1,2,3] instead of Having array_agg(actiontype Order by actiontype) = array[1,2,3] I will give it a try

Anton Grig Over a year ago

You are welcome. If I've got it right types of actions (2,3) have to exist for a transaction plus the first type with the additional condition, thus the result of array_agg have to contain all three types of actions.

Collectives™ on Stack Overflow

Matching array of rows with multiple conditions

2 Answers 2

I try to simulate your scenario

the query would be

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

I try to simulate your scenario

the query would be

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related