I want to access the products with total amount-CodePudding

I have 2 tables. One is product(id, name). another is In(id, amount, products_id). I am trying to get product name with total amount. Example: Aerobat-34 God of war-3 Rs537-15

I Joined the tables. Now output is

[{"name":"Aerobat","amount":"10"},{"name":"Aerobat","amount":null},{"name":"Aerobat","amount":null},{"name":"Aerobat","amount":null},{"name":"Aerobat","amount":"2"},{"name":"Aerobat","amount":"56"},{"name":"Aerobat","amount":"56"},{"name":"god of war","amount":"7"},{"name":"god of war","amount":"23"},{"name":"Rs537","amount":null},{"name":"Rs537","amount":null},{"name":"Rs537","amount":null}]

public function index()
    {
        $product = DB::table('Products')
            ->join('ins', 'products.id', '=', 'ins.products_id')
            ->select('Products.name', 'ins.amount')
            ->get();
        echo $product;
    }

How to find the expected result?

CodePudding user response：

you could get it like this

    public function index()
    {
        $product = DB::table('Products')
            ->join('ins', 'products.id', '=', 'ins.products_id')
            ->select('Products.name', 'ins.amount',DB::raw('sum(ins.amount) as total'))
            ->get();
        echo $product;
    }

CodePudding user response：

Try this

public function index()
{
    $product = DB::table('products')
        ->join('ins', 'products.id', '=', 'ins.products_id')
        ->select('products.name', DB::raw('sum(ins.amount) as total_amount'))
        ->groupBy('ins.products_id')
        ->get();

    echo $product;
}