Getting the sum of field with where conditions with Eloquent

Question

I'm trying to get the total sum of downloaded files over all time and the last 24 hours for reporting purposes. The table I'm working on has a downloaded_at field which is a DATETIME type and a size field, which is the file size in bytes. In my model I'm doing the following queries:

return array(
    'totalBandwidth' => self::where('downloaded_at', 'IS NOT', DB::raw('null'))->sum('size'),
    'bandwidthLast24Hours' => self::where('downloaded_at', 'IS NOT', DB::raw('null'))->where('downloaded_at', '>' , new DateTime('yesterday'))->sum('size')
);

Pretty simple, however both of these queries return NULL and I can't figure out why. I've pretty much written these queries based from answers on SO and the Laravel forums.

Have you missed ->get() at the end of your queries?

dlock
– dlock

2013-12-01 04:42:28 +00:00
Commented Dec 1, 2013 at 4:42 — dlock
– dlock, Commented Dec 1, 2013 at 4:42

rmobis · Accepted Answer · 2013-12-01 05:30:16Z

2

That's because you can't check for IS NOT NULL like that. You gotta use the whereNotNull method. Example:

return array(
    'totalBandwidth' => self::whereNotNull('downloaded_at')->sum('size'),
    'bandwidthLast24Hours' => self::whereNotNull('downloaded_at')->where('downloaded_at', '>', new DateTime('yesterday')->sum('size')
);

answered Dec 1, 2013 at 5:30

rmobis

27.1k8 gold badges72 silver badges69 bronze badges

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

Comments

Fractaliste · Accepted Answer · 2013-12-01 13:23:17Z

1

Operators usable in the where function are the same of query builder one's :

protected $operators = array(
    '=', '<', '>', '<=', '>=', '<>', '!=',
    'like', 'not like', 'between', 'ilike',
    '&', '|', '^', '<<', '>>',
);

answered Dec 1, 2013 at 13:23

Fractaliste

5,99511 gold badges47 silver badges88 bronze badges

Collectives™ on Stack Overflow

Getting the sum of field with where conditions with Eloquent

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related