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.

vFragosop · Accepted Answer · 2013-05-29 19:10:54Z

5

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

edited May 29, 2013 at 19:10

answered Mar 15, 2013 at 17:17

vFragosop

5,7731 gold badge31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

twmbx Over a year ago

I've updated the answer with the query I finally used. Thanks again Vinícius.

Chris J Allen Over a year ago

group_by() and order_by() didn't work for me. I had to use groupBy() and orderBy().

Collectives™ on Stack Overflow

Laravel & multiple count queries using Eloquent

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related