4 months ago

Query scope based on multiple conditions

Posted 4 months ago by LiamMcArthur

I have a price filter query scope in our product entity that checks if a products price is between the two values inputted. Since we've added discounts to our website, we've been unable to get the query scope working correctly so that it picks either the discounted price or the normal price. Here's what I've written so far:

public function scopePriceBetween($query, $min, $max)
        $query->whereHas('price', function ($q) use ($min, $max) {

            if (isset($min) && !empty($min)) {

                    ->where('category_discounted_price', '>', $min)) or
                    $q->where('price', '>=', $min);


            if (isset($max) && !empty($max)) {

                    ->where('category_discounted_price', '<', $max)) or
                    $q->where('price', '<=', $max);


This scope seems to work on our listing pages that contain discounted items, but no longer works for product listings that contain products without discounts. Is it possible to write such complex conditions in query scopes? Essentially, I'm trying to detect if a products price has a discount ID, if it does - query the category_discounted_price, else - query the normal price.

Please sign in or create an account to participate in this conversation.