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)
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().
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With