I want to access the products with total amount

Question

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?

score 0 · Answer 1 · answered Feb 12 '22 at 19:51

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

score 0 · Answer 2 · answered Feb 12 '22 at 19:58

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

score 0 · Answer 3 · answered Feb 13 '22 at 07:25

you should use Model relations first, create a function in the Product model that is called ins()

public function ins()
{
    return $this->hasMany('App\Models\Ins', 'product_id');
}

and also you can declare a virtual column for product that executes the total amount of this product. Notice, you should set name with "get" as prefix and "Attribute" as postfix

public function getTotalAmountAttribute()
{
    $totalAmount=0;
    foreach($this->ins as $item){
         $totalAmount+=$item->amount??0
    }
    return $totalAmount;
}

and finally, you can use both fields like name & total amount

I want to access the products with total amount

3 Answers3