Laravel Fluent Query Builder 加入子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18079281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Fluent Query Builder Join with subquery
提问by driechel
Okay after hours of research and still using DB::select I have to ask this question. Because I am about to trough my computer away ;).
好的，经过数小时的研究并仍在使用 DB::select 我不得不问这个问题。因为我即将离开我的电脑;)。
I want to get the last input of a user (base on the timestamp). I can do this with the raw sql
我想获取用户的最后输入（基于时间戳）。我可以用原始 sql 做到这一点
SELECT c.*, p.*
FROM users c INNER JOIN
(
SELECT user_id,
MAX(created_at) MaxDate
FROM `catch-text`
GROUP BY user_id
) MaxDates ON c.id = MaxDates.user_id INNER JOIN
`catch-text` p ON MaxDates.user_id = p.user_id
AND MaxDates.MaxDate = p.created_at
I got this query from another post hereon stackoverflow.
我从另一个帖子此查询这里的计算器。
I have tried everything to do this with the fluent query builder in Laravel however with no success.
我已经尝试使用 Laravel 中的流畅查询构建器来执行此操作，但没有成功。
I know the manual says you can do this:
我知道手册说你可以这样做：
DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
But that is not helping much because I do not see how I could use a subquery there? Anyone who can light up my day?
但这并没有多大帮助，因为我不知道如何在那里使用子查询？谁能点亮我的一天？
回答by driechel
Ok for all of you out there that arrived here in desperation searching for the same problem. I hope you will find this quicker then I did ;O.
对于那些在绝望中寻找相同问题的人来说，好的。我希望你能比我更快地找到它；O。
This is how it is solved. JoostK told me at github that "the first argument to join is the table (or data) you're joining.". And he was right.
这就是它的解决方法。JoostK 在 github 告诉我“加入的第一个参数是你要加入的表（或数据）。”。他是对的。
Here is the code. Different table and names but you will get the idea right? It t
这是代码。不同的表和名称，但你会明白吗？它
DB::table('users')
->select('first_name', 'TotalCatches.*')
->join(DB::raw('(SELECT user_id, COUNT(user_id) TotalCatch,
DATEDIFF(NOW(), MIN(created_at)) Days,
COUNT(user_id)/DATEDIFF(NOW(), MIN(created_at))
CatchesPerDay FROM `catch-text` GROUP BY user_id)
TotalCatches'),
function($join)
{
$join->on('users.id', '=', 'TotalCatches.user_id');
})
->orderBy('TotalCatches.CatchesPerDay', 'DESC')
->get();
回答by ph4r05
I was looking for a solution to quite a related problem: finding the newest records per group which is a specialization of a typical greatest-n-per-groupwith N = 1.
我正在寻找一个相当相关问题的解决方案：找到每组的最新记录，这是N = 1的典型每组最大 n 的特化。
The solution involves the problem you are dealing with here (i.e., how to build the query in Eloquent) so I am posting it as it might be helpful for others. It demonstrates a cleaner way of sub-query construction using powerful Eloquent fluent interface with multiple join columns and wherecondition inside joined sub-select.
该解决方案涉及您在此处处理的问题（即，如何在 Eloquent 中构建查询），因此我将其发布，因为它可能对其他人有帮助。它使用强大的 Eloquent fluent 接口演示了一种更简洁的子查询构建方式，其中包含多个连接列和where连接子选择内的条件。
In my example I want to fetch the newest DNS scan results (table scan_dns) per group identified by watch_id. I build the sub-query separately.
在我的示例中，我想获取scan_dns由watch_id. 我单独构建子查询。
The SQL I want Eloquent to generate:
我希望 Eloquent 生成的 SQL：
SELECT * FROM `scan_dns` AS `s`
INNER JOIN (
SELECT x.watch_id, MAX(x.last_scan_at) as last_scan
FROM `scan_dns` AS `x`
WHERE `x`.`watch_id` IN (1,2,3,4,5,42)
GROUP BY `x`.`watch_id`) AS ss
ON `s`.`watch_id` = `ss`.`watch_id` AND `s`.`last_scan_at` = `ss`.`last_scan`
I did it in the following way:
我是通过以下方式做到的：
// table name of the model
$dnsTable = (new DnsResult())->getTable();
// groups to select in sub-query
$ids = collect([1,2,3,4,5,42]);
// sub-select to be joined on
$subq = DnsResult::query()
->select('x.watch_id')
->selectRaw('MAX(x.last_scan_at) as last_scan')
->from($dnsTable . ' AS x')
->whereIn('x.watch_id', $ids)
->groupBy('x.watch_id');
$qqSql = $subq->toSql(); // compiles to SQL
// the main query
$q = DnsResult::query()
->from($dnsTable . ' AS s')
->join(
DB::raw('(' . $qqSql. ') AS ss'),
function(JoinClause $join) use ($subq) {
$join->on('s.watch_id', '=', 'ss.watch_id')
->on('s.last_scan_at', '=', 'ss.last_scan')
->addBinding($subq->getBindings());
// bindings for sub-query WHERE added
});
$results = $q->get();
UPDATE:
更新：
Since Laravel 5.6.17the sub-query joinswere added so there is a native way to build the query.
从Laravel 5.6.17 开始，添加了子查询连接，因此有一种本地方式来构建查询。
$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();
回答by shalonteoh
I think what you looking for is "joinSub". It's supported from laravel ^5.6. If you using laravel version below 5.6 you can also register it as macro in your app service provider file. like this https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/171#issuecomment-413062522
我想你要找的是“joinSub”。laravel ^5.6 支持它。如果您使用低于 5.6 的 Laravel 版本，您还可以在您的应用服务提供程序文件中将其注册为宏。像这样https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/171#issuecomment-413062522
$subquery = DB::table('catch-text')
->select(DB::raw("user_id,MAX(created_at) as MaxDate"))
->groupBy('user_id');
$query = User::joinSub($subquery,'MaxDates',function($join){
$join->on('users.id','=','MaxDates.user_id');
})->select(['users.*','MaxDates.*']);
回答by Amit Meena
Query with sub query in Laravel
在 Laravel 中使用子查询进行查询
$resortData = DB::table('resort')
->leftJoin('country', 'resort.country', '=', 'country.id')
->leftJoin('states', 'resort.state', '=', 'states.id')
->leftJoin('city', 'resort.city', '=', 'city.id')
->select('resort.*', 'country.name as country_name', 'states.name as state_name','city.name as city_name', DB::raw("(SELECT GROUP_CONCAT(amenities.name) from resort_amenities LEFT JOIN amenities on amenities.id= resort_amenities.amenities_id WHERE resort_amenities.resort_id=resort.id) as amenities_name"))->groupBy('resort.id')
->orderBy('resort.id', 'DESC')
->get();
回答by cby016
I can't comment because my reputation is not high enough. @Franklin Rivero if you are using Laravel 5.2 you can set the bindings on the main query instead of the join using the setBindings method.
我不能发表评论，因为我的声誉不够高。@Franklin Rivero 如果您使用的是 Laravel 5.2，则可以使用 setBindings 方法在主查询上设置绑定而不是连接。
So the main query in @ph4r05's answer would look something like this:
所以@ph4r05 的答案中的主要查询看起来像这样：
$q = DnsResult::query()
->from($dnsTable . ' AS s')
->join(
DB::raw('(' . $qqSql. ') AS ss'),
function(JoinClause $join) {
$join->on('s.watch_id', '=', 'ss.watch_id')
->on('s.last_scan_at', '=', 'ss.last_scan');
})
->setBindings($subq->getBindings());
回答by Maksim Martianov
You can use following addon to handle all subquery related function from laravel 5.5+
您可以使用以下插件来处理 laravel 5.5+ 中所有与子查询相关的功能
https://github.com/maksimru/eloquent-subquery-magic
https://github.com/maksimru/eloquent-subquery-magic
User::selectRaw('user_id,comments_by_user.total_count')->leftJoinSubquery(
//subquery
Comment::selectRaw('user_id,count(*) total_count')
->groupBy('user_id'),
//alias
'comments_by_user',
//closure for "on" statement
function ($join) {
$join->on('users.id', '=', 'comments_by_user.user_id');
}
)->get();
