How to find the current path in the business process?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















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










share|improve this question

























  • You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

    – Joakim Danielson
    Jan 4 at 9:07











  • Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

    – Kitcat711
    Jan 5 at 10:23




















0















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










share|improve this question

























  • You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

    – Joakim Danielson
    Jan 4 at 9:07











  • Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

    – Kitcat711
    Jan 5 at 10:23
















0












0








0








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










share|improve this 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







mysql sql bpmn business-logic business-rules






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 4 at 8:57







Kitcat711

















asked Jan 3 at 15:22









Kitcat711Kitcat711

12




12













  • You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

    – Joakim Danielson
    Jan 4 at 9:07











  • Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

    – Kitcat711
    Jan 5 at 10:23





















  • You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

    – Joakim Danielson
    Jan 4 at 9:07











  • Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

    – Kitcat711
    Jan 5 at 10:23



















You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

– Joakim Danielson
Jan 4 at 9:07





You might want to ready up on MCVE because this is a very extensive question with way too much information. We souldn't have to learn about your whole project to be able to help you with your question.

– Joakim Danielson
Jan 4 at 9:07













Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

– Kitcat711
Jan 5 at 10:23







Thanx for your answer @JoakimDanielson : I do realize that was too much demanding. I think about making the all question differently, and if not, I will remove this question.

– Kitcat711
Jan 5 at 10:23














0






active

oldest

votes












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025168%2fhow-to-find-the-current-path-in-the-business-process%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025168%2fhow-to-find-the-current-path-in-the-business-process%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

MongoDB - Not Authorized To Execute Command

in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith

Npm cannot find a required file even through it is in the searched directory