laravel 5.5 增加宏指令 joinSub, 省去-＞toSql() 和 addBinding($bindings)；

七星静香

已于 2024-11-22 18:29:25 修改

阅读量466

点赞数 6

文章标签： laravel mysql

于 2024-11-22 18:12:48 首次发布

本文链接：https://blog.csdn.net/weixin_44475312/article/details/143979984

版权

laravel 5.5 增加宏指令 joinSub, 省去->toSql() 和 addBinding($bindings);

1. 在laravel5使用join 子查询时

	$sub_query = DB::table('table1')
            ->select(['table1.id', 'cate_id'])
            ->join('table2', 'table1.id', '=', 'table2.id')
            ->where('table1.cate_id', 2)
            ->orderBy('table1.id');
        DB::table('cate as c')
            ->join(DB::raw("({$sub_query->toSql()}) as t"), 'c.id', '=', 't.cate_id')
            ->addBinding($sub_query->getBindings())
            ->get();

打印sql

select * from cate as c inner join (select table1.id, cate_id from table1 inner join table2 on table1.id = table2.id where table1.cate_id = 2 order by table1.id asc) as t on c.id = t.cate_id;

2. 增加宏指令 joinSub

  Builder::macro('joinSub', function ($subQuery, $alias, $first, $operator = null, $second = null, $type = 'inner', $where = false) {
  	   if ($subQuery instanceof \Illuminate\Database\Eloquent\Builder || $subQuery instanceof Illuminate\Database\Query\Builder) {
           $sql = $subQuery->toSql();
           $bindings = $subQuery->getBindings();
       } elseif (is_string($subQuery)) {
           $sql = $subQuery;
           $bindings = [];
       } else {
           throw new \InvalidArgumentException('Invalid sub-query provided');
       }

       $joinClause = DB::raw("($sql) as $alias");

       return $this->join($joinClause, $first, $operator, $second, $type, $where)
           ->addBinding($bindings);
   });

3. 使用joinSub()

$sub_query = DB::table('table1')
            ->select(['table1.id','cate_id'])
            ->join('table2', 'table1.id', '=', 'table2.id')
            ->where('cate_id', 1)
            ->orderBy('table1.id');
        DB::table('cate as c')
            ->joinSub($sub_query, 't', 'c.id', '=', 't.cate_id')
            ->get();

打印sql

select * from cate as c inner join (select table1.id, cate_id from table1 inner join table2 on table1.id = table2.id where cate_id = 1 order by table1.id asc) as t on c.id = t.cate_id;