Struggling with a query that subtracts sum of relationship column from another relationship column if that relationship exists

I have Product, ProductStock and CartProducts models.

I have been trying to figure out how to build a query which:

Gets all products where product_stock.stock >= 1 ,
- where product_stock.stock - sum(cart_products.count) > 0 (cart_products where cart_products .product_code = products.code)
- or where doesn't have any cart_products relation, but still has product_stock.stock >= 1

This only gets me products which have relation with CartProduct:

Product::whereHas('stock', fn ($q) => $q->where('stock', '>=', 1))
    ->withSum('stock', 'stock')
    ->withSum('cartProducts', 'count')
    ->havingRaw('stock_sum_stock - cart_products_sum_count > 0')
    ->get();

This frankenstein only returns products which don't have any relation to CartProduct:

Product::whereHas('stock', fn ($q) => $q->where('stock', '>=', 1))
    ->where(function (Builder $q) {
        return $q->where(function (Builder $q) {
            return $q->withSum('stock', 'stock')
                ->withSum('cartProducts', 'count')
                ->havingRaw('stock_sum_stock - cart_products_sum_count > 0');
        })->orWhereDoesntHave('cartProducts');
    })->get();

Product:

protected $table = 'products';

public function stock(): HasOne
{
    return $this->hasOne(ProductStock::class, 'product_code', 'code');
}

public function cartProducts(): HasMany
{
    return $this->hasMany(CartProduct::class, 'product_code', 'code');
}

ProductStock:

protected $table = 'product_stock';

public function product(): BelongsTo
{
    return $this->belongsTo(Product::class, 'product_code', 'code');
}

CartProduct:

protected $table = 'cart_products';

public function product(): HasOne
{
    return $this->hasOne(Product::class, 'code', 'product_code')->withTrashed();
}

AungHtetPaing__

3 years ago

Level 22

@r123 I think you just need to use whereHas and orWhereHas. I hope I didn't misunderstand your question. Your table naming make me confuse.

Product::query()
	->whereHas('cartProducts', function($query) {
		$query->whereRaw('product_stock.stock - SUM(cart_products.count) > 0')
	})
	->orWhereHas('stock', function($query) {
		$query->where('stock', '>=', 1);
	})

or may be use when clause

$hasCart = Product::has('cartProducts')->get();

Product::query()
	->when($hasCart, function($query) {
		$query->whereHas('cartProducts', function($query) {
			$query->whereRaw('product_stock.stock - SUM(cart_products.count) > 0')
		})	
	}, function($query) {
		$query->WhereHas('stock', function($query) {
			$query->where('stock', '>=', 1);
		})
	})

Please or to participate in this conversation.