CODEWITHSUNDEEP
mysqllaraveleloquentquery-builder
sandip kakade
sandip kakade

Reputation: 1356

How to convert custom query to laravel 5?

I am fetching order_id, created_at date, transaction_amount from transaction table. I got some records with order_id, its created_at date and transaction_amount. Now I want the sum of transaction_amount column. I tried following query on db directly which working well, but I am unable to write same query in laravel.

select sum(transaction_amount) from (
select order_id, max(created_at), transaction_amount
from transactions
WHERE user_id =100 AND accounting_type = "Debit"
group by order_id
limit 500 ) as transactions

I tried this way but still not working

$sql = "select sum(transaction_amount) from 
                ('select order_id, max(created_at), transaction_amount
                from transactions
                WHERE user_id =100 
                group by order_id
                limit 500') as transactions";
        $result = \DB::select( \DB::raw($sql));

Upvotes: 1

Views: 251

Answers (2)

Dharma Saputra
Dharma Saputra

Reputation: 1674

First, let break down your query: Main query

SELECT
SUM( transaction_amount )
FROM
    (...) AS transactions

This just used for summarize. And your sub query:

        SELECT
            order_id,
            MAX( created_at ),
            transaction_amount
        FROM
            transactions
        WHERE
            user_id = 100
            AND accounting_type = "Debit"
        GROUP BY
            order_id LIMIT 500

For the sub query, the Laravel query builder should be:

use DB;

$result = DB::table('table')
->select(DB::raw("order_id, MAX(created_at), transaction_amount"))
->where("user_id", "=", 100)
->where("accounting_type", "=", "Debit")
->groupBy("order_id")
->limit(500)
->get(); // this will return Laravel Collection (Illuminate\Support\Collection)

Laravel Collection has sum method so you can call it

$result->sum('transaction_amount');

For more information please read this and this Laravel documentation.

Upvotes: 3

Kévin Bibollet
Kévin Bibollet

Reputation: 3623

I think your $sql variable has to contain only the sub query from your FROM.

The table() method of the Laravel QueryBuilder is the "equivalent" of FROM statement in SQL, it is here that you put your sub query.

Try

$sql = 'select order_id, max(created_at), transaction_amount
            from transactions
            WHERE user_id =100 
            group by order_id
            limit 500';

$result = \DB::table(\DB::raw($sql))->sum('transaction_amount');

And instead, if you want a parrallel with the select() method you tried to use:

$result = \DB::table(\DB::raw($sql))->select(\DB::raw('sum(transaction_amount) as sum'))->get();

Upvotes: 0

Related Questions