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

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;
        }

Answer

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.