How to find the current path in the business process?

Question

Purpose of the application:

I am working on a Saas application for property management. Using BPMN 2.0 artefacts to modeliez business rules.

Pilote BPMN process: Tenant departure management

My pilote is the following process modelization

Purpose of this question To make it easier to understand by the user, user ask to see a timeline rather than a BPMN process.

The timeline is base on a MySQL request that should provide the "current user pathway".

Show me all the tasks I have to do until the end of the process or the next "not yet answered" gateway?

Pathway rules are:

rule 1: From start event (position = 1) show all artefacts until you find a gateway where the result is NULL (the user did not answer yet).

rule 2: When you meet a gateway wherethere is an answer - field result is set - , keep going on the selected branch.

The problem I am facing is about the selected branch (see below).

MySQL schemas

I chose to implement the model through 3 tables:

1. activities hasMany artefacts - this table is not set for this question for we are focused on only one activity.
2. artefacts hasOne parent, hasOne child (to itself) trough artefacts_realtionships
3. artefacts_relationships : join table to describe the 'pathways' of the process. Please note that this cannot be a tree for "leaves may join at the end".

artefacts table

    CREATE TABLE `artefacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activity_id` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `options` json DEFAULT NULL,
  `name` char(255) DEFAULT NULL,
  `description` text,
  `alert_message` text,
  `alert_type` varchar(20) DEFAULT NULL,
  `position` int(3) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `reminder` int(3) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `result` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

artefacts_relationships table

CREATE TABLE `artefacts_relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `child_id` int(11) DEFAULT NULL,
  `choice` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

MySQL implementation for the pilote process

You will find a running MySQL fiddle here

Here is the artefacts_relationships table values artefacts_relationships table values

The 'gateway' artefact functionality

A 'gateway' is a question. Here it is a 'yes/no' question that with either to the 'yes' - choice = 1 - or the 'no' - choice = 2 - branch of the process.

The answer given by the user is store in the field "result" at the row of the gateway artefact.

The orientation switch to branches is given through the field "choice" in the artefacts_relationships_table, at the row where the parent_id is the gateway artefacts.id.

Here I show the case of the artefacts table when the user answer "no" to the question: choice = 2 artefacts table values

My query, so far is:

SELECT DISTINCT
    currents.type AS current_type,
    currents.options AS current_options,
    currents.name AS current_name,
    currents.result AS current_result,
    currents.position AS current_position,
    CASE
        -- Traitement de la gateway
        WHEN currents.type = 'gateway'
            AND currents.result IS NULL
        THEN 'stop1'
        WHEN 
            parents.type = 'gateway'
            AND (
                parents.result != parent_relationships.choice
                OR parents.result IS NULL)
        THEN 'stop2'
        WHEN 
            parents.type != 'gateway'
            OR parents.type IS NULL
        THEN currents.position 

        ELSE NULL
    END AS filtered_position,
FROM artefacts AS currents
-- relation parent_current
LEFT JOIN artefacts_relationships AS parent_relationships
ON currents.id = parent_relationships.child_id
LEFT JOIN (
    SELECT *
    FROM artefacts AS parents
    -- WHERE xxx
    )
    AS parents
ON parent_relationships.parent_id = parents.id

  -- relation current_enfant
LEFT JOIN artefacts_relationships AS child_relationships
ON currents.id = child_relationships.parent_id

LEFT JOIN (
    SELECT *
    FROM artefacts 
        -- WHERE xxx
    )
    AS children
ON child_relationships.child_id = children.id
WHERE 
    currents.activity_id = 1
    AND currents.position <= IFNULL(
                        (SELECT position FROM artefacts
                        WHERE type = 'gateway'
                            AND result IS NULL
                        ORDER BY position
                        LIMIT 1), 9999)
    AND (parents.type != 'gateway'
        OR(parents.type = 'gateway' AND parents.result = parent_relationships.choice)
        )
ORDER BY current_position
;

We should focus only on the 'current' row

**The problem:** 
current_position 7 and 8 from the 'yes' branch (choice = 1) should be filtered (removed) since current_position 6 is not selected.

Thank you for your time and help