Sorting problem with getCollection

☆ A M B ☆
326 Posts

aladage Reply #1, 15 years, 2 months ago

I have a fairly simple third-party table structure that lists a field name in one column and a field value (always a number, sometimes a decimal number) in another. I’m using getCollection to retrieve all values that match a certain field name. I want to sort them from largest to smallest value, so right now, my getCollection call looks like this:

$c = $modx->newQuery('Stats');
$whereArray = array(
   'statName' => 'tackles'
);
$c->where($whereArray);
$c->sortby('value','DESC');
$stats = $modx->getCollection('Stats',$c);

This works great on whole numbers. However, sometimes (but not always) the value field is a decimal, and sortBy is ignoring the decimal. The value field is varchar, which may be the problem, but I’m not sure how to store decimal and non-decimal numbers in the same field any other way (could use advice on that). What am I doing wrong? Thanks!

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #2, 15 years, 2 months ago

Could use a float or decimal database field type?

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
(You can pick your MySQL version in the left menu)

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

MODX Staff
10,725 Posts

opengeek Reply #3, 15 years, 2 months ago

The db field would have to be a DECIMAL to be able to sort properly on it in the query. This has nothing to do with getCollection. That said, you can call CAST() in the sortby, e.g.

$c->sortby("CAST({$modx->escape('value')} AS DECIMAL(5,2))", "DESC");

Be aware that this will not be indexed and the query will be slower than if you stored the data as a DECIMAL in the table column with a proper index.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
326 Posts

aladage Reply #4, 15 years, 2 months ago

Thanks, guys. DB storage basics I should know

I’m going to modify my DB to properly store as decimal. However, what’s the best approach for displaying only the whole number when the after-decimal number is zero? For now, I’m just doing a str_replace, but I don’t know if there’s a better way.

EDIT: Changing my schema to decimal worked great. Thanks!