fork(1) download 
  1. <?php
  2.  
  3. $conditions = 'C++ AND ((UML OR Python) OR (not Perl))';
  4.  
  5. // Other tests...
  6. //$conditions = "C++ AND Python OR Perl";
  7. //$conditions = "C++ AND Python OR Perl OR (Perl AND (Ruby AND Docker AND (Lisp OR (C++ AND Ada) AND Java)))";
  8.  
  9. ///////// CONFIGURATION /////////
  10. $maxNest = 0; // Set to 0 for unlimited nest levels
  11. /////////////////////////////////
  12.  
  13. print "Original Input:\n";
  14. print $conditions . "\n\n";
  15.  
  16. // Add implicit parenthesis...
  17. // For example: `A AND B OR C` should be: `(A AND B) OR C`
  18. $addParenthesis = '/(?|(((?:\bNOT\b\s*+)?+[^)(\s]++|(?:\bNOT\b\s*+)?+[(](?:\s*+(?2)\s*+)*+[)])(?:\s*+\bAND\b\s*+((?2)))++)(?=\s*+\bOR\b\s*+)|\s*+\bOR\b\s*+\K((?1)))/im';
  19. while (preg_match($addParenthesis, $conditions)) {
  20.   $conditions = preg_replace($addParenthesis, '(\1)', $conditions);
  21. }
  22.  
  23. print "Input after adding implicit parenthesis (if needed):\n";
  24. print $conditions . "\n\n";
  25.  
  26. // Optional cleanup: Remove useless NOT () parenthesis
  27. $conditions = preg_replace('/[(]\s*((?:NOT\s*)?+(\S+))\s*[)]/i', '\1', $conditions);
  28.  
  29. // Optional cleanup: Remove useless NOT NOT...
  30. $conditions = preg_replace('/\bNOT\s+NOT\b/i', '', $conditions);
  31.  
  32. $list_conditions = [$conditions];
  33.  
  34. function split_conditions($input, $level = 0) {
  35.   global $list_conditions, $maxNest;
  36.  
  37.   if ($maxNest > 0 && $level >= $maxNest) { return; }
  38.  
  39.   // If it is a logic operator, skip
  40.   if ( preg_match('/^\s*(?:AND|OR)\s*$/i', $input) ) {
  41.     return;
  42.   }
  43.  
  44.   // Add condition to the list:
  45.   array_push($list_conditions, $input);
  46.  
  47.   // Don't go on if this is a single filter
  48.   if ( preg_match('/^\s*(?:NOT\s+)?+[^)(\s]+\s*$/i', $input) ) {
  49.     return;
  50.   }
  51.  
  52.   // Remove parenthesis (if exists) before evaluating sub expressions
  53.   // Do this only for level > 0. Level 0 is not guaranteed to have
  54.   // sorrounding parenthesis, so It may remove wanted parenthesis 
  55.   // such in this expression: `(Cond1 AND Cond2) OR (Cond3 AND Cond4)`
  56.   if ($level > 0) {
  57.     $input = preg_replace('/^\s*(?:NOT\b\s*)?+[(](.*)[)]\s*$/i', '\1', $input);
  58.   }
  59.  
  60.   // Fetch all sub-conditions at current level:
  61.   $next_conds = '/((?:\bNOT\b\s*+)?+[^)(\s]++|(?:\bNOT\b\s*+)?+[(](?:\s*+(?1)\s*+)*+[)])/i';
  62.   preg_match_all($next_conds, $input, $matches);
  63.  
  64.   // Evaluate subexpressions
  65.   foreach ($matches[0] as $match) {
  66.     split_conditions($match, $level + 1);
  67.   }
  68. }
  69.  
  70. split_conditions($conditions);
  71.  
  72. // Trim and remove duplicates
  73. $list_conditions = array_unique(array_map(function($x){
  74.   return preg_replace('/^\s*|\s*$/', '', $x);
  75. }, $list_conditions));
  76.  
  77. // Add columns
  78. $list_conditions = array_map(function($x){
  79.   return preg_replace('/([^\s()]++)(?<!\bAND\b)(?<!\bOR\b)(?<!\bNOT\b)/i', "skill='$1'", $x);
  80. }, $list_conditions);
  81.  
  82. print "Just the conditions...\n\n";
  83. print_r($list_conditions);
  84. print "\n\n";
  85.  
  86. print "Method 1) Single query with multiple SUM\n\n";
  87. $sum_conditions = implode(",\n", array_map(function($x){
  88.   return "    SUM( $x )";
  89. }, $list_conditions));
  90. $sumSQL = "SELECT\n$sum_conditions\nFROM candidates;";
  91. print $sumSQL . "\n\n";
  92.  
  93. print "Method 2) Multiple queries\n\n";
  94. $queries = implode("\n", array_map(function($x){
  95.   return "SELECT count(*) from candidates WHERE $x;";
  96. }, $list_conditions));
  97. print $queries . "\n\n";
  98.  
Success #stdin #stdout 0.01s 82624KB
comments (?)
stdin 
Standard input is empty
stdout 
Original Input:
C++ AND ((UML OR Python) OR (not Perl))

Input after adding implicit parenthesis (if needed):
C++ AND ((UML OR Python) OR (not Perl))

Just the conditions...

Array
(
    [0] => skill='C++' AND ((skill='UML' OR skill='Python') OR not skill='Perl')
    [2] => skill='C++'
    [3] => ((skill='UML' OR skill='Python') OR not skill='Perl')
    [4] => (skill='UML' OR skill='Python')
    [5] => skill='UML'
    [6] => skill='Python'
    [7] => not skill='Perl'
)


Method 1) Single query with multiple SUM

SELECT
    SUM( skill='C++' AND ((skill='UML' OR skill='Python') OR not skill='Perl') ),
    SUM( skill='C++' ),
    SUM( ((skill='UML' OR skill='Python') OR not skill='Perl') ),
    SUM( (skill='UML' OR skill='Python') ),
    SUM( skill='UML' ),
    SUM( skill='Python' ),
    SUM( not skill='Perl' )
FROM candidates;

Method 2) Multiple queries

SELECT count(*) from candidates WHERE skill='C++' AND ((skill='UML' OR skill='Python') OR not skill='Perl');
SELECT count(*) from candidates WHERE skill='C++';
SELECT count(*) from candidates WHERE ((skill='UML' OR skill='Python') OR not skill='Perl');
SELECT count(*) from candidates WHERE (skill='UML' OR skill='Python');
SELECT count(*) from candidates WHERE skill='UML';
SELECT count(*) from candidates WHERE skill='Python';
SELECT count(*) from candidates WHERE not skill='Perl';
https://ideone.com/mIMXPY
language:
PHP (php 7.3.5)
created:
6 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!