Lazarus99 Posted March 12, 2021 Share Posted March 12, 2021 (edited) Hi, all I have the below snippet of code, But specifically about the l.buyout_price bit at the end. I want to have 0 values at the end of the ordering. Does anyone have any idea on how to rewrite the below code to achieve this. Example or ordering I would like l.buyout_price to be in this order: 1,12,200,0 instead of 0,1,12,200 case 'price_asc': $select->order(new Expr("IF(l.listing_type='auction', IF(max(b.amount) is null, l.start_price, max(b.amount)), l.buyout_price) ASC")); break; Edited March 12, 2021 by Lazarus99 Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/ Share on other sites More sharing options...
Barand Posted March 12, 2021 Share Posted March 12, 2021 (edited) 1 hour ago, Lazarus99 said: Does anyone have any idea on how to rewrite the below code to achieve this. Do you mean something like this example? mysql> select * from test_a; +----+------+-------+ | id | name | price | +----+------+-------+ | 1 | A | 12 | | 2 | B | 200 | | 3 | C | 1 | | 4 | D | 0 | | 5 | E | 5 | +----+------+-------+ mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY price = 0, price; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ (The value of "price = 0" will be either 1 or 0 depending on whether it is true or false) Edited March 12, 2021 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585045 Share on other sites More sharing options...
Lazarus99 Posted March 12, 2021 Author Share Posted March 12, 2021 (edited) Hi, Thanks for replying! I have tried using l.buyout_price = 0 in the above snippet but it give weird ordering results Original case 'price_asc': $select->order(new Expr("IF(l.listing_type='auction', IF(max(b.amount) is null, l.start_price, max(b.amount)), l.buyout_price) ASC")); break; Tried case 'price_asc': $select->order(new Expr("IF(l.listing_type='auction', IF(max(b.amount) is null, l.start_price, max(b.amount)), l.buyout_price = 0 ) ASC")); break; Edited March 12, 2021 by Lazarus99 Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585046 Share on other sites More sharing options...
Barand Posted March 12, 2021 Share Posted March 12, 2021 I've no idea what that code of yours does. All I could do was show you how to use SQL TO order by a column ASC but with zero values last. How you accomplish that with your classes is down to you I'm afraid. Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585047 Share on other sites More sharing options...
Lazarus99 Posted March 12, 2021 Author Share Posted March 12, 2021 It's OK, I posted it on reddit and a guy came back with this and it worked a treat. case 'price_asc': $select->order(new Expr("IF(l.listing_type='auction', IF(max(b.amount) is null, l.start_price, max(b.amount)), CASE l.buyout_price WHEN 0 THEN ~0 ELSE l.buyout_price END) ASC")); break; Cheers Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585048 Share on other sites More sharing options...
Barand Posted March 12, 2021 Share Posted March 12, 2021 (edited) Strange, because if I sort by that expression, I get mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN -0 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 4 | D | 0 | | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | +----+------+-------+ However, on a similar tack, mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN 999999999 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ EDIT.... My apologies, I read it as a minus sign and not a bitwise inversion. Your version works fine. mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN ~0 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ ( ~0 evaluates to 18,446,744,073,709,551,615 ) Edited March 12, 2021 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585049 Share on other sites More sharing options...
Lazarus99 Posted March 13, 2021 Author Share Posted March 13, 2021 Thanks for checking that out! Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585050 Share on other sites More sharing options...
Barand Posted March 13, 2021 Share Posted March 13, 2021 15 hours ago, Lazarus99 said: Tried case 'price_asc': $select->order(new Expr("IF(l.listing_type='auction', IF(max(b.amount) is null, l.start_price, max(b.amount)), l.buyout_price = 0 ) ASC")); break; Looks like that code is giving ORDER BY l.buyout_price = 0 instead of the code I used... (which would explain the "weird ordering") ORDER BY l.buyout_price = 0, price (The ones that aren't zero need sorting by price) Quote Link to comment https://forums.phpfreaks.com/topic/312282-help-with-a-ordering/#findComment-1585057 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.