CODEWITHSUNDEEP
phplaravellaravel-5eloquent
Paul Diamant
Paul Diamant

Reputation: 189

Laravel eloquent where and orWhere issue fetching correct data

I have the following query builder which generates this query:

My problem lies within the where condition in the sale and stock options, when using the min and max price filtering. My URL looks like this:

category/men-clothes?sale=no_sale&min_price=10&max_price=10000&per_page=8

sale=no_sale, means the query performs the where 'sale_price', '=', 0, hence this in my SQL query: 

and `sale_price` = ? order by `id` desc

Yet, it still fetches products whose sale_price is bigger than 0, but when I switch to on_sale, only products with sale_price bigger than 0 are fetched. So the problem is only when selecting products that have no active sale. So goes for the on_stock and no_stock where, it just doesn't fetch the right products. I know the builder is quite big, but I haven't been able to figure why it is not working properly. It only works properly after I remove the min_price and max_price filtering completely SO it has to be somewhere in the where and orWhere in the price filtering that causes those issues.

.

select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `id` = ?) and (`has_variants` = ? and `price` >= ?) or (`has_variants` = ? and `min_price` != ? and `min_price` >= ?) and (`has_variants` = ? and `price` <= ?) or (`has_variants` = ? and `max_price` != ? and `max_price` <= ?) and `sale_price` = ? order by `id` desc

.

$products = Product::whereHas('categories', function ($query) use ($category) {
            $query->where('id', '=', $category->id);
        })->when(count($brand_list) > 0, function ($query) use ($brand_list) {
            $query->whereHas('brand', function ($query) use ($brand_list) {
                $query->whereIn('slug', $brand_list);
            });
        })->when($minPrice, function ($query) use ($data) {
            $query->where([
                    ['has_variants', '=', 0],
                    ['price', '>=', $data['active_filters']['min_price']],
                ])
                ->orWhere([
                    ['has_variants', '=', 1],
                    ['min_price', '!=', 0],
                    ['min_price', '>=', $data['active_filters']['min_price']],
                ]);
        })->when($maxPrice, function ($query) use ($data) {
            $query->where([
                ['has_variants', '=', 0],
                ['price', '<=', $data['active_filters']['max_price']],
            ])
                ->orWhere([
                    ['has_variants', '=', 1],
                    ['max_price', '!=', 0],
                    ['max_price', '<=', $data['active_filters']['max_price']],
                ]);
        })->when($orderPrice, function ($query) use ($orderPrice) {
            $query->orderBy('price', $orderPrice);
        })->when(!$orderPrice, function ($query) {
            $query->orderBy('id', 'desc');
        })->when($stockOrder, function ($query) use ($stockOrder) {
            if($stockOrder == 'in_stock') {
                $query->where('quantity', '>', 0);
            } else if($stockOrder == 'no_stock') {
                $query->where('quantity', '=', 0);
            }
        })->when($saleOrder, function ($query) use ($saleOrder) {
            if($saleOrder == 'on_sale') {
                $now = time();
                $query->where([
                    ['sale_price', '>', 0],
                    ['sale_start', '<', $now],
                    ['sale_end', '>', $now],
                ])->orWhere([
                    ['sale_price', '>', 0],
                    ['sale_start', '=', 0],
                    ['sale_end', '=', 0],
                ]);
            } else if($saleOrder == 'no_sale') {
                $query->where('sale_price', '=', 0);
            }
        })->when($featuredOrder, function ($query) use ($featuredOrder) {
            if($featuredOrder == 'featured') {
                $query->where('featured', '=', 1);
            } else if($featuredOrder == 'not_featured') {
                $query->where('featured', '=', 0);
            }
        })->when(count($activeColors) > 0, function ($query) use ($activeColors) {
            $query->whereHas('colors', function ($query) use ($activeColors) {
                $query->whereIn('value', $activeColors);
            });
        })->when(count($activeSizes) > 0, function ($query) use ($activeSizes) {
            $query->whereHas('sizes', function ($query) use ($activeSizes) {
                $query->whereIn('value', $activeSizes);
            });
        })->with(['colors', 'sizes', 'reviewsCount'])->get();

Upvotes: 1

Views: 1830

Answers (1)

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111839

I think the problem here are orWhere. When you use orWhere you should probably always wrap construction with additional closure. Let's look at:

->when($minPrice, function ($query) use ($data) {
            $query->where([
                    ['has_variants', '=', 0],
                    ['price', '>=', $data['active_filters']['min_price']],
                ])
                ->orWhere([
                    ['has_variants', '=', 1],
                    ['min_price', '!=', 0],
                    ['min_price', '>=', $data['active_filters']['min_price']],
                ]);
        })

This part should probably look like this:

->when($minPrice, function ($query) use ($data) {
      $query->where(function($query) use ($data) {
            $query->where([
                    ['has_variants', '=', 0],
                    ['price', '>=', $data['active_filters']['min_price']],
                ])
                ->orWhere([
                    ['has_variants', '=', 1],
                    ['min_price', '!=', 0],
                    ['min_price', '>=', $data['active_filters']['min_price']],
                ]);
          });
        })

As you see above the whole where .. orWhere was wrapped into additional closure.

The reason for this is quite obvious. If you don't have additional closure you can produce query like this

WHERE A and B or C and D or E

and usually it should be like this:

WHERE A and (B or C) and (D or E)

so when you use closures they will add extra parenthesis to query to make it work.

The same way you should obviously wrap all the others where ... orWhere constructions to make it work as you expect

Upvotes: 3

Related Questions