mysql多次查询_mysql – 使用Eloquent的Laravel和多次计数查询

每当在模型类上调用静态方法时,它都会返回一个Fluent查询,如DB :: table(‘yourmodeltable’) – >方法.如果你牢记这一点,你很快就会意识到可以用Eloquent模型进行任何查询.

现在,要实现更高的性能,可以使用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);

}

这应该打印如下：

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

编辑：

上面建议的方法返回Eloquent Model的实例,这看起来很奇怪,特别是如果你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);

}