Help with a ordering

[Lazarus99]

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

[Barand]

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

[Lazarus99]

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

[Barand]

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.

[Lazarus99]

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

[Barand]

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

[Lazarus99]

Thanks for checking that out!

[Barand]

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)