Laravel & multiple count queries using Eloquent

Question

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.

Answer 1

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