CODEWITHSUNDEEP
mysqllaravelquery-builderlaravel-query-builder
cabs
cabs

Reputation: 710

How to combine two queries from the same table using Laravel query builder

From this sales table, I created 2 queries.

Sale::where(branch_id', 1)->select('*', DB::raw('SUM(amount) as amount))->groupBy('date')->get()

Sale::where(branch_id', 2)->select('*', DB::raw('SUM(amount) as amount))->groupBy('date')->get()

I want to combine the two queries...

to achieve this

enter image description here

Here's my sales table

actual sales table

Upvotes: 2

Views: 723

Answers (3)

Samsquanch
Samsquanch

Reputation: 9146

I think what you're looking for in raw SQL is this:

SELECT 
    `date`,
    SUM(IF(branch_id = 1, amount, 0)) as 'Branch 1',
    SUM(IF(branch_id = 2, amount, 0)) as 'Branch 2',
    SUM(amount) as 'Total'
FROM
    branches
WHERE
    branch_id IN (1,2)
GROUP BY
    `date`;

Unfortunately you can't do most of this with querybuilder, so you'll need to do it with DB::raw(), something like this:

Sale::whereIn(branch_id', [1,2])->
        select('date',
                DB::raw("
                    SUM(IF(branch_id = 1, amount, 0)) as 'Branch 1',
                    SUM(IF(branch_id = 2, amount, 0)) as 'Branch 2',
                    SUM(amount) as amount
                    ")
                )->groupBy('date')->get();

I haven't tested this in Laravel so it may be slightly off, but it should get you close.

Sample fiddle.

Upvotes: 3

Akshay Deshmukh
Akshay Deshmukh

Reputation: 1292

Try This.

DB::table('Sale')->whereIn('branch_id',[1,'2])->get();

Upvotes: 0

Joel Hinz
Joel Hinz

Reputation: 25374

Since they're both identical except for an integer, you could simply do this:

Sale::whereIn('branch_id', [1, 2])->...

... and let the rest of the query stay the same.

Upvotes: 2

Related Questions