Query scope based on multiple conditions

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)) {

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

            }

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

                ($q->whereNotNull('discount_id')
                    ->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.

DavidPetrov

6 years ago

Best Answer

Level 2

If I understood correctly - if there's a discount_id, you want discounted price, if not - the normal price. If you didn't know, there's an inbound whereBetween method for laravel's query builder, which makes it a bit easier ^^

$query->whereHas('price', function($q) use ($min, $max){
    $q->where(function($q) use ($min, $max){
        $q->has('discount')->whereBetween('category_discounted_price', [$min, $max])
    })->orWhere(function($q) use ($min, $max){
        $q->doesntHave('discount')->whereBetween('price', [$min, $max]);
    });
})

P.S. Not tested, but should produce the needed query.

2 likes

LiamMcArthur

6 years ago

Level 1

@DAVIDPETROV - That's perfect! With a bit of tinkering, we managed to get it fully working with this:

public function scopePriceBetween($query, $min, $max)
    {
        $query->whereHas('price', function ($q) use ($min, $max) {
            if ((isset($min) && !empty($min)) || ((isset($max) && !empty($max)))) {
                $q->where(function ($q) use ($min, $max) {
                    $q->where('discounted_price', '>', 0)
                        ->whereBetween('category_discounted_price', [$min, $max])
                        ->orWhereHas('discount')->whereBetween('category_discounted_price', [$min, $max]);
                })->orWhere(function ($q) use ($min, $max) {
                    $q->doesntHave('discount')->whereBetween('price', [$min, $max]);
                });
            }
        });
    }

Many thanks for your help with this.

Robstar

6 years ago

Level 50

@LIAMMCARTHUR - You could refactor that further to use the conditional when Eloqurnt clause, see https://laravel.com/docs/5.8/queries#conditional-clauses

Please or to participate in this conversation.