Laravel order by column with the condition of other column

Question

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?

Can you share some data, and table definition

SelVazi
– SelVazi

2023-02-02 09:06:31 +00:00
Commented Feb 2, 2023 at 9:06 — SelVazi
– SelVazi, Commented Feb 2, 2023 at 9:06

user1191247 · Accepted Answer · 2023-02-02 09:35:31Z

1

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

answered Feb 2, 2023 at 9:35

user1191247

13k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel order by column with the condition of other column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related