I have an INT 'weight' field for some nodes that runs -20 through to +20 and want to order the nodes so that -20 is at the top, but, as far as I can tell I'm getting them in string order. I've tried

  $query->addField('w', 'field_weight_value', 'weight');

  $result = $query
    ->orderBy('CAST(w.field_weight_value AS SIGNED)', 'ASC')

But that makes no difference, I still get 0 appearing above -10. I'm using db_select so that I can use the PagerDefault extension. I could just change the 'weight' field to be all positive, but that seems like a fail!

I think the problem might be it's always evaluated as a string even though the table column is type INT, if I perform the same query as db_query, e.g.

  $result = db_query('SELECT n.nid, n.title, w.field_weight_value 
    FROM {node} n 
    ORDER BY w.field_weight_value ASC', array(':nid' => $nid));

I get the results I expect.

  • Shouldn't that be AS SIGNED INTEGER? – Clive Mar 10 '14 at 17:08
  • Shouldn't that be sorted by ASCif you want the smallest weights at the top? – berliner Mar 10 '14 at 17:56
  • @Clive I believe the SIGNED alone is correct - see the MYSQL docs - dev.mysql.com/doc/refman/5.0/en/… – bloke_zero Mar 11 '14 at 10:09
  • @berliner - correct -edited for that. – bloke_zero Mar 11 '14 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.