上一篇blog只是一个思路，代码封装实现还是个大问题。
分析
传参格式，把界面的按钮输入框，一个一个解析对应的数据库字段，实现搜索条件的扁平化
"search":{
{button_1},
{button_2}
{button_3}
}
安照前端界面的方式组装搜索条件，button_1和button_2互不干扰，无论是单表还是跨表，统一的格式组装，
最后的代码会比普通写法更多，但是因为两个条件的关联分解开了，写代码时候不需要考虑两个条件（多表多条件）的关系，所以更好阅读、方便队友扩展
使用
把前端参数组装
$search = [
'select'=>[
'user.user_id'
],
'search' => [
[
'table' => 'order',
'join' => [
'type' => 'LEFT JOIN',
'key' => 'user_id',
],
'where' => [
['tid', '=', 10000121221],
'OR_123'=>[
['tid', '=', 10000121221],
'OR_333'=>['tid', '=', 10000121221]
],
['tid', '=', 10000121221],
],
],
[
'table' => 'user',
'join' => [
'foreign'=>'user.user_id',
'type' => 'LEFT JOIN',
'key' => 'user_id',
],
'orWhere' => [
['user_id', '=', 10000121221],
],
],
],
]
实现
<?php
/**
* Created by PhpStorm.
* User: 明月有色
* Date: 2018/1/9
* Time: 14:07
*/
namespace Test;
/**
* Class SearchMysqlService
*/
class SearchMysqlService
{
/**
* 表关系声明
*
* @var array {
* @var array $foreign 外键声明
* }
*/
private $table = [
'user' => [
'foreign' => [
'user_id' => 'order.user_id',
],
],
'order' => [
'foreign' => [
'user_id' => 'user.user_id',
]
],
];
private static $paramKey = 0;
/**
* @param $search
* @author baichou <baichou@qingmutec.com>
* @return array
*/
public function build($search)
{
return $this->buildSql($search);
}
/**
* 构造sql
*
* @param $search
* @return array
* @author baichou <baichou@qingmutec.com>
*/
private function buildSql($search)
{
// 查询的表
$first = $this->getAllJson($search);
$table = $first->getData()['table'];
$have_select = isset($search['select']);
if (!isset($search['select'])) {
/**
* A，当没有设置select时候
* B，先生成一次sql_test
* C, 获取where后面使用过的字段
* D，检查testSql是否真实使用过$table
*/
$testSearch = $search;
$testSearch['select'] = [$table . '.*'];
$testSql = $this->buildSql($testSearch)['sql'];
$checkSql = strstr($testSql, 'WHERE ');
$search['select'] = [];
if( $checkSql ){
preg_match_all('/[^\(\)\s]+(\.)[\S]+[^\(\s\)]+/',$checkSql,$field);
if( isset($field[0]) && $field[0] ){
// 抽取WHERE后面使用的字段信息
$search['select'] = array_unique($field[0]);
}
}
// 检查是否有多余的表关联
$arrSql = explode('JOIN',$testSql);
if( count($arrSql)==1 || strpos(reset($arrSql), $table) === false ){
// 多个JOIN，获取第一个JOIN没有$table，就是正常使用
$search['select'] = empty($search['select'])?[$table . '.*']:$search['select'];
}else{
$checkSql = strstr($testSql, 'WHERE ' . $table);
if (strpos($checkSql, $table) === false) {
/**
* A,没有使用过$table
* B，链排序向下移一位
*/
$first = $first->next();
$table = $first->getData()['table'];
$search['select'] = empty($search['select'])?[$table . '.*']:$search['select'];
} else {
$search['select'] = empty($search['select'])?[$table . '.*']:$search['select'];
}
}
}
/**
* A，检查having当中的字段是否在select
* B，having条件依赖select
*/
$having = $this->getAllHaving($search);
$arrAllHaving = $having['having'];
$param = $having['param'];
if ($arrAllHaving) {
$havingSql = 'HAVING ' . $this->buildWhere($arrAllHaving);
preg_match_all('/[^\(\)\s]+(\.)+[\S]+[^\(\s\)]+/',$havingSql,$m);
if( isset($m[0]) && $m[0] && ($diff = array_diff(array_unique($m[0]),$search['select'])) && !$have_select){
// 有设置having，抽取字段信息
$search['select'] = array_merge($search['select'],$diff);
}
}
$select = implode(',', $search['select']);
$sql = "SELECT {$select} FROM {$table} ";
$link = $first;
while ($link = $link->next()) {
$data = $link->getData();
if ($data['join']) {
$sql .= $data['join'] . ' ';
}
}
// 条件组装
$allWhere = $this->getAllWhere($search);
$arrAllWhere = $allWhere['where'];
$param = array_merge($param,$allWhere['param']);
if ($arrAllWhere) {
$strWhere = $this->buildWhere($arrAllWhere);
$sql = $sql . 'WHERE ' . $strWhere;
}
// group拼接
$group = $this->getAllGroup($search);
if ($group) {
foreach ($group as $groupValue){
$arr = explode('.',$groupValue);
if( isset($arr[1]) && strpos('AS '.$arr[1],$sql) ){
$newGroup[] = $arr[1];
}else{
$newGroup[] = $groupValue;
}
}
$sql = $sql . 'GROUP BY ' . implode(',', $newGroup) . ' ';
if( isset($havingSql) ){
$sql = $sql . $havingSql;
}
}
return [
'sql' => $sql,
'param' => $param,
];
}
public function getAllHaving($search,$type='having')
{
$list = $param = [];
$whereKey = $paramKey = 0;
foreach ($search['search'] as $sea) {
if (isset($sea['having']) && $sea['having']) {
$isAnd = true;
} elseif (isset($sea['orHaving']) && $sea['orHaving']) {
$isAnd = false;
}else{
continue;
}
$arrWhere = $isAnd?$sea['having']:$sea['orHaving'];
foreach ($arrWhere as &$where) {
$key = ':'.$type.++$paramKey.'_'.str_replace(['(',')','.'],'_',$where[0]);
switch ($where[1]){
case 'BETWEEN':
$startKey = $key.'_start';
$endKey = $key.'_end';
$param[$startKey] = $where[2][0];
$param[$endKey] = $where[2][1];
$where[2] = "{$startKey} AND {$endKey}";
break;
case 'IN':
$tempParam = [];
foreach ($where[2] as $inKey=>$value){
$tempParam[$key.$inKey] = $value;
}
$param[$key] = array_merge($param,array_keys($tempParam));
$where[2] = '('.implode(',',$tempParam).')';
break;
default:
$param[$key] = $where[2];
$where[2] = $key;
break;
}
}
$list[++$whereKey . ($isAnd?'_AND':'_OR')] = $arrWhere;
}
return [
'having'=>$list,
'param'=>$param
];
}
private function getAllGroup($search)
{
$list = [];
foreach ($search['search'] as $sea) {
if (isset($sea['group']) && $sea['group']) {
foreach ($sea['group'] as &$where) {
if( strpos($where,'.')===false ){
$where = $sea['table'] . '.' . $where;
}
}
$list = array_merge($sea['group'], $list);
}
}
return array_unique($list);
}
/**
* @param $arrAndWhere
* @param int $level
* @return string
*/
private function buildWhere($arrAndWhere, $level = 0)
{
$where = [];
foreach ($arrAndWhere as $key => $item) {
$isOrWhere = strpos($key, 'OR') === false ? false : true;
if (is_array(reset($item))) {
if ($isOrWhere) {
$where[] = ['where' => 'OR', 'sql' => $this->buildWhere($item, ++$level)];
} else {
$where[] = ['where' => 'AND', 'sql' => $this->buildWhere($item, ++$level)];
}
} else {
if ($isOrWhere) {
$where[] = ['where' => 'OR', 'sql' => implode(' ', $item)];
} else {
$where[] = ['where' => 'AND', 'sql' => implode(' ', $item)];
}
}
}
$string = '';
foreach ($where as $arr) {
if (!$string) {
if (count($where) < 2) {
$string .= "{$arr['sql']} ";
} else {
$string .= "({$arr['sql']}) ";
}
} else {
$string .= "{$arr['where']} ({$arr['sql']}) ";
}
}
return $string;
}
private function getAllWhere($search,$type='where')
{
$list = $param = [];
$whereKey = $paramKey = 0;
foreach ($search['search'] as $sea) {
if (isset($sea['where']) && $sea['where']) {
$isAnd = true;
} elseif (isset($sea['orWhere']) && $sea['orWhere']) {
$isAnd = false;
}else{
continue;
}
$arrWhere = $isAnd?$sea['where']:$sea['orWhere'];
foreach ($arrWhere as &$where) {
self::$paramKey++;
if( is_string($where[0]) ){
if (strpos($where[0], '(') === false) {
switch ($where[1]) {
case 'BETWEEN':
$key = ':' . $type . self::$paramKey . '_' . $where[0];
$startKey = $key . '_start';
$endKey = $key . '_end';
$param[$startKey] = $where[2][0];
$param[$endKey] = $where[2][1];
$where[2] = "{$startKey} AND {$endKey}";
break;
case 'IN':
$key = ':' . $type . self::$paramKey . '_' . $where[0];
if (is_array($where[2])) {
$tempParam = [];
foreach ($where[2] as $inKey => $value) {
$tempParam[$key . $inKey] = $value;
}
// $param[$key] = array_merge($param,array_keys($tempParam));
$where[2] = "('" . implode("','", $tempParam) . "')";
} else {
$where[2] = "(" . $where[2] . ")";
}
break;
default:
$key = ':' . $type . self::$paramKey . '_' . $where[0];
$param[$key] = $where[2];
$where[2] = $key;
break;
}
$where[0] = $sea['table'] . '.' . $where[0];
} else {
$key = ':' . $type . self::$paramKey . '_function';
$param[$key] = $where[2];
$where[2] = $key;
}
}else{
foreach ($where as &$arrWhereSecant){
self::$paramKey++;
switch ($arrWhereSecant[1]){
case 'BETWEEN':
$key = ':'.$type.self::$paramKey.'_'.$arrWhereSecant[0];
$startKey = $key.'_start';
$endKey = $key.'_end';
$param[$startKey] = $arrWhereSecant[2][0];
$param[$endKey] = $arrWhereSecant[2][1];
$arrWhereSecant[2] = "{$startKey} AND {$endKey}";
break;
case 'IN':
$key = ':'.$type.self::$paramKey.'_'.$where[0];
if (is_array($arrWhereSecant[2])) {
$tempParam = [];
foreach ($arrWhereSecant[2] as $inKey => $value) {
$tempParam[$key . $inKey] = $value;
}
// $param[$key] = array_merge($param,array_keys($tempParam));
$arrWhereSecant[2] = "('" . implode("','", $tempParam) . "')";
} else {
$arrWhereSecant[2] = "(" . $arrWhereSecant[2] . ")";
}
break;
default:
$key = ':'.$type.self::$paramKey.'_'.$arrWhereSecant[0];
$param[$key] = $arrWhereSecant[2];
$arrWhereSecant[2] = $key;
break;
}
$arrWhereSecant[0] = $sea['table'] . '.' . $arrWhereSecant[0];
}
}
}
$list[++$whereKey . ($isAnd?'_AND':'_OR')] = $arrWhere;
}
return [
'where'=>$list,
'param'=>$param
];
}
/**
* 获取所有join
*
* @param $search
* @return Link
* @throws \Exception
*/
private function getAllJson($search)
{
$list = [];
foreach ($search['search'] as $sea) {
if ($sea['join']) {
// 根据设计的外键拼接json
if( isset($sea['join']['foreign']) ){
// 如果明确指定第二表外键
$foreign = $sea['join']['foreign'];
$list[$sea['table']][] = "{$sea['join']['type']} {$sea['table']} ON {$sea['table']}.{$sea['join']['key']}={$foreign}";
// 检查join语句是否有引入新的表
$arr = explode('.', $foreign);
if (!isset($list[$arr[0]])) {
$list[$arr[0]] = [];
}
}elseif (isset($this->table[$sea['table']]['foreign'][$sea['join']['key']])) {
// 采用预设外键
$foreign = $this->table[$sea['table']]['foreign'][$sea['join']['key']];
$list[$sea['table']][] = "{$sea['join']['type']} {$sea['table']} ON {$sea['table']}.{$sea['join']['key']}={$foreign}";
// 检查join语句是否有引入新的表
$arr = explode('.', $foreign);
if (!isset($list[$arr[0]])) {
$list[$arr[0]] = [];
}
} else {
rdump($search,$list);
throw new \Exception('需要配置外键声明' . $sea['join']['key']);
}
} elseif (!isset($list[$sea['table']])) {
$list[$sea['table']] = [];
}
}
// 进行关联排序
$first = $this->sortJoin($list);
// 所有已经排序的table
$link = $first;
do {
$table = $link->getData()['table'];
$isSortTable[$table] = $table;
} while ($link = $link->next());
// 找出还没有排序的table
$noSort = array_diff(array_keys($list), $isSortTable);
if (!empty($noSort)) {
if (count($noSort) > 1) {
throw new \Exception('需要配置相关外键获取明确指定join链接 : ' . implode(' and ', $noSort));
} else {
$lastTable = reset($noSort);
$data = $first->getData();
if( $data=='header' ){
// 单表查询
$first = new Link([
'table' => $lastTable,
'join' => '',
]);
}else{
$table = $data['table'];
$linkTable = $this->getTableForJoin($table);
if (isset($linkTable[$lastTable])) {
$data['join'] = $linkTable[$lastTable];
$first->setData($data);
$link = new Link([
'table' => $lastTable,
'join' => '',
]);
$link->setNext($first);
$first = $link;
} else {
throw new \Exception('需要配置相关外键获取明确指定join链接 :' . $table . ' -> ' . $lastTable);
}
}
}
}
return $first;
}
/**
* 对所有表名称进行排序
*
* @param $list
* @return Link
*/
private function sortJoin($list)
{
$first = new Link('header');
foreach ($list as $table => $arrJoin) {
foreach ($arrJoin as $join) {
// 获取join前一步必须的表名
$arr = explode('ON', $join);
$arr = explode('=', end($arr));
$table_1 = trim(explode('.', reset($arr))[0]);
// SQL当中$table_2表必须在$table_1之前出现
$table_2 = trim(explode('.', end($arr))[0]);
$link_1 = new Link([
'table' => $table_1,
'join' => $join,
]);
$link_2 = new Link([
'table' => $table_2,
'join' => '',
]);
if ($first->getData() == 'header') {
$first = $link_2;
$first->setNext($link_1);
} else {
$link = $first;
$hasTable1 = false;
$hasTable2 = false;
do {
if (($tempTable = $link->getData()['table']) == $table_2) {
$hasTable2 = $link;
} elseif ($tempTable == $table_1) {
$hasTable1 = $link;
}
} while ($link = $link->next());
if (!$hasTable1) {
if ($hasTable2) {
$next = $hasTable2->next();
if ($next) {
$link_1->setNext($next);
}
$hasTable2->setNext($link_1);
} else {
$link_1->setNext($first);
$first = $link_1;
}
} elseif (!$hasTable1->getData()['join']) {
$link_1->setNext($first);
$first = $link_1;
$next = $hasTable1->next();
$first->setNext($next);
unset($next);
}
if (!$hasTable2) {
$link_2->setNext($first);
$first = $link_2;
} elseif (!$hasTable2->getData()['join']) {
$link_2->setNext($first);
$first = $link_2;
$next = $hasTable2->next();
$first->setNext($next);
unset($next);
}
}
}
}
return $first;
}
/**
* 获取表外键所有相关表
*
* @param $fromTable
* @return array
*/
private function getTableForJoin($fromTable)
{
$list = [];
foreach ($this->table as $table => $arr) {
if ($table == $fromTable) {
foreach ($arr['foreign'] as $field => $item) {
$temTable = explode('.', $item)[0];
$list[$temTable] = "INNER JOIN {$fromTable} ON {$fromTable}.{$field}=" . $item;
}
} else {
foreach ($arr['foreign'] as $field => $item) {
$temTable = explode('.', $item)[0];
if ($temTable == $fromTable) {
$list[$table] = "INNER JOIN {$fromTable} ON {$fromTable}.{$field}={$table}." . $field;
}
}
}
}
return $list;
}
}
/**
* 链对象
*/
class Link
{
/**
* @var
*/
private static $all_link;
private $data;
private $next;
private $key;
private static $num=0;
public function __construct($data = 'header')
{
$this->data = $data;
self::$num++;
$this->key = self::$num;
self::$all_link[$this->key] = $this;
}
public function setData($data)
{
$this->data = $data;
}
public function getData()
{
return $this->data;
}
public function setNext(Link $data)
{
$this->next = $data->key;
}
public function next()
{
return self::$all_link[$this->next] ?? null;
}
}
Link类是一个模拟c语言的指针连，用来存储join表上下关系
难点
SearchMysqlService类中，大部分代码用来分析表关联，和上下级关系。
$table属性描述了默认表关系（如果不传入外键关系），
因为join语句的第一个table很关键，所以必须保证sql第一个表必须在链对象的第一个
