Tp5.1 搜索器withSearch()的封装以及多表join搜索

a. 封装的搜索器：

/**
     * 单表搜索框查询（使用where[]和搜索器）
     *
     * @param $request
     * @param $data //存放查询条件、字段、排序的 数组
     * @param $data ['where']     查询条件  " [ a.id => $id ,b.name => 'test' ] "
     * @param $data ['field']     查询字段  " a.id,title,class_id , b.id,name "
     * @param $data ['order']     排序      " a.id desc "
     * @param $data ['orderRaw']  sql语句排序
     *
     * @param $search // 使用搜索器
     * @param $search [fields] 搜索器字段  ['title', 'type']
     * @param $search [data]   条件 ['title' => $title , 'type' => $type ]
     */
    public function _search($request, $data, $search)
    {
        $page = $request->page ?? 1;
        $limit = $request->limit ?? 5;

        // 时间搜索
        $start = input('start_time');
        $end = input('end_time');
        if (!empty($start) && !empty($end)) {     // 时间搜索
            $data['where'][] = [
                ['update_time', 'between time', [($start), ($end)]],
            ];
        }

        // 状态搜索
        $status = input('status');
        if (strlen($status)) {
            $data['where'][] = [                  // $data['where'][] 叠加，$data['where']替换
                ['status', '=', $status],
            ];
        }

        // 查询
        $list = $this
            ->order($data['order'] ?? '')
            ->orderRaw($data['orderRaw'] ?? 'id desc')
            ->where($data['where'] ?? '')
            ->field($data['field'])
            ->withSearch($search['fields'], $search['data'])->select()->toArray();

        $total = count($list);
        if (!$total)
            sucApi()->send();

        // 分页
        $start = $limit * ($page - 1);
        $data = array_slice($list, $start, $limit);
        return arrayApi(count($data), $data, 'success!!', 'fail!!', $total);
    }

 

使用例子：

public function search()
    {
        $data = [
            'field' => 'id,head_img,title,status,type,update_time',
            'order' => 'status desc,update_time desc'
        ];
        $search = [
            'fields' => ['title', 'type'],
            'data' =>[
                'title' => input('title'),
                'type' => input('type'),
            ]
        ];

        $res =  $this->grant->_search($this->request,$data,$search);
        addDomain($res['data'],'head_img');
        return json($res);
    }

 

 

b.封装的搜索器加上join: （在 a 的基础上加了个 join）

/**
     * join多表搜索框查询（使用where[]和搜索器）
     *
     * @param $request
     * @param $data //存放查询条件、字段、排序的 数组
     * @param $data ['join']      连接对象  " 表名 b "
     * @param $data ['condition'] 连接条件  " a.id = b.classId "
     * @param $data ['where']     查询条件  " [ a.id => $id ,b.name => 'test' ] "
     * @param $data ['field']     查询字段  " a.id,title,class_id , b.id,name "
     * @param $data ['order']     排序      " a.id desc "
     * @param $data ['orderRaw']  sql语句排序
     *
     * @param $search // 使用搜索器
     * @param $search [fields] 搜索器字段  ['title', 'type']
     * @param $search [data]   条件 ['title' => $title , 'type' => $type ]
     * @param $type // left right join
     */
    public function _joinSearch($request, $data, $search, $type = '')
    {
        $page = $request->page ?? 1;
        $limit = $request->limit ?? 5;

        $list = self::alias('a')                     //主表为 a
        ->join($data['join'] ?? '', $data['condition'] ?? '', $type)
            ->where($data['where'] ?? '')
            ->field($data['field'])
            ->order($data['order'] ?? '')
            ->orderRaw($data['orderRaw'] ?? 'a.id desc')
            ->withSearch($search['fields'], $search['data'])
            ->select()->toArray();

        $total = count($list);
        if (!$total)
            sucApi()->send();

        // 分页
        $start = $limit * ($page - 1);
        $data = array_slice($list, $start, $limit);
        return arrayApi(count($data), $data, 'success!!', 'fail!!', $total);
    }

 

使用例子：

public function search()
    {
        $data = [
            'join' => [['role b','a.role_id = b.id','right'],['admin_log admin_log','a.id = admin_log.admin_id','right']],
            'field' => 'a.username,account,b.title,admin_log.content,admin_log.create_time',
            'order' => 'admin_log.create_time desc',
        ];
        $search = [
            'fields' => ['account',  'role_id'],
            'data' =>[
                'account' => input('account'),
                'role_id' => input('role_id'),
            ]
        ];

        // 时间搜索
        $start = input('start_time');
        $end = input('end_time');
        if (!empty($start) && !empty($end)) {     // 时间搜索
            $data['where'][] = [
                ['admin_log.update_time', 'between time', [($start), ($end)]],
            ];
        }
        
        $this->request->limit = 10;
        $list = (new Admin)->_joinSearch($this->request,$data,$search);
       // return $this->log->getLastSql();
        return json($list);
    }