Laravel5.7：使用视图中返回的模型获取当月的sum（）

Question

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()

Answer 1

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