How to write eloquent query by joining different tables

I have 4 tables.
1)materials
2)advertisements
3)offerrequests
4)transactions

I need to write a query that will select the sum of the weight of transactions per material involved in the transaction

The query I tried is as follows,

Transaction::->select(DB::raw("IF(transactions.is_sell = '1', advertisements.weight, offerrequests.weight) as weight"),DB::raw("IF(transactions.is_sell = '1', advertisements.material_id, offerrequests.material_id) as material_id"))
            ->leftJoin('advertisements', function ($join) {
                $join->on('advertisements.id', '=', 'transactions.post_id');
            })
            ->leftJoin('offerrequests', function ($join) {
                $join->on('offerrequests.id', '=', 'transactions.post_id');
            })
            ->leftJoin('materials', function ($join) {
                $join->on('materials.id', '=', 'material_id');
            })
           ->groupBy('material_id')->get();

Transactions table

Advertisements table

Offerrequests table

Materials table

My expected result will be like this

You can try the query like:

$results = DB::table('transactions as t')
            ->leftJoin('advertisements as a', function ($join) {
                $join->on('a.id', '=', 't.post_id')
                     ->where('t.is_sell', '=', 1);
            })
            ->leftJoin('offerrequests as o', function ($join) {
                $join->on('o.id', '=', 't.post_id')
                     ->where('t.is_sell', '=', 0);
            })
            ->join('materials as m', function ($join) {
                $join->on('m.id', '=', DB::raw('COALESCE(a.material_id, o.material_id)'));
            })
            ->groupBy('m.id')
            ->selectRaw('SUM(CASE WHEN t.is_sell = 1 THEN a.weight ELSE o.weight END) AS weight')
            ->select('m.name AS material')
            ->get();

and print the weight and material name as:

foreach ($results as $result) {
    echo $result->weight . "\t" . $result->material . "\n";
}