Laravel get sum of related table's columns with eloquent

Question

I'm having some trouble calculating the price of my carts with eloquent, here are my tables:

cart_products:
- cart_id
- product_id
- quantity

products:
- price

One cart can have multiple cart_products, and each cart_products have one product associated

I'm making the request from the Cart Model, I'm trying to get the total price of the cart (cart_products.quantity * products.price).

Here is my query:

Cart::select('cart.*', \DB::raw('IFNULL(SUM(products.price*cart_products.quantity), 0) AS cart_price'))
        ->leftJoin('cart_products', 'cart.id', '=', 'cart_products.cart_id')
        ->join('products', 'cart_products.product_id', '=', 'products.id');

When I'm doing that, I do get the expected result but all the carts that doesn't contains product are excluded, I would like them to be included.

How could I include them ? Or is there a better way to do it (I saw withCount method but I couldn't make it work properly) ?

Answer 1

Another way would be to setup a virtual relation in your cart model and calculate your cart price like

class Cart extends Model
{
    public function price()
    {
        return $this->hasOne(CartProducts::class, 'cart_id')
            ->join('products as p', 'product_id', '=', 'p.id')
            ->groupBy('cart_id')
            ->selectRaw('cart_id,IFNULL(SUM(products.price*cart_products.quantity), 0) as cart_price');
    }
}

To get price data for your carts your can query as

Cart::with('price')->get()->sortByDesc('price.cart_price');

Answer 2

I finally managed to do it another way using raw SQL:

        Cart::select('cart.*', \DB::raw('(SELECT IFNULL(SUM(products.price*cart_products.quantity), 0) from cart_products join products on products.id = cart_products.product_id where cart_products.cart_id = cart.id) AS cart_price'));

Thanks to you all for your help !