CODEWITHSUNDEEP
mysqlsqllaravel-4eloquent
John Dorean
John Dorean

Reputation: 3874

Getting the sum of field with where conditions with Eloquent

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.

Upvotes: 0

Views: 629

Answers (2)

Fractaliste
Fractaliste

Reputation: 5957

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

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

Upvotes: 1

rmobis
rmobis

Reputation: 27021

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

Upvotes: 2

Related Questions