Answered GetResources sortby price

I'm trying to list a range of products by ascending value. The following call...



...messes up the result and displays prices in the following order:

795
595
650
895
1,650
1,395
etc

...so in some sort of order but not quite. I'm aware that the comma might be a problem. Sorting by `integer` gives the same result.

I've tried entering the `product_rrp` in formats such as 00,795 | 00,595 | 01,650 to no avail.

I've considered stripping the comma out of the product_rrp field but I'm still not convinced I would get the right result when some prices are 3 figures, some are 4 figures and some are 5 figures.
Revo 2.4.3
GetResources 1.6.1

Any help, much appreciated.

I don't think it's a problem with the commas alone. I don't think it's sorting by that TV at all. If it were, the ones starting with one would be at the top and the list would be in ascending order based on the first digit.

TVs are always returned as strings, so you might try removing the &sortbyTVType property and making them all 5 digits:



There's seems to be something else wrong though, because getResources is supposed to sort the strings as decimals:



I imagine you've double-checked the spelling of the TV. Are any of them @INHERIT TVs?

Thanks for looking at this Bob. I'm not using any @INHERIT TVs.

I agree that &sortbyTVType appears to be doing nothing but removing the &sortbyTVType doesn't help. After some hunting around (inspired by PdoTools) I came up with the following - it's odd as it shouldn't work and still appears to do nothing when I debug. Prices are formatted exactly as my original post...

[[getResources?
&parents=`146,11`
&resources=`-[[*id]]`
&tpl=`productTpl`
&tplLast=`productTplLast`
&showHidden=`0`
&limit=`0`
&depth=`3`
&hideContainers=`1`
&sortbyTV=`product_rrp`
&sortdirTV=`ASC`
&sortbyTVType=`CAST(product_rrp AS DECIMAL(10,2));`
&includeContent=`1`
&includeTVs=`1`
&processTVs=`1`
&tvPrefix=``
&tvFilters=`make==%[[*pagetitle]]%||maker_text==%[[*pagetitle]]%`
]]

I'm wondering if PdoTools might be a better fit but too late for this project really. Nobody else using pricing to order output in Modx?

On second thought. Moving the price to a resource field is probably not a good idea, since the field would be sorted as a string and there's no way to tell getResources to cast it to a decimal.

I did consider using *description for the price field but there are quite a few products and it's one more thing for the client to remember, if all product details aren't grouped together.

A custom snippet using the money_format() function seems to be the way to go. I can strip the commas out of the database.

Thank you for your help. We have a working solution to get the site live and I can tweak it from there.

Can you report on what worked, for others with the same problem?

The following call worked but I didn't want to stress that too much as I've got a feeling it's not going to be the right solution for anybody else. I also formatted the product_rrp TV prices as...

00,795
00,595
01,650

[[getResources?
&parents=`146,11`
&resources=`-[[*id]]`
&tpl=`productTpl`
&tplLast=`productTplLast`
&showHidden=`0`
&limit=`0`
&depth=`3`
&hideContainers=`1`
&sortbyTV=`product_rrp`
&sortdirTV=`ASC`
&sortbyTVType=`CAST(product_rrp AS DECIMAL(10,2));`
&includeContent=`1`
&includeTVs=`1`
&processTVs=`1`
&tvPrefix=``
&tvFilters=`make==%[[*pagetitle]]%||maker_text==%[[*pagetitle]]%`
]]

Thanks for posting that. Can you tell if the CAST is doing anything? It should be the equivalent of &sortbyTVType=`decimal`.

When I originally tried it I 'switched on' debugging and the report told me nothing about whether it was working. All I know is that without CAST, it doesn't output correctly. The domain is propagating at the moment - when it's done I'll run debug again and report back.