Laravel 和使用 Eloquent 的多计数查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15437854/
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 & multiple count queries using Eloquent
提问by twmbx
I'm trying to optimise my code wherein I generate stats over the last seven days.
我正在尝试优化我的代码，其中我在过去 7 天内生成了统计信息。
Presently I'm using eloquent & querying counts for recorded data for each day, this causes me to make 7 seperate queries within a loop.
目前，我正在使用 eloquent & 查询每天记录数据的计数，这导致我在循环中进行 7 个单独的查询。
example:
例子：
// loop for number of days
for($i = 0; $i < $days; $i++){
// some logic to set comparitive unix times
$oldest = $newest - $dayDuration;
// count number of objects between oldest time and newest time
$counts[$i] = Object::where('objecttime','>',$oldest)
->where('objecttime','<',$newest)->count();
// more logic to set comparitive unix times
$newest = $newest - $dayDuration;
}
I know that one can group queries in sql using similar syntax as described here; what I would like to know is if it is possible to do the same using eloquent/fluent in Laravel, or is it only possible to do this using raw queries?
我知道可以使用此处描述的类似语法对 sql 中的查询进行分组；我想知道的是是否可以在 Laravel 中使用 eloquent/fluent 来做同样的事情，还是只能使用原始查询来做到这一点？
EDIT: I don't know if I need to clarify, but this is a Laravel 3 question.
编辑：我不知道是否需要澄清，但这是 Laravel 3 问题。
回答by vFragosop
Whenever you call a static method on your model class, it returns a Fluent query like DB::table('yourmodeltable')->method. If you keep that in mind, you will soon realize it's possible to do any query with Eloquent models.
每当您在模型类上调用静态方法时，它都会返回一个 Fluent 查询，如DB::table('yourmodeltable')->method. 如果您牢记这一点，您很快就会意识到可以使用 Eloquent 模型进行任何查询。
Now, to achieve greater performance, you can use SQLs DATE()function. My example below is untested, so feel free to correct it please.
现在，为了获得更高的性能，您可以使用 SQL 的DATE()函数。我下面的示例未经测试，请随时更正。
// tomorrow -1 week returns tomorrow's 00:00:00 minus 7 days
// you may want to come up with your own date tho
$date = new DateTime('tomorrow -1 week');
// DATE(objecttime) turns it into a 'YYYY-MM-DD' string
// records are then grouped by that string
$days = Object::where('objecttime', '>', $date)
->group_by('date')
->order_by('date', 'DESC') // or ASC
->get(array(
DB::raw('DATE(`objecttime`) AS `date`'),
DB::raw('COUNT(*) as `count`')
));
foreach ($days as $day) {
print($day->date . ' - '. $day->count);
}
This should print something like:
这应该打印如下内容：
2013-03-09 - 13
2013-03-10 - 30
2013-03-11 - 93
2013-03-12 - 69
2013-03-13 - 131
2013-03-14 - 185
2013-03-15 - 69
Edit:
编辑：
The suggested approach above returns instances of Eloquent Model, which may seem weird, specially if you var_dump($days). You can also use Fluent's list()method to achieve the same thing.
上面建议的方法返回 Eloquent 模型的实例，这可能看起来很奇怪，特别是如果你var_dump($days). 您也可以使用 Fluent 的list()方法来实现相同的目的。
$date = new DateTime('tomorrow -1 week');
// lists() does not accept raw queries,
// so you have to specify the SELECT clause
$days = Object::select(array(
DB::raw('DATE(`objecttime`) as `date`'),
DB::raw('COUNT(*) as `count`')
))
->where('created_at', '>', $date)
->group_by('date')
->order_by('date', 'DESC') // or ASC
->lists('count', 'date');
// Notice lists returns an associative array with its second and
// optional param as the key, and the first param as the value
foreach ($days as $date => $count) {
print($date . ' - ' . $count);
}
