Laravel&使用Eloquent的多个计数查询 [英] Laravel & multiple count queries using Eloquent

问题描述

我正在尝试优化我的代码，其中我在过去七天内产生统计信息。

目前我正在使用雄辩和查询每天记录数据的计数，这使我在一个循环中进行7个单独的查询。

示例：

（$ i = 0; $ i <$ days; $ i ++）{
//某些逻辑到设置比较unix次
$ oldest = $ newest - $ dayDuration;

//计算最旧时间和最新时间​​之间的对象数量
$ counters [$ i] = Object :: where（'objecttime'，'>'，$ oldest）
- > where（'objecttime'，'<'，$ newest） - > count（）;

//更多逻辑设置比较unix次
$ newest = $ newest - $ dayDuration;
}

我知道可以使用类似的语法在sql中对查询进行分组HREF = https://dba.stackexchange.com/questions/23068/how-to-make-multiple-counts-in-one-query>此处;我想知道的是，如果可以使用雄辩/流利的Laravel做同样的事情，还是只能使用原始查询来做到这一点？

编辑：我不知道我是否需要澄清，但这是一个Laravel 3问题。

解决方案

每当你调用静态方法返回一个流利的查询，如 DB :: table（'yourmodeltable'） - >方法。如果您记住这一点，您将很快意识到可以使用Eloquent模型进行任何查询。

现在，为了实现更高的性能，可以使用SQL DATE（）函数。我下面的例子是未经测试的，所以请随时更正。

  // tomorrow -1周返回明天00:00:00减去7天
 //你可能想要自己的日期tho 
 $ date = new DateTime（'tomorrow -1 week'）; 
 
 // DATE（objecttime）将其变成'YYYY-MM-DD'字符串
 //记录然后按字符串分组
 $ days = Object :: where （'objecttime'，'>'，$ date）
  - > group_by（'date'）
  - > order_by（'date'，'DESC'）//或ASC 
  - > get（array（
 DB :: raw（'DATE（`objecttime`）AS`date`'），
 DB :: raw（'COUNT（*）as`count`' ）
））; 
 
 foreach（$ days as $ day）{
 print（$ day-> date。' - '。$ day-> count）; 
} 
  

这应该打印如下：

  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 
  

编辑：

上述建议的方法返回演绎模型的实例，这可能看起来很奇怪，特别是如果你 var_dump（$ days）。您还可以使用Fluent的 list（）方法来实现同样的事情。

  $ date = new DateTime（'tomorrow -1 week'）; 
 
 // lists（）不接受原始查询，
 //所以你必须指定SELECT子句
 $ 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'）//或ASC 
  - > lists（'count'，'date'）; 
 
 //注意列表返回一个关联数组，其第二个和
 //可选参数作为键，第一个参数作为值
 foreach（$ days as $ date => $ count）{
 print（$ date。' - '。$ count）; 
} 
  

I'm trying to optimise my code wherein I generate stats over the last seven days.

Presently I'm using eloquent & querying counts for recorded data for each day, this causes me to make 7 seperate queries within a loop.

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?

EDIT: I don't know if I need to clarify, but this is a Laravel 3 question.

解决方案

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.

Now, to achieve greater performance, you can use SQLs DATE() function. My example below is untested, so feel free to correct it please.

// 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.

$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);
}

这篇关于Laravel&amp;使用Eloquent的多个计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！