Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Query Builder - sum() method issue

I'm new to laravel and I have some issues with the query builder. The query I would like to build is this one:

SELECT SUM(transactions.amount)
FROM transactions
JOIN categories
ON transactions.category_id == categories.id 
WHERE categories.kind == "1"

I tried building this but it isn't working and I can't figure out where I am wrong.

$purchases = DB::table('transactions')->sum('transactions.amount')
    ->join('categories', 'transactions.category_id', '=', 'categories.id')
    ->where('categories.kind', '=', 1)
    ->select('transactions.amount')
    ->get();

I would like to get all the transactions that have the attribute "kind" equal to 1 and save it in a variable. Here's the db structure:

transactions(id, name, amount, category_id)

categories(id, name, kind)

like image 891
burn15 Avatar asked May 24 '15 15:05

burn15


People also ask

How do I sum in laravel query builder?

We mostly required to get sum of amount, salary etc in laravel. We can also get sum of column using mysql SUM(). We have two way to get sum of column value. first we can use laravel sum() of query builder and another one we can use with directly with select statement using DB::raw().

Which is better eloquent or query builder in laravel?

Eloquent ORM is best suited working with fewer data in a particular table. On the other side, query builder takes less time to handle numerous data whether in one or more tables faster than Eloquent ORM. In my case, I use ELoquent ORM in an application with tables that will hold less than 17500 entries.

What is the use of toArray () in laravel?

In many cases, the toArray() method on Model the class is called under the hood to serialize your model. To customize for all your models what should get returned for the translatable attributes you could wrap the Spatie\Translatable\HasTranslations trait into a custom trait and overrides the toArray() method.


1 Answers

You don't need to use select() or get() when using the aggregate method as sum:

$purchases = DB::table('transactions')
    ->join('categories', 'transactions.category_id', '=', 'categories.id')
    ->where('categories.kind', '=', 1)
    ->sum('transactions.amount');

Read more: http://laravel.com/docs/5.0/queries#aggregates

like image 107
Limon Monte Avatar answered Oct 13 '22 00:10

Limon Monte