CODEWITHSUNDEEP
phpmysqllaravel
Dyasis
Dyasis

Reputation: 61

Laravel5.7: Get sum() for current month using model returned in view

I am trying to get the sum of a field 'time_spent' for the current month, so when it's the next month it will start over again. I currently have in my model:

 public function transactions()
{
    return Transaction::groupBy('created_at')
    ->selectRaw('sum(time_spent) as sum')
    ->pluck('sum');
}

Then in my blade view I am calling using:

$info->transactions()->sum()

This does return the sum of time_spent from the DB, but it doesn't care about the created_at, I am assuming its not using it, My column for created_at is timestamp()

Upvotes: 0

Views: 1137

Answers (2)

Lucas Ferreira
Lucas Ferreira

Reputation: 888

You need to use the methods whereMonth and whereYear to filter the transactions by the current month and year. After that, you can use the database's sum method to sum all time_spent.

Your method should look like this:

public function transactions($year, $month)
{
    return Transaction::whereYear('created_at', $year)
        ->whereMonth('created_at', $month)
        ->sum('time_spent');
}

This will run a query similar to this (it depends on which database you're using):

SELECT SUM(transactions.time_spent) FROM transactions WHERE YEAR(transactions.created_at) = 2018 AND MONTH(transactions.created_at) = 11;

To get the current month, you should call the method like this:

$month = date('m');
$year = date('Y');

$transactions = $info->transactions($month, $year);

Upvotes: 1

Felippe Duarte
Felippe Duarte

Reputation: 15131

created_at is a timestamp from year to the second (YYYY-MM-DD HH:mm:ss). If you are trying to sum by some month, you need to identify the year and month to do this. So, use this instead:

public function transactions($year, $month)
{
    return Transaction::selectRaw('sum(time_spent) as sum')
        ->where('YEAR(created_at)', '=', $year)
        ->where('MONTH(created_at)', '=', $month)
        ->pluck('sum');
}

$year = 2018; //change in application
$month = 11; //change in application
$info->transactions($year, $month)->sum();

Upvotes: 0

Related Questions