多年后再写sql查询条件拼接

先快速能用再说…

<?php

namespace App\Service;

class QueryToolService
{

    /**
     * sql空格分割
     */
    const SQL_SPACE = ' ';

    /**
     * 括号-左
     */
    const SQL_BRACKETS_LEFT = '(';

    /**
     * 括号-右
     */
    const SQL_BRACKETS_RIGHT = ')';

    /**
     * 双引号
     */
    const SQL_BRACKETS_QUOTATIONS = '"';

    /**
     * getInstance
     * @return QueryToolService
     */
    public static function getInstance()
    {
        return new self();
    }

    /**
     * sql where 查询条件拼装
     * 暂时支持：'=', '<', '<=', '>', '>=', '<>', 'like', 'not like', 'in', 'not in', 'between', 'not between'
     * where为空查询全部， 结构为二维数组
     * @param $where =>  [
     * ['id', '=', 2],
     * ['name', '=', '张三'],
     * ['remark', 'like', '%张三%'],
     * ['id', 'in', [1,2,3]],
     * ['age', 'between', [1,'3']],
     * ];
     * @param string $logic
     * @param bool $asOneCondition | 是否当成一个条件拼接，or拼and的时候
     * @return string|void
     */
    public function genConditionSql($where, string $logic = 'AND', $asOneCondition = false)
    {
        if (empty($where)) {
            return $this->getAllTrueSql();
        }

        $arr = [];
        foreach ($where as $key => $condition) {
            if (empty($condition)) {
                continue;
            }
            list($field, $operator, $value) = $condition;
            $operatorStr = self::SQL_SPACE . $operator . self::SQL_SPACE;
            if (in_array($operator, ['=', '<', '<=', '>', '>=', '<>', 'like', 'not like'], true)) {
                $value = $this->tranVal($value);
                $arr[] = self::SQL_BRACKETS_LEFT . $field . $operatorStr . $value . self::SQL_BRACKETS_RIGHT;
            } elseif (in_array($operator, ['in', 'not in'], true)) {
                if (empty($value)) {
                    if ($operator == 'in') {
                        $arr[] = self::SQL_BRACKETS_LEFT . $field . $operatorStr . '(NULL)' . self::SQL_BRACKETS_RIGHT;
                    } else {
                        $arr[] = $this->getAllTrueSql();
                    }
                } else {
                    $arr[] = self::SQL_BRACKETS_LEFT . $field . $operatorStr . self::SQL_BRACKETS_LEFT . $this->implodeSql(',', $value) . self::SQL_BRACKETS_RIGHT . self::SQL_BRACKETS_RIGHT;
                }
            } elseif (in_array($operator, ['between', 'not between'], true)) {
                list($start, $end) = $value;
                $start = $this->tranVal($start);
                $end = $this->tranVal($end);
                $arr[] = self::SQL_BRACKETS_LEFT . $field . $operatorStr . $start . self::SQL_SPACE . 'AND' . self::SQL_SPACE . $end . self::SQL_BRACKETS_RIGHT;
            }
        }
        $conditionSql = implode(self::SQL_SPACE . $logic . self::SQL_SPACE, $arr);
        return $asOneCondition ? self::SQL_BRACKETS_LEFT . $conditionSql . self::SQL_BRACKETS_RIGHT : $conditionSql;
    }

    /**
     * sql的implode
     * @param $separator
     * @param $arr
     * @return string
     */
    public function implodeSql($separator, $arr): string
    {
        $newArr = [];
        foreach ($arr as $k => $v) {
            $newArr[] = $this->tranVal($v);
        }
        return $newArr ? implode($separator, $newArr) : 'NULL';
    }

    /**
     * 数据类型转化
     * @param $v
     * @return string
     */
    public function tranVal($v)
    {
        return is_string($v) ? (self::SQL_BRACKETS_QUOTATIONS . $v . self::SQL_BRACKETS_QUOTATIONS) : $v;
    }

    /**
     * 快速生成 id condition
     * 空id则表示一条数据都没有
     * @param $ids
     * @return string
     */
    public function genIdSql($ids)
    {
        if (is_array($ids)) {
            if (empty($ids)) {
                return $this->getAllFalseSql();
            }
            return self::SQL_BRACKETS_LEFT . 'id in' . self::SQL_SPACE . self::SQL_BRACKETS_LEFT . $this->implodeSql(',', $ids) . self::SQL_BRACKETS_RIGHT . self::SQL_BRACKETS_RIGHT;
        } else {
            return self::SQL_BRACKETS_LEFT . 'id =' . self::SQL_SPACE . $this->tranVal($ids) . self::SQL_BRACKETS_RIGHT;
        }
    }

    /**
     * sql：1=1
     * @return string
     */
    public function getAllTrueSql(): string
    {
        return self::SQL_BRACKETS_LEFT . '1=1' . self::SQL_BRACKETS_RIGHT;
    }

    /**
     * sql：1=2
     * @return string
     */
    public function getAllFalseSql(): string
    {
        return self::SQL_BRACKETS_LEFT . '1=2' . self::SQL_BRACKETS_RIGHT;
    }

}

本作品采用《CC 协议》，转载必须注明作者和本文链接