Pdoressources sorting question

sspind · January 8, 2021, 11:12am

Hi!

Is there a possibility to sort pdoressources by a selfdefined list of TV content?

Example: TV-content of “animals” is dog, cat, pig.
I can sort it by TV asc or desc.
But I want my own sorting structure by TV content:
1=pig, 2 = cat, 3 = dog

Is this possible somehow?

Thanks a lot,
Stefan

jaredfhealy · January 8, 2021, 1:49pm

From what I can see in pdoResources, I don’t think you can. I’m not sure how much you are or want to get into PHP, but any Extra that is in Github is very easy to search and explore:

The pdoResources snippet sets up a few things and then passes over to pdoFetch. In the pdoFetch file you can see the sequence of events in the “run” function:

github.com

bezumkin/pdoTools/blob/adff11faa52cca8fcfbf3bd85d4acbdad1a885e4/core/components/pdotools/model/pdotools/pdofetch.class.php#L87


 */
public function run()
{
    $this->makeQuery();
    $this->addTVFilters();
    $this->addTVs();
    $this->addJoins();
    $this->addGrouping();
    $this->addSelects();
    $this->addWhere();
    $this->addSort();
    $this->prepareQuery();

    $output = '';
    if (strtolower($this->config['return']) == 'sql') {
        $this->addTime('Returning raw sql query');
        $output = $this->query->toSQL();
    } else {
        $this->modx->exec('SET SQL_BIG_SELECTS = 1');
        $this->addTime('SQL prepared <small>"' . $this->query->toSQL() . '"</small>');
        $tstart = microtime(true);

Then, drop down to the addSort function:

It looks like line 469 is where it’s doing the sorting and checks the type to determine the sorting statement.

github.com

bezumkin/pdoTools/blob/adff11faa52cca8fcfbf3bd85d4acbdad1a885e4/core/components/pdotools/model/pdotools/pdofetch.class.php#L469


        if (array_key_exists($tv, $this->config['tvsJoin'])) {
            if (!empty($this->config['tvsJoin'][$tv]['tv'])) {
                $this->config['tvsJoin'][$tv]['tv']['type'] = $this->config['sortbyTVType'];
            }
        }
    }
}

$fields = $this->modx->getFields($this->config['class']);
$sorts = $this->replaceTVCondition($tmp);
foreach ($sorts as $sortby => $sortdir) {
    if (preg_match_all('#`TV(.*?)`#', $sortby, $matches)) {
        foreach ($matches[1] as $tv) {
            if (array_key_exists($tv, $this->config['tvsJoin'])) {
                $params = $this->config['tvsJoin'][$tv]['tv'];
                switch ($params['type']) {
                    case 'number':
                    case 'decimal':
                        $sortby = preg_replace('#(TV' . $tv . '\.value|`TV' . $tv . '`\.`value`)#',
                            'CAST($1 AS DECIMAL(13,3))', $sortby);
                        break;

I think what you would need is the ability to pass in a sorting function which would add a fair amount of complexity to that Extra.

There’s only two options I can think of:

Return the raw data, sorting the array results by your custom sorting function, and then further processing to render the templates with that new data set.

Maybe someone can think of a better solution here. There is a &returnIds property you could pass that would return the list of resource Ids instead of any parsed content. You could pass that set of Ids to your own snippet, get them as a collection, sort them based on your logic and then return the Ids back into another pdoResources call with the templates.

Javascript based sorting applied on the client side.

bruno17 · January 8, 2021, 2:30pm

I think, the easiest way would be to loop over all possible animals and run pdoResources for each animal separately.

Maybe, it would also be possible to use the mysql FIELD sorting

halftrainedharry · January 8, 2021, 3:07pm

Maybe you can use the extra Tagger instead of a TV.

Create a group “animals” in Tagger with the field type “Combo box” and add dog, cat and pig as tags to this group.
Then you should be able to use the tables from Tagger as joins in the pdoResources query and sort the result by the field rank of the tag.

[[pdoResources?
    &loadModels=`tagger`
    &leftJoin=`{
        "TaggerTagResource":{"class":"TaggerTagResource", "on":"modResource.id = TaggerTagResource.resource"},
        "TaggerTag":{"class":"TaggerTag", "on":"TaggerTagResource.tag = TaggerTag.id"}
    }`
    &select=`{
        "modResource": "*",
        "TaggerTag": "tag, rank"
    }`
    &sortby=`{
        "rank":"ASC"
    }`
]]

halftrainedharry · January 8, 2021, 3:51pm

It seems that in pdoResources it is possible to use FIELD sorting (like @bruno17 suggested).
So something like this should work:

[[pdoResources?
    &includeTVs=`animals`
    &sortby=`FIELD(animals, 'pig', 'dog', 'cat')`
    &sortdir=`ASC`
]]

sspind · January 9, 2021, 4:33pm

Thanks very much so far FIELD would be my preffered option because it would be easy. But it doesnt seem to work.

This is my working statement:

[[pdoResources?
&tpl=events_tpl_uebersicht
&depth=1
&parents=39
&includeContent=1
&includeTVs=Kursart,Beginn, PLZ, Straße, Ort, Bundesland,kuerzlich_geaendert
&limit=500
&showHidden=1
&sortbyTV=Kursart,Beginn
&sortdirTV=ASC
&tvFilters=Kursart==%[[*description]]%
&select = {"modResource":"id,pagetitle,uri"}
]]

and I changed it to this not working statement:

 [[pdoResources?
&tpl=`events_tpl_uebersicht`
&depth=`1`
&parents=`39`
&includeContent=`1`
&includeTVs=`Kursart,Beginn, PLZ, Straße, Ort, Bundesland,kuerzlich_geaendert`
&limit=`500`
&showHidden=`1`

sortby=FIELD(Kursart, 'Praxis-Wochenende', 'Informations-Vortrag', 'Praxisabend 1')
&sortdir=ASC
&tvFilters=Kursart==%[[*description]]%
&select = {"modResource":"id,pagetitle,uri"}
]]

It doesnt throw any error but the statement doesnt work. Of course there are much more possible contenct entries in “Kursart” but after my knowledge it should start sorting by this.
What Im doing wrong?

halftrainedharry · January 9, 2021, 5:13pm

The problem could be, that all the values of “Kursart” that are not in the FIELD()-list get a value of 0 and are therefore put at the top when sorting by ASC. One Solution could be to sort by DESC and reverse the order of your FIELD()-list.

If that doesn’t work, add the property &showLog=`1` to your pdoResources-call and check in the SQL-query it outputs, that the ORDER BY-clause is correct.

sspind · January 11, 2021, 7:17am

Thanks a lot!

“showlog” shows simply "ORDER BY modResource.publishedon ASC LIMIT 500 " with the new FIELD statement

I digged into “Kursart” and found the TV with the following config:
@EVAL return $modx->runSnippet('listMyResources',array('parent' => 47));

The data fetching in listmyressources is done pdofetch. I dont think that should influence my new sorting but anyhow, this seems not to be the solution.

sspind · January 11, 2021, 7:20am

Before anyone puts more time into the solution I have to say this is done for me because no one will reward the effort which will be put into this further. This special sorting would be a nice addon but not really important. Everyone has to stay on their main tasks.

Thanks a lot!

system · January 13, 2021, 7:20am

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.