LEFT
JOIN
(
SELECT
*
FROM
`b`
WHERE
`type`
=
2
)
AS
b
AND
( `a`.`a`
=
`b`.`a`
AND
`a`.`b`
=
`b`.`b`
AND
`a`.`c`
=
`b`.`c` )
OR
( `a`.`a`
=
`b`.`a`
AND
`a`.`b`
=
`b`.`b`
AND
`a`.`d`
=
`b`.`d` )
官方文档给的方法(子查询联接)
您可以使用
joinSub
，
leftJoinSub
和
rightJoinSub
方法加入查询的子查询。这些方法中的每一个都接收三个参数：子查询，其表别名和定义相关列的Closure：
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function ($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();
实现方法1：joinSub
$query = DB::table('table_name2')->where('type','=',2);
$info = DB::table('table_name')
->joinSub($query,'table_name2', function ($join) {
$join->on([
['table_name.a', '=', 'table_name2.a'],
['table_name.b', '=', 'table_name2.b'],
['table_name.c', '=', 'table_name2.c'],
])->orOn([
['table_name.a', '=', 'table_name2.a'],
['table_name.b', '=', 'table_name2.b'],
['table_name.d', '=', 'table_name2.d'],
})->get();
实现方法2：join+getBindings+addBinding
ps: join+子查询只有用到where的时候才需要addBinding，否则sql语句会只有'?'
$query = DB::table('table_name2')->where('type','=',2);
$info = DB::table('table_name')
->join(DB::raw('('.$query->toSql().') as table_name2'), function ($join) use ($query) {
$join->on([
['table_name.a', '=', 'table_name2.a'],
['table_name.b', '=', 'table_name2.b'],
['table_name.c', '=', 'table_name2.c'],
])->orOn([
['table_name.a', '=', 'table_name2.a'],
['table_name.b', '=', 'table_name2.b'],
['table_name.d', '=', 'table_name2.d'],
])->addBinding($query->getBindings());
})->get();
源码剖析joinSub方法
文件地址：/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php
* AddHoneycombLog a subquery join clause to the query.
* @param \Closure|\Illuminate\Database\Query\Builder|string $query
* @param string $as
* @param \Closure|string $first
* @param string|null $operator
* @param string|null $second
* @param string $type
* @param bool $where
* @return \Illuminate\Database\Query\Builder|static
* @throws \InvalidArgumentException
public function joinSub($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false)
[$query, $bindings] = $this->createSub($query);
$expression = '('.$query.') as '.$this->grammar->wrapTable($as);
$this->addBinding($bindings, 'join');
return $this->join(new Expression($expression), $first, $operator, $second, $type, $where);
* Creates a subquery and parse it.
* @param \Closure|\Illuminate\Database\Query\Builder|string $query
* @return array
protected function createSub($query)
if ($query instanceof Closure) {
$callback = $query;
$callback($query = $this->forSubQuery());
return $this->parseSub($query);
* Parse the subquery into SQL and bindings.
* @param mixed $query
* @return array
* @throws \InvalidArgumentException
protected function parseSub($query)
if ($query instanceof self || $query instanceof EloquentBuilder) {
return [$query->toSql(), $query->getBindings()];
} elseif (is_string($query)) {
return [$query, []];
} else {
throw new InvalidArgumentException(
'A subquery must be a query builder instance, a Closure, or a string.'
* AddHoneycombLog a binding to the query.
* @param mixed $value
* @param string $type
* @return $this
* @throws \InvalidArgumentException
public function addBinding($value, $type = 'where')
if (! array_key_exists($type, $this->bindings)) {
throw new InvalidArgumentException("Invalid binding type: {$type}.");
if (is_array($value)) {
$this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
} else {
$this->bindings[$type][] = $value;
return $this;
上述代码已用model的方式实现了原生sql
laravel join 子查询 joinSub的源码剖析就到这了，希望文章可以帮忙解决你的难题，期待你的关注👍
