laravel10使用db类通过链式调用构建复杂查询

最近由于业务需要，laravel项目需要构建一个比较复杂的查询要求，大概的sql是这样的。

select
  `threec_store_tmps`.`business_tmp_id`,
  `tbt`.`name` as `business_name`,
  `tbt`.`unified_credit`,
  CONCAT(area1.title, '/', area2.title) AS attrbute_business_region_city,
	threec_store_tmps.category_tree_id,
  `tbt`.`status` as `tstatus`,
  CASE
    WHEN tbt.status = 2 THEN '已上架'
    ELSE '未上架'
  END as business_status,
  CASE
    WHEN tbt.status = 2 THEN tbt.updated_at
    ELSE ''
  END as business_updated_at,
  `threec_store_tmps`.`title`,
  GROUP_CONCAT(distinct c.title SEPARATOR '、') AS category_names,
  CONCAT(
    threec_store_tmps.start_time,
    '--',
    threec_store_tmps.end_time
  ) as open_time,
  `threec_store_tmps`.`company_name`,
  CONCAT(
    threec_store_tmps.region_name,
    '/',
    threec_store_tmps.city_name
  ) AS attrbute_region_city,
  `threec_store_tmps`.`company_region`,
  `threec_store_tmps`.`address`,
  `threec_store_tmps`.`legal_person`,
  `threec_store_tmps`.`phone`,
  CASE
    WHEN threec_store_tmps.status = 3 THEN '已上架'
    ELSE '未上架'
  END as uphold,
  CASE
    WHEN threec_store_tmps.status = 3 THEN threec_store_tmps.updated_at
    ELSE ''
  END as store_updated_at
from
  `threec_store_tmps`
  left join `threec_business_tmp` as `tbt` on `threec_store_tmps`.`business_id` = `tbt`.`business_id`
  left join `area` as `area1` on `tbt`.`area` = `area1`.`id`
  left join `area` as `area2` on `tbt`.`city` = `area2`.`id`
  inner join `store_category_tree` as `c` on JSON_CONTAINS(
    threec_store_tmps.category_tree_id,
    CAST(c.id AS CHAR)
  ) <> ''
where
  `threec_store_tmps`.`category_tree_id` != ''
  and `threec_store_tmps`.`category_tree_id` is not null
  and `threec_store_tmps`.`business_id` <> 0
  and `tbt`.`id` <> 0
  and (
    `tbt`.`name` like '%92410400MA9L5N6K4A%'
    or `tbt`.`unified_credit` like '%92410400MA9L5N6K4A%'
  )
  and `threec_store_tmps`.`deleted_at` is null
group by
  `threec_store_tmps`.`id`,
  `threec_store_tmps`.`title`
order by
  `threec_store_tmps`.`id` asc
limit
  500 offset 0

1.我网上搜了一下laravel的连表查询都是a join b on a.cloumn=b.column，这种带等于号的。所以，用laravel构建的难点是on后面的这部分:JSON_CONTAINS(threec_store_tmps.category_tree_id,
CAST(c.id AS CHAR)).该怎么构建？

2.通过翻阅源码，发现join()居然全部有六个参数，具体源码如下：

 /**
     * Add a join clause to the query.
     *
     * @param  \Illuminate\Contracts\Database\Query\Expression|string  $table
     * @param  \Closure|\Illuminate\Contracts\Database\Query\Expression|string  $first
     * @param  string|null  $operator
     * @param  \Illuminate\Contracts\Database\Query\Expression|string|null  $second
     * @param  string  $type
     * @param  bool  $where
     * @return $this
     */
    public function join($table, $first, $operator = null, $second = null, $type = 'inner', $where = false)
    {
        $join = $this->newJoinClause($this, $type, $table);

        // If the first "column" of the join is really a Closure instance the developer
        // is trying to build a join with a complex "on" clause containing more than
        // one condition, so we'll add the join and call a Closure with the query.
        if ($first instanceof Closure) {
            $first($join);

            $this->joins[] = $join;

            $this->addBinding($join->getBindings(), 'join');
        }

        // If the column is simply a string, we can assume the join simply has a basic
        // "on" clause with a single condition. So we will just build the join with
        // this simple join clauses attached to it. There is not a join callback.
        else {
            $method = $where ? 'where' : 'on';

            $this->joins[] = $join->$method($first, $operator, $second);

            $this->addBinding($join->getBindings(), 'join');
        }

        return $this;
    }

3.发现第六个参数有where或者on两者方式来构建sql,遂通过builder构建以下sql来完成连表，其实最重要的就是把最后一个where参数设为ture就可以。

->join('store_category_tree as c',DB::raw("JSON_CONTAINS(threec_store_tmps.category_tree_id, CAST(c.id AS CHAR))",),'<>','','inner',true)

二、下面放出完整构建builder的链式查询代码，供大家学习。

use DB;
use App\Models\Threec\ThreecStoreTmp;


/**
     * 获取门店信息
     */
    protected function getData(){
        $query = ThreecStoreTmp::select([
            'threec_store_tmps.business_tmp_id',
            'tbt.name as business_name',
            'tbt.unified_credit',
            DB::raw("CONCAT(area1.title,'/',area2.title) AS attrbute_business_region_city"),
            'tbt.status as tstatus',
            DB::raw("CASE WHEN tbt.status = 2 THEN '已上架' ELSE '未上架' END as business_status"),
            DB::raw("CASE WHEN tbt.status = 2 THEN tbt.updated_at ELSE '' END as business_updated_at"),
            'threec_store_tmps.title',
            DB::raw("GROUP_CONCAT(distinct c.title SEPARATOR '、') AS category_names"),
            DB::raw("CONCAT(threec_store_tmps.start_time, '--', threec_store_tmps.end_time) as open_time"),
            'threec_store_tmps.company_name',
            DB::raw("CONCAT(threec_store_tmps.region_name,'/',threec_store_tmps.city_name) AS attrbute_region_city"),
            'threec_store_tmps.company_region',
            'threec_store_tmps.address',
            'threec_store_tmps.name as contact',
            'threec_store_tmps.phone',
            DB::raw("CASE WHEN threec_store_tmps.status = 3 THEN '已上架' ELSE '未上架' END as uphold"),
            DB::raw("CASE WHEN threec_store_tmps.status = 3 THEN threec_store_tmps.updated_at ELSE '' END as store_updated_at")
        ])
        ->leftJoin('threec_business_tmp as tbt', 'threec_store_tmps.business_id', '=', 'tbt.business_id')
        ->leftJoin('area as area1', 'tbt.area', '=', 'area1.id')
        ->leftJoin('area as area2', 'tbt.city', '=', 'area2.id')
        ->join('store_category_tree as c',DB::raw("JSON_CONTAINS(threec_store_tmps.category_tree_id, CAST(c.id AS CHAR))",),'<>','','inner',true)
        ->where('threec_store_tmps.category_tree_id', '!=', '')
        ->whereNotNull('threec_store_tmps.category_tree_id')
        ->where('threec_store_tmps.business_tmp_id', '<>', 0)
        ->where('tbt.id', '<>', 0)
        // append search condition
        ->when(isset($this->search['id']), function ($query) {
            return $query->where('tbt.id',$this->search['id']);
        })
        ->when(isset($this->search['keyword']), function ($query) {
            return $query->where(function ($query) {
                $query->where('tbt.name', 'like', "%{$this->search['keyword']}%")
                    ->orWhere('tbt.unified_credit', 'like', "%{$this->search['keyword']}%");
            });
        })->when(isset($this->search['area']), function ($query) {
            return $query->where('tbt.area', "{$this->search['area']}");
        })->when(isset($this->search['status']), function ($query) {
            return $query->where('tbt.status', $this->search['status']);
        })->when(isset($this->search['created_at']['end']), function ($query) {
            return $query->where('tbt.created_at', '<', $this->search['created_at']['end'])
                ->where('tbt.created_at', '>=', $this->search['created_at']['start']);
        })
        ->groupBy('threec_store_tmps.id', 'threec_store_tmps.title');
        
        return $query;
    }

这样子，就不单单可以查询出数据，还可以额外的附加各种检索条件了。