I have a table conditions, which defines some conditions. I have another table called rules which defines some rules, and each rule can have many conditions. However, the rule must be able to have grouped conditions in this manner:
(condition1) &&
(condition2) &&
(condition3
|| condition4
||
(condition6 &&
condition7)
) &&
(condition5)
Since this nesting can go as deep as it wants, I can't bind these two tables with a bind table, so I was thinking of actually saving it into the rules table as such: (condition1)&&(condition2)&&(condition3||condition4||(condition6&&condition7))&&(condition5) where "condition1" would be the ID of condition 1, etc.
I would then parse this information in PHP, and somehow create a logical check from the entire string.
Is this an OK approach, or is there a better way to do this kind of nested binding withing MySQL? I absolutely need to keep the ability to group conditions in both AND and OR subgroups on a single rule.
I would certainly steer clear of storing your conditions as a string. You'll have to parse that string to use it and if you want to edit an pre-existing conditions (or search for certain conditions etc) you will probably end up tying yourself in knots.
I would certainly store it in some form of tree structure although @tpaksu's solution has a few flaws. I would suggest something along these lines
ID | Rule_ID | Condition_ID | Parent | Operator |
------------------------------------------------------------
1 | 1 | NULL | NULL | AND |
2 | 1 | 1 | 1 | NULL |
3 | 1 | 2 | 1 | NULL |
4 | 1 | NULL | 1 | OR |
5 | 1 | 3 | 4 | NULL |
6 | 1 | 4 | 4 | NULL |
7 | 1 | NULL | 4 | AND |
8 | 1 | 6 | 7 | NULL |
9 | 1 | 7 | 7 | NULL |
10 | 1 | 5 | 4 | NULL |
I think this represents the original logical structure you wanted to model. The advantage of this is you can have several branches in your tree where there are conditions at the same level and you can trace which branch each condition belongs to - you don't have to rely on the order of the entries in the table. The parent column refers to the table's main ID column.
You basically have two types of row (which suggests to me you may be able to split this into two tables):
I'm not sure you need the rule_id as well. In this example, you don't but if you wanted to store meta-info about each rule (e.g. name/label, valid date range etc) you might want to extract that out into a separate table. It might also make it easier to reconstruct the logic in PHP as MySQL doesn't easily handle recursive queries AFAIAA.
I might have used this structure if I would build something like this,
ID | Rule_ID | Condition_ID | Level | Operator |
------------------------------------------------------------
1 | 1 | 1 | 0 | AND |
2 | 1 | 2 | 1 | OR |
3 | 1 | 1 | 1 | OR |
4 | 1 | 2 | 0 | NULL |
So it would produce:
condition1 && (condition2 || condition1) || condition2
Ok, here's what I succeeded :
<?php
/*(condition1) &&
(condition2) &&
(condition3
|| condition4
||
(condition6 &&
condition7)
) &&
(condition5)
id | RuleID | conditionID | level | operator
-------------------------------------------------
1 1 1 1 AND
2 1 2 1 AND
3 1 3 2 OR
4 1 4 2 OR
5 1 6 3 AND
6 1 7 3 AND
7 1 5 1 NULL
*/
$conditions = array(
array(),
array(condition=>"condition1"),
array(condition=>"condition2"),
array(condition=>"condition3"),
array(condition=>"condition4"),
array(condition=>"condition5"),
array(condition=>"condition6"),
array(condition=>"condition7")
);
$rules = array(
array(RuleID=>1,conditionID=>1,level=>1,operator=>"AND"),
array(RuleID=>1,conditionID=>2,level=>1,operator=>"AND"),
array(RuleID=>1,conditionID=>3,level=>2,operator=>"OR"),
array(RuleID=>1,conditionID=>4,level=>2,operator=>"OR"),
array(RuleID=>1,conditionID=>6,level=>3,operator=>"AND"),
array(RuleID=>1,conditionID=>7,level=>3,operator=>"AND"),
array(RuleID=>1,conditionID=>5,level=>1,operator=>null)
);
echo "<pre>";
//print_r($conditions);
//print_r($rules);
//mocking mysql ruleID filter with array_filter()
$firstrule = array_filter($rules,function($element){return ($element["RuleID"]==1);});
var_dump($firstrule);
$level = 1;
$condition_count = count($firstrule);
foreach($firstrule as $key=>$condition){
$new_level = $condition["level"];
$next_level = $firstrule[$key+1]["level"];
$prev_op = $firstrule[$key-1]["operator"];
if($new_level!=$level){
if($new_level>$level){
echo str_repeat("(",$new_level-$level);
if($condition_count > $key && $next_level < $new_level){
echo $conditions[$condition["conditionID"]]["condition"];
}else{
echo $conditions[$condition["conditionID"]]["condition"].getOperator($condition["operator"]);
}
}
else
{
if($condition_count > $key && $next_level < $new_level){
echo str_repeat(")",$level-$new_level);
echo getOperator($prev_op).$conditions[$condition["conditionID"]]["condition"] ;
}else{
echo str_repeat(")",$level-$new_level);
echo $conditions[$condition["conditionID"]]["condition"] ;
}
}
}else{
if($condition_count > $key && $next_level < $new_level){
echo $conditions[$condition["conditionID"]]["condition"];
}else{
echo $conditions[$condition["conditionID"]]["condition"].getOperator($condition["operator"]);
}
}
$level = $new_level;
}
function getOperator($op){
switch($op){
case "AND": return "&&";
case "OR" : return "||";
case null : return "";
default : return "";
}
}
?>