在PHP中将布尔表达式解析为MySql查询 [英] Parsing a boolean expression into a MySql query in PHP

问题描述

这是仅有的两个表。

  mysql>。描述skill_usage; 
 + ---------- + --------- + ------ + ----- + --------- +- ------ + 
 |领域类型空|关键默认值|额外| 
 + ---------- + --------- + ------ + ----- + --------- +- ------ + 
 | skill_id | int（11）|否| MUL | NULL | | 
 | job_id | int（11）|否| MUL | NULL | | 
 + ---------- + --------- + ------ + ----- + --------- +- ------ + 
 
 mysql>描述技能名称； 
 + ------------ + ---------- + ------ + ----- + -------- -+ ---------------- + 
 |领域类型空|关键默认值|额外| 
 + ------------ + ---------- + ------ + ----- + -------- -+ ---------------- + 
 | skill_id | int（11）|否| PRI | NULL | auto_increment | 
 | skill_name | char（32）|否| MUL | NULL | | 
 + ------------ + ---------- + ------ + ----- + -------- -+ ---------------- + 
  

基本上，用户输入一个布尔值搜索字符串，使用技能名称。

我将技能名称转换为 skill_id ，然后想要生成一个MySql查询以获取表 skill_usage 中所有匹配的 job_id ，都是通过分析用户的搜索字符串来实现的。

字符串可以包含技能名称，运算符AND和OR，以及优先级括号。

某些示例可能是

• C


• C或C ++


• C ++和UML


• （C AND内核）或（C ++ AND UML）

但是表达的复杂性没有限制-这就是我的问题。

我不是SQL专家，如果我错了，请纠正我。我想我想开始从skill_usage 选择job_id然后解析，并建立其余的查询。

对于第一个例如，只是技能名称C，我想添加 WHERE skillId = X ，其中X是从表 skill_names 中获得的

对于简单的 OR ，例如 C或C ++ ，我可以使用 IN 子句- WhereEdIdId IN（X，Y）（同样，X&Y是对技能名称以获得 skill_id ）。

对于简单的 AND ，像 C ++和UML ，我认为我需要一个 INNER JOIN ，例如 WHERE skill_id = X INNER JOIN skill_usage ON skill_usage.skill_id = Y （其中X是C ++的 skill_id ，UML是Y）。 $ b

对于那些简单的情况（？），我认为这基本上是正确的。

但是，当我遇到甚至更复杂的情况时，例如（ C和内核）或（C ++和UML），我很困惑。

正则表达式或算法在这里合适吗？

@AnthonyVallée-Dubois对这个问题看起来好像我可以修改它，但看起来也很复杂。我希望简化一些事情，但是不确定如何开始（PHP编码不是我的问题，只是正则表达式或算法）。

Update

我试图将解析与查询分开，并使用此问题整理查询。

我得到的答案是

  SELECT job_id 
 FROM skill_usage 
 Skill_id IN（3，4）
 GROUP BY job_id 
 HAVING MIN（skill_id）< MAX（技能ID）; 
  

和

 选择s1.job_id来自skill_usage s1 
的
其中s1.skill_id = 3 
和s1.job_id（
从skill_usage s2 
中选择s2.job_id 
其中s2.skill_id = 4 
）
  

后者看起来更可扩展。

而我的PHP将搜索字符串转换为SQL查询的伪代码大致是

 ，如果方括号不匹配
 
将多个空格减少为单个
在关闭/打开括号（&）之前和之后删除空格。 
 
 foreach c in string 
 
如果c ==（
 
否则
如果c ===）
 
 else 
如果AND AND 
 
 else 
如果OE 
 
 else 
＃这是技能名称
  

解决方案

假定PDO的简单查询生成器

---

  ##对于简单的标记化，此处用空格分隔术语。 
 ## ########################################## ################### 
 $ string =（C AND kernel）OR（C ++ AND UML）; 
 
函数emitt_term（$ tag）{
 $ res ='存在（
 SELECT * 
 FROM skill_usage su 
 JOIN skill_names sn ON sn.skill_id = su.skill_id 
 W.su.Job_id = j.job_id 
和sn.skillname =：。$ tag。）\n; 
返回$ res; 
} 
 
 
 $ fixed_pa​​rt =" 
选择job_id，job_name 
从作业j 
到1 = 1 
并且 n ; 
 
 
＃$ tokens = explode（’，$ string）; ＃在任意单个空格上拆分
 $ tokens = preg_split（// [\s] + /'，$ string）; ＃接受多个空格
＃print_r（$ tokens）; 
 
 $ query = $ fixed_pa​​rt; 
 
 $ args = array（）; 
 $ num = 1; 
 foreach（$ tokens为$ token）{
 switch（$ tok）{
 case''：＃跳过空令牌
 case';'：＃不，您不应该！ 
 case‘’’：
 case‘’’：
 case’；’：中断； 
 case’（’：$ query。=’（’; break; 
 case’）’：$ query。=’）’;打破; 
 case‘&’：
 case‘AND’：$ query。=’AN​​D’;打破; 
 case’|’：
 case‘OR’：$ query。=‘OR’；打破; 
 case’！’：
 case‘NOT’：$ query。=‘NOT’；打破; 
默认值：
 $ tag =‘_q’。 $ num; 
 $ query。= generate_term（$ tag）; 
 $ args [$ tag] = $ tok; 
 $ num + = 1; 
休息时间； 
} 
} 
 $ query。=; \n\n ;; 
 
 echo查询+参数（用于PDO）：\n ; 
 echo $ query; 
 print_r（$ args）; 
 
  

---

输出：

---

 选择job_id，job_name 
从作业j 
中1 = 1 
和
（存在（
选择* 
 FROM skill_usage su 
 JOIN skill_names sn ON sn.skill_id = su.skill_id 
 su.Job_id = j.job_id 
 AND sn.skillname =：_q1） 
 AND EXISTS（
 SELECT * 
 FROM skill_usage su 
 JOIN skill_names sn ON sn.skill_id = su.skill_id 
 W.E.su.Job_id = j.job_id 
 AND sn.skillname =：_q2）
）或（存在（
 SELECT * 
 FROM skill_usage su 
 JOIN Skill_names sn ON sn.skill_id = su.skill_id 
 su.Job_id = j.job_id 
 AND sn.skillname =：_q3）
并且存在（
 SELECT * 
 FROM skill_usage su 
 JOIN skill_names sn ON sn。 skill_id = su.skill_id 
其中su.Job_id = j.job_id 
 AN D sn.skillname =：_q4）
）; 
 
数组
（
 [_q1] => C 
 [_q2] =>内核
 [_q3] => C ++ 
 [_q4] => UML 
）
 
 
  

These are the only two tables that are germane. No need to bother you with the others.

mysql> describe skill_usage;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

mysql> describe skill_names;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| skill_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| skill_name | char(32) | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

Basically, users enter a boolean search string, using skill names.

I will convert the skill snames to skill_id, and then want to generate a MySql query to get all matching job_id from table skill_usage, by parsing the user's search string.

Strings can contain skill name, the operators AND and OR, plus brackets for precedence.

Some examples might be

• C
• C or C++
• C++ AND UML
• (C AND kernel) OR (C++ AND UML)

But there is no limit to the complexity of expression allowed - and that's my problem.

I'm no SQL guru, so correct me if I am wrong. I think that I want to start SELECT job_id FROM skill_usage then parse, and build up the rest of the query.

For the first example, just skill name C, I want to add WHERE skillId = X, where X is gotten from the table skill_names.

For a simple OR, like C OR C++, I can use an IN clause - WHERE skillId IN (X, Y) (again, X & Y are lookups of the skill names to get a skill_id).

For a simple AND, like C++ AND UML, I reckon I need an INNER JOIN, something like WHERE skill_id = X INNER JOIN skill_usage ON skill_usage.skill_id = Y (where X is the skill_id for C++ and Y for UML).

I think that is roughly correct, for those simple cases (?).

But, when I get to even slightly more complex cases like (C AND kernel) OR (C++ AND UML), I get confused.

Would a regex or an algorithm be appropriate here?

@AnthonyVallée-Dubois answer to this question looks like I might be able to modify it, but it also seems very complex. I am hoping to make something simpler, but am unsure how to start (the PHP coding is not my problem, just the regex or algorithm).

Update

I am trying to separate the parsing from the queries, and am using this question to sort out the queries.

I am getting answers like

SELECT job_id
FROM skill_usage
WHERE skill_id IN (3, 4)
GROUP BY job_id
HAVING MIN(skill_id) <> MAX(skill_id);

and

select s1.job_id
  from skill_usage s1
  where s1.skill_id = 3
    and s1.job_id in (
                       select s2.job_id
                         from skill_usage s2
                        where s2.skill_id = 4
                     )

Where the latter looks more extensible.

Whereas my pseudo-code for the PHP to convert a search string to an SQL query is roughly

fail if mis-matched brackets

reduce multiple spaces to single
removes spaces before and after closing/opening bracket  "( " & " )"

foreach c in string

   if c == (
   
   else
      if c === )
      
      else
         if AND
         
         else
           if OE
           
           else
              # it's a skill name

解决方案

Simple query generator, assuming PDO

---

        ## for simple tokenisation, the terms are separated by space here.
        ## ###############################################################
$string = "( C AND kernel ) OR ( C++ AND UML )";

function emit_term( $tag ) {
$res = " EXISTS (
                SELECT *
                FROM skill_usage su
                JOIN skill_names sn ON sn.skill_id = su.skill_id
                WHERE su.Job_id = j.job_id
                AND sn.skillname = :" . $tag . ")\n";
return $res;
}


$fixed_part ="
SELECT job_id, job_name
 FROM jobs j
 WHERE 1=1
 AND \n" ;


# $tokens = explode( ' ' , $string ); #splits on any single space
$tokens = preg_split( '/[\s]+/' , $string ); # accepts multiple whitespace
# print_r ( $tokens );

$query = $fixed_part;

$args = array();
$num = 1;
foreach ( $tokens as $tok ) {
        switch ($tok) {
        case '':  # skip empty tokens
        case ';':  # No, you should not!
        case '"':
        case "'":
        case ';':  break;
        case '(': $query .= '('; break;
        case ')': $query .= ')'; break;
        case '&':
        case 'AND': $query .= ' AND '; break;
        case '|':
        case 'OR': $query .= ' OR '; break;
        case '!':
        case 'NOT': $query .= ' NOT '; break;
        default:
                $tag = '_q' . $num ;
                $query .= emit_term ( $tag );
                $args[$tag] = $tok;
                $num += 1;
                 break;
                }
        }
$query .= ";\n\n";

echo "Query + parameters (for PDO):\n" ;
echo $query;
print_r ( $args) ;
          

---

Output:

---

SELECT job_id, job_name
 FROM jobs j
 WHERE 1=1
 AND 
( EXISTS (
        SELECT *
        FROM skill_usage su
        JOIN skill_names sn ON sn.skill_id = su.skill_id
        WHERE su.Job_id = j.job_id
        AND sn.skillname = :_q1)
 AND  EXISTS (
        SELECT *
        FROM skill_usage su
        JOIN skill_names sn ON sn.skill_id = su.skill_id
        WHERE su.Job_id = j.job_id
        AND sn.skillname = :_q2)
) OR ( EXISTS (
        SELECT *
        FROM skill_usage su
        JOIN skill_names sn ON sn.skill_id = su.skill_id
        WHERE su.Job_id = j.job_id
        AND sn.skillname = :_q3)
 AND  EXISTS (
        SELECT *
        FROM skill_usage su
        JOIN skill_names sn ON sn.skill_id = su.skill_id
        WHERE su.Job_id = j.job_id
        AND sn.skillname = :_q4)
);

Array
(
    [_q1] => C
    [_q2] => kernel
    [_q3] => C++
    [_q4] => UML
)

                     

这篇关于在PHP中将布尔表达式解析为MySql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！