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

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;