Jump to content

Help with a ordering


Lazarus99
 Share

Recommended Posts

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 by Lazarus99
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by Lazarus99
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.