Sort Order By

11 posts by 4 authors in: Forums > CMS Builder
Last Post: November 3, 2011   (RSS)

By zip222 - April 3, 2009

Having an issue with sorting order...

I have the "sort order by" set as follows:
date_awarded DESC, category, grant_amount DESC, title

Meaning, I want the records to be listed by Date, then by category, then by a dollar amount, and then by the title.

All sorting appears to function properly except for the amount column. It works fine as long as all values are less than six figures... 99,999 or less. If I set any of the values above 99,999 they don't appear in the proper place in the sort - they appear last rather than first.

The field is setup as a text field and only accepts numbers, commas and decimal points. I am not allowing dollar signs.

Anyone have any thoughts why this is happening?

Re: [jdancisin] Sort Order By

By zip222 - April 3, 2009

Through a little trial and error, it seems like a sort using numbers works like an alphabetical sort. It groups all values starting with "1", and then determines the order of the "1" values by the second character. As opposed to sorting the list by the full value of the number.

Is this standard behavior? Is there a way around this?

Re: [jdancisin] Sort Order By

By zip222 - April 3, 2009

Figured it out!

By changing my sort order to this...
date_awarded DESC, category, (grant_amount+0) DESC, title

The numbers are now seen as numbers rather than characters. Cool!

If someone knows another solution to this, I would be interested in knowing what it is.

Re: [jdancisin] Sort Order By

By Dave - April 3, 2009

Hi jdancisin,

The simplest is to just add +0 when sorting, but a more advanced option would be to set the field to be a numeric column type in mysql. You can do that under: Admin > Section Editors > Your Section > Your Field > Advanced > Mysql Column Type

Depending on your content you might want INT or FLOAT. You can see a list of column types here:
http://help.scibit.com/Mascon/masconMySQL_Field_Types.html

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Sort Order By

By zip222 - May 14, 2009

I need to bring this post back up because there is still an issue...

I am ordering by a field that contains a number and it works fine as long as all of the numbers are between 1,000 and 999,000. But I have a few numbers that are outside that range and they aren't ordering properly. I am thinking I may need to use the mySQL column type to get this to work properly.

Help? Thoughts?

Re: [jdancisin] Sort Order By

By ross - May 15, 2009

Hi there.

Would you mind sending me an email with FTP details to your site through consulting@interactivetools.com. Also put a link to this thread in there so I know what I need to look for :).

I would like to take a closer look at how you have all this setup. My first thought is that perhaps the data format is the problem. The best case is to always enter your numbers without any formatting. So you would use 1000 instead of 1,000.

Let me know what you think and send those details when you can.

Thanks!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Re: [Dave] Sort Order By

By Toledoh - November 3, 2011

When I try to do this, I get an error pop-up.

There was an error changing the MySQL Column, the error was:

Incorrect integer value: '' for column 'price' at row 16

See attached.
Cheers,

Tim (toledoh.com.au)
Attachments:

nuts-n-bolts-2011-11-04-11-00-45.jpeg 171K

nuts-n-bolts-2011-11-04-11-01-17.jpeg 170K

Re: [Toledoh] Sort Order By

By Dave - November 3, 2011

Hi Tim,

You can't have blank values for INT fields, you need to default everything to zero ("0"). Is there an easy way you can update the records with a blank price to be 0 instead?
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Sort Order By

By Toledoh - November 3, 2011

I've used 'orderBy' =>'(price +0) DESC', and this is doing the trick, so I'll stick with that.

Thanks Dave
Cheers,

Tim (toledoh.com.au)