Thread

Hi,

Been doing quite a fair amount of bespoke development recently, thought I’d share this as it’s bound to help some one out in the future.

If you ever find your self needing to sort (ORDER BY) a custom field by a numeric value you may find yourself becoming unstuck.

The “problem” is that EE stores all custom field data as VARCHAR.  This causes an issue when trying to sort on a numeric value.

e.g.

Imagine you have the following record set of ids:

200
100
10000
2000
1000
20000

The following SQL would sort them like so:

mysql> SELECT column FROM table_name ORDER BY column; 

column
======
100
1000
10000
200
2000
20000
...

Which, whilst ordering correctly is undesirable.  The following quick fix allows you to sort numerically on varchar fields without having to use CAST. 

mysql> SELECT column FROM table_name ORDER BY column+0; 

column
======
100
200
1000
2000
10000
20000
...

Hope someone finds this as useful as I did.

Thank you for sharing.  I hope you’ll take a moment to add this to the wiki so that it continues to be easy to find! =)

how would I add this code to my template? I am trying to sort weblog entries by number, starting with the lowest. where would I add this line:

mysql> SELECT column FROM table_name ORDER BY column+0;

and do I need to add anything else to this code or just us as it is?

You shouldn’t need this inside an exp:weblog:entries loop. You’d just add sort=“asc”.

The field I need to sort for has only numbers. The following is how sort=“asc” sorts my entries:
2034
2014
2054
2130

The following is how I need them to be sorted: lowest to highest
2014
2034
2054
2130

I am also trying to change database custom field to INT and then using the orderby=“custom-field-name”.
So far not successful with that method so I wanted to see if I can use this sql code.

Florian, you have added to a lot of threads on the subject, but the answers are not going to be different. This one really outlines your options and gives you a few lines of code to work with.

Still trying to solve this issue. Changing the database field to INT didnt work for me. The entries still dont get sorted lowest to highest. See above post.
I will try the cast method that is mentioned in your suggested post. I dont know php or cast, so I was avoiding this option. Hopefully I can just reuse the sample code ..