在 Eloquent/Laravel 中使用子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43119995/
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
Using subqueries in Eloquent/Laravel
提问by Peter Astbury
Here's the query in raw SQL:
这是原始 SQL 中的查询：
SELECT *
FROM (
SELECT `characters`.`id`,`characters`.`refreshToken`,
`characters`.`name`,max(`balances`.`created_at`) as `refreshDate`
FROM `characters`
INNER JOIN `balances` ON `characters`.`id` = `balances`.`character`
WHERE `characters`.`refreshToken` IS NOT NULL
GROUP BY `characters`.`id`
) AS `t1`
WHERE `refreshDate` < '2017-03-29';
I've tested this in phpMyAdmin and it returns the expected results. However I'm using the Eloquent and Laravel libraries in my PHP app and I'm not sure how to approach this. How exactly do subqueries work in this case?
我已经在 phpMyAdmin 中对此进行了测试，它返回了预期的结果。但是，我在我的 PHP 应用程序中使用了 Eloquent 和 Laravel 库，但我不确定如何解决这个问题。在这种情况下，子查询究竟是如何工作的？
回答by Eric Tucker
You can do a subquery as a table but need to create the subquery first and then merge the bindings into the parent query:
您可以将子查询作为表执行，但需要先创建子查询，然后将绑定合并到父查询中：
$sub = Character::select('id', 'refreshToken', 'name')
->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
->join('balances', 'characters.id', '=', 'balances.character')
->whereNotNull('characters.refreshToken')
->groupBy('characters.id');
DB::table(DB::raw("($sub->toSql()) as t1"))
->mergeBindings($sub)
->where('refreshDate', '<', '2017-03-29')
->get();
If that is your entire query you can do it without the subquery and use having()instead like:
如果这是您的整个查询，您可以在没有子查询的情况下执行此操作，having()而是使用如下所示：
Character::select('id', 'refreshToken', 'name')
->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
->join('balances', 'characters.id', '=', 'balances.character')
->whereNotNull('characters.refreshToken')
->groupBy('characters.id')
->having('refreshDate', '<', '2017-03-29');
回答by SUB0DH
You can use subqueries in Eloquent by specifying them as a closure to the wheremethod. For example:
您可以通过将子查询指定为where方法的闭包来在 Eloquent 中使用子查询。例如：
$characters = Character::where(function ($query) {
// subqueries goes here
$query->where(...
...
->groupBy('id');
})
->where('refreshDate', '<', '2017-03-29')
->get();
You have to chain your methods to the $queryvariable that is passed to the closure in the above example.
您必须将您的方法链接到$query传递给上述示例中的闭包的变量。
If you want to pass any variable to the subquery you need the usekeyword as:
如果要将任何变量传递给子查询，则需要将use关键字设为：
$characterName = 'Gandalf';
$characters = Character::where(function ($query) use ($characterName) {
// subqueries goes here
$query->where('name', $characterName)
...
->groupBy('id');
})
->where('refreshDate', '<', '2017-03-29')
->get();
