Finding only active items from a column of ‘added’ / ‘removed’

I’m attempting to write a query that tries to find the most recent items.

Given a set of parent_service_items find the relevant child service items and return a list of them. For finding just the direct children, I’m using query which simply finds all the associated children. I then use this query in a recursive postgres function that goes until no children are left.

My goal is to update this to handle the case of last item being removed. Take this case for example.

epic 1 
 ticket 1
    links (added, removed)
 ticket 2
    links (added)

epic 2
 ticket 1
    links (added)
 ticket 2
    links (added)

If given epic 1 as a parent id, only ticket 2 should be returned. If given epic 1 and epic 2 as parent ids, ticket 1 and ticket 2 should be returned. The above query currently gives both ticket 1 and ticket 2 for epic 1.

As you can imagine with jira, these examples get far more complex. Any ideas on how best to filter this so that we only include a ticket if the most recent action was added. The table includes both an action_key which is a string of ‘added’ or ‘removed’, and a timestamp.

thanks.

Sample Data:

service_items

id	item_type
1	epic
2	epic
3	card
4	card

service_item_links

link_type	parent_service_item	child_service_item	action_date	action_key
‘has_child’	1	3	‘2020-01-01 00:00:00’	‘added’
‘has_child’	1	3	‘2020-01-02 00:00:00’	‘removed’
‘has_child’	1	3	‘2020-01-03 00:00:00’	‘added’
‘has_child’	1	4	‘2020-01-01 00:00:00’	‘added
‘has_child’	2	3	‘2020-01-01 00:00:00’	‘added’
‘has_child’	2	4	‘2020-01-01 00:00:00’	‘added’

Is sample data for the above example.

Given the parent id of 1, the child id of 4 should be returned.

given the parent ids of {1,2}, the child if of 3 and 4 should be returned.

Answer

If a row is removed can’t be added again, here is how you can achieve that :

SELECT DISTINCT
    t.child_service_item, action_date
FROM
    service_item_links t
WHERE
    t.parent_service_item IN (1, 2)
    AND NOT EXISTS
    (
        SELECT
            1
        FROM
            service_item_links t2
        WHERE
            t2.parent_service_item = t.parent_service_item
            AND t2.child_service_item = t.child_service_item
            AND t.action_key = 'removed'
    );

If the row can be added later on again you need to find out last action_type on that :

;WITH cte AS (
     SELECT *, FIRST_VALUE(action_key) OVER (PARTITION BY parent_service_item, child_service_item ORDER BY action_date DESC) last_action_type
     FROM service_item_links
)

    SELECT parent_service_item, child_service_item, MAX(action_date) AS action_date
    FROM cte 
    WHERE last_action_type ='added'
    AND parent_service_item IN (1, 2)
    GROUP BY cte.parent_service_item, child_service_item