getResources sortbyTV wrong order using double digit numbers

24 Posts

logiag Reply #11, 15 years, 8 months ago

Thank for help, but I still don understandd¡¡ I’m a novice¡¡¡¡¡

I’ve imported a site from EVO to REVO and i’m migrating Ditto to GetResources.

Snippets works fine, but I need to sort by TV from this values: 10, 9.5, 9, 8.5, etc.

Using DITTO: &orderBy=`numbers DESC` (this paremeters works fine)

Using GetResources: &sortbyTV=`numbers` &sortdirTV =`DESC` (9.5, 9, 8.5, 10) Number 10 is in the end of the list.

Where sould I put the code?

Thanks again?

30 Posts

Nadoedalo Reply #12, 15 years, 8 months ago

You must put $rate = str_pad($rate, ’2’,’0’,STR_PAD_LEFT); in the snippet that counts numbers and puts it in TV.
And $number = ltrim($number,"0"); in additional snippet that transforms tv.Rating in outputted number without 0.
Or you can write your own sorting or updating TV values if you do not have counting snippet.

863 Posts

Lucas Reply #13, 15 years, 7 months ago

Hi Jason, if you’re reading this is there any way we could make sorting numerical TVs in getResources a little more user-friendly out of the box? I’m sure it will catch quite a lot of people out... and the solutions above are not exactly straightforward. I’m still trying to work out a simple way to do it.

When setting up my TV I’ve selected ’Input type: Number’. Given how likely it is that a ’Number’ type tv would eventually be sorted, wouldn’t it be logical for MODx to format any number entered into a sortable string, perhaps by making it an Output String option? Or would there be a way for getResources to handle numerical sorting in a future version?

I’m sure there’s a reason why it’s been set up this way but it just seemed so easy with Ditto in Evolution

MODX Staff
10,725 Posts

opengeek Reply #14, 15 years, 7 months ago

I do have an idea for adding custom post-query sorting functions, but I haven’t had time to realize the code as of yet...

Right now getResources does not support anything but query-based sorting (IOW it’s sorted in the SQL query sent to the database), unlike Ditto which did post-query sorting (in PHP) after retrieving the result set. Both approaches have advantages and disadvantages, though TBH, sorting on a Template Variable is not the intention of Template Variables (they are for organizing the presentation layer, thus Template Variables) and you will unlikely ever be able to efficiently sort that result set using that data, at least with any quantity of data. But if efficiency and scalability is not as important as convenience here, then maybe the post-query sorting will suffice when it is available.

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

863 Posts

Lucas Reply #15, 15 years, 7 months ago

Ah, gotcha. I hadn’t understood why it ’worked’ for Ditto but not getResources. Thanks for the quick response!

I agree that TVs are not really the right place for data, but as you said, sometimes convenience is more important. I imagine it would be very useful if future versions of getResources could handle this kind of sorting. I also appreciate the amount of work you and the other developers are putting into Revo and it’s absolutely up to you guys to prioritise.

That said, should I post a request for post-query sorting on your gitHub page?

MODX Staff
10,725 Posts

opengeek Reply #16, 15 years, 7 months ago

Quote from: lucas at Mar 10, 2011, 03:45 AM

That said, should I post a request for post-query sorting on your gitHub page?1

If one does not already exist for it, sure... wink

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

61 Posts

dj13 Reply #17, 15 years, 7 months ago

Quote from: lucas at Mar 09, 2011, 12:33 PM

Hi Jason, if you’re reading this is there any way we could make sorting numerical TVs in getResources a little more user-friendly out of the box? I’m sure it will catch quite a lot of people out... and the solutions above are not exactly straightforward. I’m still trying to work out a simple way to do it.

Hi!
I needed it too. Just as Jason said TV value is always string. After little googling I find simple solution. Ofcourse there is small change of the getResources code, and one more conditional parameter (tvNumericSort for forcing sort numeric).

Original code from line 195..

if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    $criteria->sortby("`sortTV`", $sortdirTV);
}

Change to:

if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    if (!empty($tvNumericSort)){
        $criteria->sortby("CAST(`sortTV` AS DECIMAL)", $sortdirTV);       
    } else {
        $criteria->sortby("`sortTV`", $sortdirTV);
    }
}

And call getResources like this:

[[!getResources? 
&parents=`4,10,5` 
&tpl=`myTpl`
&includeTVs=`1` 
&sortbyTV=`myTvVar` 
&sortdirTV=`DESC` 
&tvNumericSort=`1`
]]

I hope this will help someone.

863 Posts

Lucas Reply #18, 15 years, 7 months ago

@dj13: that looks pretty neat! Will test it out tomorrow and report back. Cheers

24 Posts

logiag Reply #19, 15 years, 7 months ago

Quote from: dj13 at Mar 19, 2011, 01:19 PM

Quote from: lucas at Mar 09, 2011, 12:33 PM

Hi Jason, if you’re reading this is there any way we could make sorting numerical TVs in getResources a little more user-friendly out of the box? I’m sure it will catch quite a lot of people out... and the solutions above are not exactly straightforward. I’m still trying to work out a simple way to do it.

Hi!
I needed it too. Just as Jason said TV value is always string. After little googling I find simple solution. Ofcourse there is small change of the getResources code, and one more conditional parameter (tvNumericSort for forcing sort numeric).

Original code from line 195..
if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    $criteria->sortby("`sortTV`", $sortdirTV);
}
Change to:
if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    if (!empty($tvNumericSort)){
        $criteria->sortby("CAST(`sortTV` AS DECIMAL)", $sortdirTV);       
    } else {
        $criteria->sortby("`sortTV`", $sortdirTV);
    }
}
And call getResources like this:
[[!getResources? 
&parents=`4,10,5` 
&tpl=`myTpl`
&includeTVs=`1` 
&sortbyTV=`myTvVar` 
&sortdirTV=`DESC` 
&tvNumericSort=`1`
]]
I hope this will help someone.

It doesn’t work with "." Example: 9.8 - 8.8 - 7.8 - 10

61 Posts

dj13 Reply #20, 15 years, 7 months ago

Quote from: logiag at Mar 20, 2011, 09:54 PM

Quote from: dj13 at Mar 19, 2011, 01:19 PM
Quote from: lucas at Mar 09, 2011, 12:33 PM

Hi Jason, if you’re reading this is there any way we could make sorting numerical TVs in getResources a little more user-friendly out of the box? I’m sure it will catch quite a lot of people out... and the solutions above are not exactly straightforward. I’m still trying to work out a simple way to do it.

Hi!
I needed it too. Just as Jason said TV value is always string. After little googling I find simple solution. Ofcourse there is small change of the getResources code, and one more conditional parameter (tvNumericSort for forcing sort numeric).

Original code from line 195..
if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    $criteria->sortby("`sortTV`", $sortdirTV);
}
Change to:
if (!empty($sortbyTV)) {
    $criteria->leftJoin('modTemplateVar', 'tvDefault', array(
        "tvDefault.name" => $sortbyTV
    ));
    $criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
        "`tvSort`.`contentid` = `modResource`.`id`",
        "`tvSort`.`tmplvarid` = `tvDefault`.`id`"
    ));
    $criteria->select("IFNULL(`tvSort`.`value`, `tvDefault`.`default_text`) AS `sortTV`");
    if (!empty($tvNumericSort)){
        $criteria->sortby("CAST(`sortTV` AS DECIMAL)", $sortdirTV);       
    } else {
        $criteria->sortby("`sortTV`", $sortdirTV);
    }
}
And call getResources like this:
[[!getResources? 
&parents=`4,10,5` 
&tpl=`myTpl`
&includeTVs=`1` 
&sortbyTV=`myTvVar` 
&sortdirTV=`DESC` 
&tvNumericSort=`1`
]]
I hope this will help someone.
It doesn’t work with "." Example: 9.8 - 8.8 - 7.8 - 10

Yes my mistake wink

Try this.
Line:

$criteria->sortby("CAST(`sortTV` AS DECIMAL)", $sortdirTV);

Change to:

$criteria->sortby("CAST(`sortTV` AS DECIMAL(10,2))", $sortdirTV);

Example from MySql:

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

I have done quick test, and worked for me.