CODEWITHSUNDEEP
databaselaraveleloquent
Konstantinos
Konstantinos

Reputation: 1

Laravel Eloquent - Find average for a group of days in a month

i have 2 colums. One is datetime (imeromina_metrisis) and the other has integers (apotelesma). What i am trying to do is to get the average integer number for each day so that i can send it to a chart. Do you have any idea how can i do this?

This is my table here

Upvotes: 0

Views: 7811

Answers (3)

Gauravbhai Daxini
Gauravbhai Daxini

Reputation: 2190

Ok I found the solution, by using Eloquent + Fluent, to be able to use my relationships. This is my code to get a collection of all my Articles + User + Rating average :

$articles = Articles::where('state', '=', '1')
->with('user')
->leftJoin('ratings', 'ratings.article_id', '=', 'articles.id')
->select(array('articles.*',
    DB::raw('AVG(rating) as ratings_average')
    ))
->groupBy('id')
->orderBy('ratings_average', 'DESC')
->get();

Upvotes: 0

xmhafiz
xmhafiz

Reputation: 3538

We need to get DATE format first instead of using DATETIME since you need data for each day. Try this query

$data = DB::table('table_name')
        ->select(DB::raw('avg(apotelesma) as count, DATE(imeromina_metrisis) day'))
        ->groupBy('day')
        ->get();

refer how to select raw expression here

Upvotes: 2

Josh
Josh

Reputation: 3288

Probably something like this.

$avg = Model::where('imeromina_metreisis', $carbon)->avg('apotelesma');

See:
https://laravel.com/docs/5.1/queries#aggregates

Just be aware of your date column. $carbon is a variable representing the Carbon date and time object that Laravel uses extensively. If the average is for an entire day, but you're using datetime (which includes hours/minutes/seconds), you will want to do something like this.

$carbon = Carbon::now()->startOfDate();
$avg = Model::where('imeromina_metreisis', '>=', $carbon)
    ->where('imeromina_metreisis', '<=', $carbon->copy()->endOfDay())
    ->avg('apotelesma');

And yes, those are built-in Carbon methods. It really is a great library.

Edit: More documentation on Carbon. I think you want arbitrary day ranges.
http://carbon.nesbot.com/docs/

Upvotes: 0

Related Questions