Jump to content

Help with a ordering


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

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.

Link to post
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 post
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 post
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 post
Share on other sites

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.

×
×
  • 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.