开发ERP中遇到的树形表查询条件嵌套构造

发布：2024-03-15 17:22分类：后端程序评论：1浏览：214

由于树形表具有无限下级的特点，当搜索某关键词时，需要把满足条件子级的顶级记录列出来，并具有分页。此时须用到Join查询，并去重：
``` php
// 模型类 ErpWarehouse.php
public function listQuery($where = [], $fields = '*', $limit=0)
{
	$d = request()->get('','','strip_sql');
	$kw = $d['kw'] ?? '';
	$fds = ['code','name','contact','tel','address'];
	$field = isset($d['fields']) && isset($fds[$d['fields']]) ? $d['fields'] : -1;
	$sotime = $d['sotime'] ?? '';
	$state = $d['state'] ?? '';
	$parentid = isset($d['parentid']) ? intval($d['parentid']) : 0;
	$limit = $limit>0 ? $limit : (isset($d['limit']) ? intval($d['limit']) : 10);
	if($kw!=''){
		if($field>-1){
			$where[] = in_array($field,[0,1]) ? [$fds[$field],'=',$kw] : [$fds[$field],'LIKE', '%'.$kw.'%'];
		}else{
			$where[] = [implode('|',$fds),'LIKE', '%'.$kw.'%'];
		}
	}
	if(strpos($sotime,' - ')!==false){
		$t = explode(' - ',$sotime);
		$where[] = ['addtime','>=',strtotime($t[0]." 00:00:00")];
		$where[] = ['addtime','<=',strtotime($t[1]." 23:59:59")];
	}
	if(is_numeric($state)) $where[] = ['state','=',$state];
	// 区分查询
	if($parentid){
		// 异步子级返回的数据 【这里对条件集进行了重置】
		$where = [['parentid','=',$parentid]];
		return $this->where($where)->order(['id'=>'desc'])->field($fields)->select();
		//echo $this->getLastSql();exit; //打印SQL
	}else{
		if($where){
			// 顶级有搜索时返回的数据，重构数据结构
			// ((A表条件组) OR (B表条件组)) AND a.parentid = $parentid
			// "SELECT DISTINCT a.* FROM vt_erp_warehouse a LEFT JOIN vt_erp_warehouse b ON FIND_IN_SET(a.id, b.arrparentid) WHERe a.parentid = 0 AND b.code LIKE '%Keys%' LIMIT 0,10;";
			$where2 = [];
			foreach($where as &$v){
				$where2[] = ['b.'.str_replace("|","|b.",$v[0]),$v[1],$v[2]];
				$v = ['a.'.str_replace("|","|a.",$v[0]),$v[1],$v[2]];
			}
			return $this->distinct(true)->alias('a')->leftJoin('erp_warehouse b','FIND_IN_SET(a.id, b.arrparentid)')
			->where(
				function($query)use($where,$where2){
					$query->where($where)->whereOr(
						function($query2)use($where2){
							$query2->where($where2);
						}
					);
				}
			)
			->where([['a.parentid','=',$parentid]])
			->order(['a.id'=>'desc'])->field('a.*')->paginate($limit);
			//echo $this->getLastSql();exit; //打印SQL
		}else{
			$where[] = ['parentid','=',$parentid];
			// 顶级无搜索返回的数据
			return $this->where($where)->order(['id'=>'desc'])->field($fields)->paginate($limit);
		}
	}
}
```
![ERP#100%](/file/upload/image/20240315/65f415c7a9a1c.jpg)

树形表 join distinct erp

haohao1 楼
2024-03-15 18:04

<img src="http://img.baidu.com/hi/tsj/t_0006.gif"/>

0 回复 (0)

发布帖子

作者信息

: anna; 等级：Lv2; 积分：4660; 这家伙很懒，什么都没写呢~

开发ERP中遇到的树形表查询条件嵌套构造

发布：2024-03-15 17:22分类：后端程序评论：1浏览：214

作者信息

最近热帖