SQL 如何使用 Laravel Query Builder 从子查询中进行选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24823915/
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
How to select from subquery using Laravel Query Builder?
提问by quenty658
I'd like to get value by the following SQL using Eloquent ORM.
我想使用 Eloquent ORM 通过以下 SQL 获取价值。
- SQL
- SQL
SELECT COUNT(*) FROM
(SELECT * FROM abc GROUP BY col1) AS a;
Then I considered the following.
然后我考虑了以下内容。
- Code
- 代码
$sql = Abc::from('abc AS a')->groupBy('col1')->toSql();
$num = Abc::from(\DB::raw($sql))->count();
print $num;
I'm looking for a better solution.
我正在寻找更好的解决方案。
Please tell me simplest solution.
请告诉我最简单的解决方案。
回答by Jarek Tkaczyk
In addition to @delmadord's answer and your comments:
除了@delmadord 的回答和您的评论：
Currently there is no method to create subquery in FROMclause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings:
目前没有在FROM子句中创建子查询的方法，所以需要手动使用raw语句，然后，如果需要，你将合并所有绑定：
$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance
$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
->count();
Mind that you need to merge bindings in correct order. If you have other bound clauses, you must put them after mergeBindings:
请注意，您需要以正确的顺序合并绑定。如果您有其他约束子句，则必须将它们放在mergeBindings:
$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
// ->where(..) wrong
->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
// ->where(..) correct
->count();
回答by mpskovvang
Laravel v5.6.12 (2018-03-14) added fromSub()and fromRaw()methods to query builder (#23476).
添加了 Laravel v5.6.12 (2018-03-14)fromSub()和fromRaw()查询构建器的方法(#23476)。
The accepted answer is correct but can be simplified into:
接受的答案是正确的，但可以简化为：
DB::query()->fromSub(function ($query) {
$query->from('abc')->groupBy('col1');
}, 'a')->count();
The above snippet produces the following SQL:
上面的代码片段生成以下 SQL：
select count(*) as aggregate from (select * from `abc` group by `col1`) as `a`
回答by Thiago Mata
The solution of @JarekTkaczyk it is exactly what I was looking for. The only thing I miss is how to do it when you are using
DB::table()queries. In this case, this is how I do it:
@JarekTkaczyk 的解决方案正是我正在寻找的。我唯一想念的是在使用DB::table()查询时如何做
。在这种情况下，我就是这样做的：
$other = DB::table( DB::raw("({$sub->toSql()}) as sub") )->select(
'something',
DB::raw('sum( qty ) as qty'),
'foo',
'bar'
);
$other->mergeBindings( $sub );
$other->groupBy('something');
$other->groupBy('foo');
$other->groupBy('bar');
print $other->toSql();
$other->get();
Special atention how to make the mergeBindingswithout using the getQuery()method
特别注意mergeBindings不使用getQuery()方法如何制作
回答by Sasa Blagojevic
From laravel 5.5 there is a dedicated method for subqueries and you can use it like this:
从 laravel 5.5 开始，有一个专门的子查询方法，你可以像这样使用它：
Abc::selectSub(function($q) {
$q->select('*')->groupBy('col1');
}, 'a')->count('a.*');
or
或者
Abc::selectSub(Abc::select('*')->groupBy('col1'), 'a')->count('a.*');
回答by Guy Mazuz
I like doing something like this:
我喜欢做这样的事情：
Message::select('*')
->from(DB::raw("( SELECT * FROM `messages`
WHERE `to_id` = ".Auth::id()." AND `isseen` = 0
GROUP BY `from_id` asc) as `sub`"))
->count();
It's not very elegant, but it's simple.
它不是很优雅，但很简单。
回答by peter.babic
I could not made your code to do the desired query, the AS is an alias only for the table abc, not for the derived table.
Laravel Query Builder does not implicitly support derived table aliases, DB::raw is most likely needed for this.
我无法让您的代码执行所需的查询，AS 只是表的别名abc，而不是派生表的别名。Laravel Query Builder 不隐式支持派生表别名，DB::raw 最有可能为此需要。
The most straight solution I could came up with is almost identical to yours, however produces the query as you asked for:
我能想到的最直接的解决方案与您的几乎相同，但是会按照您的要求生成查询：
$sql = Abc::groupBy('col1')->toSql();
$count = DB::table(DB::raw("($sql) AS a"))->count();
The produced query is
产生的查询是
select count(*) as aggregate from (select * from `abc` group by `col1`) AS a;
回答by dkop
Correct way described in this answer: https://stackoverflow.com/a/52772444/2519714Most popular answer at current moment is not totally correct.
此答案中描述的正确方法：https: //stackoverflow.com/a/52772444/2519714当前最受欢迎的答案并不完全正确。
This way https://stackoverflow.com/a/24838367/2519714is not correct in some cases like: sub select has where bindings, then joining table to sub select, then other wheres added to all query. For example query:
select * from (select * from t1 where col1 = ?) join t2 on col1 = col2 and col3 = ? where t2.col4 = ?To make this query you will write code like:
这样https://stackoverflow.com/a/24838367/2519714在某些情况下是不正确的，例如：子选择具有 where 绑定，然后将表连接到子选择，然后将其他 wheres 添加到所有查询中。例如查询：
select * from (select * from t1 where col1 = ?) join t2 on col1 = col2 and col3 = ? where t2.col4 = ?要进行此查询，您将编写如下代码：
$subQuery = DB::query()->from('t1')->where('t1.col1', 'val1');
$query = DB::query()->from(DB::raw('('. $subQuery->toSql() . ') AS subquery'))
->mergeBindings($subQuery->getBindings());
$query->join('t2', function(JoinClause $join) {
$join->on('subquery.col1', 't2.col2');
$join->where('t2.col3', 'val3');
})->where('t2.col4', 'val4');
During executing this query, his method $query->getBindings()will return bindings in incorrect order like ['val3', 'val1', 'val4']in this case instead correct ['val1', 'val3', 'val4']for raw sql described above.
在执行此查询期间，他的方法$query->getBindings()将以不正确的顺序返回绑定，例如['val3', 'val1', 'val4']在这种情况下，而不是['val1', 'val3', 'val4']对上述原始 sql正确。
One more time correct way to do this:
另一种正确的方法来做到这一点：
$subQuery = DB::query()->from('t1')->where('t1.col1', 'val1');
$query = DB::query()->fromSub($subQuery, 'subquery');
$query->join('t2', function(JoinClause $join) {
$join->on('subquery.col1', 't2.col2');
$join->where('t2.col3', 'val3');
})->where('t2.col4', 'val4');
Also bindings will be automatically and correctly merged to new query.
绑定也会自动正确地合并到新查询中。
