How to get sum of specific status or id and joining 3 table in laravel

Question

my table

table1
|table1_id | name |
| 1         john |
| 2         dave |
| 3         carl |

table2
|table2_id| table1_id| type  |status
| 1       | 1        | shoes |paid
| 2       | 1        | bag   |paid
| 3       | 2        | bag   |paid
| 4       | 2        | shoes   |unpaid

table3
|table3_id|table2_id|item  |amount|
|1        |  1      |nike   |1000  |
|2        |  1      |adidas |2000  |
|3        |  2      |lv     |1000  |
|4        |  3      |lv1    |2000  |
|5        |  3      |lv     |1000  |
|6        |  4      |adidas |1000  |

this is the result I want to display john --- total paid shoes and bag dave <-- total of bag,'shoes is unpaid so 1000 is not added to total'

|name|total |
|john|4000  |
|dave|3000  | 
|carl|0  |

this is my controller it gives me an error the total is same in all name

   public function index()
        {
            $fetch = DB::table('table1')
            ->leftjoin('table2','table1.table1_id','=','table2.table1_id')      
            ->leftjoin('table3','table2.table2_id','=','table3.table2_id')
            ->select('table1.*','table2.*',DB::raw('(select sum(table3.amount) from table3
            join table2 on table2.table2_id = table3.table2_id 
             where table.status = "paid") as used'))
            ->groupBy('table1.table1_id')
              
            ->get();
            return $fetch;
        }

user3532758 · Accepted Answer · 2020-09-04T08:00:24.720

0

You could look to join sub queries, that certainly would be one approach. Here is an example:

$sales = DB::table('table3')
            ->select('table2_id', DB::raw('sum(amount) as cat_sale_amount'))
            ->groupBy('table2_id');

$table2Sales = DB::table('table2')
            ->select('table1_id', DB::raw('sum(cat_sale_amount) as total_sale_amount'))
            ->joinSub($sales, 'sales', function($join){
                $join->on('table2.id', '=', 'sales.table2_id');
            })
            ->where('status', 'paid')
            ->groupBy('table1_id');

$userTotalSales = DB::table('table1')
                  ->select('name', 'total_sale_amount as total')
                  ->leftJoinSub($table2Sales, 'table2Sales', function($join){
                     $join->on('table1.id', '=', 'table2Sales.table1_id');
                  })->get();

The first subquery would give the total sales amount based on type from table1 achieved by grouping table2_id and using a sum operation on the amount. So the result would list user_id/table2_id and the amount per type.

The second subquery would give the total sales for individual customers/user by joining the first subquery. Group by cutomer/user/table1_id to get a sum on the amount from the first query to get the total for a individual customers.

Join the second to the users table and you should have the desired result.

edited Sep 04 '20 at 08:00

answered Sep 04 '20 at 04:01

user3532758

2,221
1
12
17

I got an error SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'amount' in field list is ambiguous – azakura Sep 04 '20 at 05:18
and also how I only get the sum of paid only – azakura Sep 04 '20 at 05:29
@azakura affix table/subquery alias name before amount or use different fields. Edited question to have different aliases. To get paid only, since it's from table2, include an additional where clause to check status. Edited answer to include that as well. – user3532758 Sep 04 '20 at 06:54
I got error Column not found: 1054 Unknown column 'status' in 'where clause' – azakura Sep 04 '20 at 07:28
Oh I edited the wrong subquery. It should be in the second one. – user3532758 Sep 04 '20 at 07:28
Fixed. Pls check. – user3532758 Sep 04 '20 at 07:29
It works !! but how can i display even if table1 has no transaction i want to display it and total is 0 because it has no transaction – azakura Sep 04 '20 at 07:32
1

But change join to `leftJoinSub` and see if it works – user3532758 Sep 04 '20 at 07:54
Glad I could help. – user3532758 Sep 04 '20 at 07:58

How to get sum of specific status or id and joining 3 table in laravel

1 Answers1