Laravel order by column with the condition of other column-CodePudding

I'm working on an e-commerce project and on the client side we have two 'sort by option' Cheapest and Expensive and for order I'm using the code below and it's ok!


$query->select([
     '*',
     DB::raw(
     'IF(`discountPrice` IS NOT NULL, `discountPrice`, `price`) `sortPrice`',
     ),
     ])->orderBy('sortPrice', 'ASC')->paginate(15);

but I don't want the products with zero quantity to show up on the first if their price is high or low. they should be constantly on the bottom of the pagination if their quantity is 0;

any solutions?

CodePudding user response：

add another condition in the orderBy clause to make sure the products with zero quantity are always on the bottom. You can do this by ordering first by the quantity in descending order and then by the price in ascending or descending order, depending on the sort option selected.

$query->select([
     '*',
     DB::raw(
     'IF(`discountPrice` IS NOT NULL, `discountPrice`, `price`) `sortPrice`',
     ),
     ])->orderBy('quantity', 'DESC')
       ->orderBy('sortPrice', 'ASC')
       ->paginate(15);

For "Cheapest" option:

$query->select([
     '*',
     DB::raw(
     'IF(`discountPrice` IS NOT NULL, `discountPrice`, `price`) `sortPrice`',
     ),
     ])->orderBy('quantity', 'DESC')
       ->orderBy('sortPrice', 'ASC')
       ->paginate(15);

For "Expensive" option:

$query->select([
     '*',
     DB::raw(
     'IF(`discountPrice` IS NOT NULL, `discountPrice`, `price`) `sortPrice`',
     ),
     ])->orderBy('quantity', 'DESC')
       ->orderBy('sortPrice', 'DESC')
       ->paginate(15);

CodePudding user response：

To push all the 0 quantity products to the bottom of your result set you need to turn your quantity into a 1 or 0 (in stock / out of stock) for ordering as you do not want to order by quantity descending, as that would negate the later sorting (ASC or DESC) by price -

$query->select([
    '*',
    DB::raw(
        'IF(`discountPrice` IS NOT NULL, `discountPrice`, `price`) `sortPrice`',
    ),
    ])->orderByRaw('IF(quantity = 0, 0, 1) DESC')
      ->orderBy('sortPrice', 'ASC')
      ->paginate(15);