PHP递归函数实现SQL条件字符串拼接

$conditions = [
  ["client_code","contains","12"],
  "and",
  [
    ["trade_name","=","KeyWholesaler"],
    "or",
    ["trade_name","=","Cash&Carry"]
  ],
  "and",
  [
    "!",
    ["state","=","B-BigCantina"],
    ["state","=","B-BigCantina2"]
  ],
  "and",
  ["client_name","contains","M"]
];

function buildQueryEcho($array) {
    // 假设 $_SESSION["NOT"] 用于管理 "!" 状态
    static $is_not_active = false; // 使用静态变量模拟会话状态

    if (is_array($array) && count($array) == count($array, COUNT_RECURSIVE)) {
        // 基本条件数组，例如 ["client_code","contains","12"]
        $operator = $array[1];
        $value = $array[2];
        $field = $array[0];

        $not_prefix = $is_not_active ? "!" : "";
        $and_suffix = $is_not_active ? " AND" : ""; // 这里的 AND 逻辑在实际中可能需要更精细处理

        echo "`$field` $not_prefix$operator '$value' $and_suffix";
        $is_not_active = false; // 重置 NOT 状态
    } else if (is_array($array)) {
        // 嵌套数组，表示一个逻辑分组
        echo "(";
        foreach ($array as $value) {
            buildQueryEcho($value);
        }
        echo ")";
        $is_not_active = false; // 重置 NOT 状态
    } else if ($array == "!") {
        // 遇到 "!" 运算符
        $is_not_active = true;
    } else {
        // 逻辑连接符，如 "and", "or"
        echo " " . strtoupper($array) . " ";
    }
}

// 调用示例
// buildQueryEcho($conditions); // 会直接输出到浏览器或控制台

function buildQueryString($array) {
    static $not_state = ""; // 使用静态变量模拟 $_SESSION["NOT"]

    // 检查是否为基本条件数组（叶子节点）
    // count($array) == count($array, COUNT_RECURSIVE) 用于判断数组是否为一维数组
    if (is_array($array) && count($array) == count($array, COUNT_RECURSIVE)) {
        $field = $array[0];
        $operator = $array[1];
        $value = $array[2];

        $is_not = $not_state; // 获取当前的 NOT 状态
        $and_if_not = $not_state ? " AND" : ""; // 如果是 NOT 状态，则后续连接用 AND

        // 针对 "!" 运算符，将操作符转换为反向，例如 "=" 变为 "!="
        if ($is_not && $operator == "=") {
            $operator = "!=";
        } elseif ($is_not && $operator == "!=") {
            $operator = "="; // 再次取反
        }
        // 其他操作符（如 contains）可能需要更复杂的转换或直接使用 NOT 关键字

        $not_state = ""; // 使用后重置 NOT 状态
        return "`$field` $operator '$value'$and_if_not"; // 返回格式化的SQL片段
    } else if (is_array($array)) {
        // 嵌套数组（内部节点），表示一个逻辑分组
        $result = "";
        foreach ($array as $key => $value) {
            $result .= buildQueryString($value); // 递归调用并拼接结果
        }
        $not_state = ""; // 确保在括号结束后重置 NOT 状态
        return "(" . $result . ")"; // 将整个分组用括号括起来并返回
    } else if ($array == "!") {
        // 遇到 "!" 运算符，激活 NOT 状态
        $not_state = "!";
        return ""; // "!" 本身不生成SQL片段，只改变状态
    } else {
        // 逻辑连接符，如 "and", "or"
        return " " . strtoupper($array) . " "; // 返回连接符
    }
}

$conditions = [
  ["client_code","contains","12"],
  "and",
  [
    ["trade_name","=","KeyWholesaler"],
    "or",
    ["trade_name","=","Cash&Carry"]
  ],
  "and",
  [
    "!", // NOT 运算符
    ["state","=","B-BigCantina"],
    "and", // 这里的 AND 会被 NOT 作用
    ["state","=","B-BigCantina2"]
  ],
  "and",
  ["client_name","contains","M"]
];

// 调用函数生成SQL字符串
$sqlWhereClause = buildQueryString($conditions);

echo "生成的SQL WHERE子句：\n";
echo $sqlWhereClause;
/* 预期输出 (可能需要根据实际需求调整操作符和空格)：
( `client_code` contains '12' AND ( `trade_name` = 'KeyWholesaler' OR `trade_name` = 'Cash&Carry' ) AND ( `state` != 'B-BigCantina' AND `state` != 'B-BigCantina2' ) AND `client_name` contains 'M' )
*/