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

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

I've updated the answer with the query I finally used. Thanks again Vinícius.
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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related