Bug with getDocumentChildren() and getAllChildren() in version 3.1.10

[q2apro]

When I use this query:

$children = $modx->getDocumentChildren($docid, 1, 0, 'id, pagetitle, description, alias, parent', '', 'pagetitle', 'ASC');

I get the error:

« Evolution CMS Parse Error »
SQLSTATE[42S22]: Column not found: 1054 Unknown column ' pagetitle' in 'field list' (SQL: select `id`, ` pagetitle`, ` description`, ` alias`, ` parent` from `modx_site_content` where `modx_site_content`.`parent` = 11 and `modx_site_content`.`published` = 1 and `modx_site_content`.`deleted` = 0 and `privatemgr` = 0 and `modx_site_content`.`deletedon` = 0 order by `pagetitle` asc)

File /core/vendor/illuminate/database/Connection.php
Line 712

But the column pagetitle is in the database!

It seems that there is a whitespace before the '_pagetitle'.

What I did before: I copied one EvoCMS instance to a new server. The old server processes the query fine, the new server crashes with the error message.

Same error with:

$childrendocs = $modx->getDocumentChildren($parentid, 1, 0, 'id, pagetitle, description, alias, parent, content', '', 'alias + 0', 'ASC');

« Evolution CMS Parse Error »
SQLSTATE[42S22]: Column not found: 1054 Unknown column ' pagetitle' in 'field list' (SQL: select `id`, ` pagetitle`, ` description`, ` alias`, ` parent`, ` content` from `modx_site_content` where `modx_site_content`.`parent` = 71 and `modx_site_content`.`published` = 1 and `modx_site_content`.`deleted` = 0 and `privatemgr` = 0 and `modx_site_content`.`deletedon` = 0 order by `alias + 0` asc)

I also updated to EVO CMS 3.1.10. Could this be the problem?

[q2apro]

The Backtrace:

Backtrace
--
EvolutionCMS\Core->processRoutes()index.php on line 137
Illuminate\Routing\Router->dispatch()core/src/Core.php on line 2701
Illuminate\Routing\Router->dispatchToRoute()core/vendor/illuminate/routing/Router.php on line 651
Illuminate\Routing\Router->runRoute()core/vendor/illuminate/routing/Router.php on line 662
Illuminate\Routing\Router->runRouteWithinStack()core/vendor/illuminate/routing/Router.php on line 698
Illuminate\Pipeline\Pipeline->then()core/vendor/illuminate/routing/Router.php on line 723
Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()core/vendor/illuminate/pipeline/Pipeline.php on line 103
Illuminate\Routing\Router->Illuminate\Routing\{closure}()core/vendor/illuminate/pipeline/Pipeline.php on line 128
Illuminate\Routing\Route->run()core/vendor/illuminate/routing/Router.php on line 721
Illuminate\Routing\Route->runCallable()core/vendor/illuminate/routing/Route.php on line 208
EvolutionCMS\Extensions\Router->EvolutionCMS\Extensions\{closure}()core/vendor/illuminate/routing/Route.php on line 238
EvolutionCMS\Core->executeParser()core/src/Extensions/Router.php on line 25
EvolutionCMS\Core->prepareResponse()core/src/Core.php on line 2820
EvolutionCMS\Core->outputContent()core/src/Core.php on line 2979
EvolutionCMS\Core->parseDocumentSource()core/src/Core.php on line 784
EvolutionCMS\Core->evalSnippets()core/src/Core.php on line 2650
EvolutionCMS\Core->_get_snip_result()core/src/Core.php on line 1993
EvolutionCMS\Core->evalSnippet()core/src/Core.php on line 2082
eval()core/src/Core.php on line 1918
EvolutionCMS\Core->getDocumentChildren()core/src/Core.php(1918) : eval()'d code on line 111
Illuminate\Database\Eloquent\Builder->get()core/src/Core.php on line 3955
Illuminate\Database\Eloquent\Builder->getModels()core/vendor/illuminate/database/Eloquent/Builder.php on line 609
Illuminate\Database\Query\Builder->get()core/vendor/illuminate/database/Eloquent/Builder.php on line 625
Illuminate\Database\Query\Builder->onceWithColumns()core/vendor/illuminate/database/Query/Builder.php on line 2374
Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()core/vendor/illuminate/database/Query/Builder.php on line 2907
Illuminate\Database\Query\Builder->runSelect()core/vendor/illuminate/database/Query/Builder.php on line 2373
Illuminate\Database\Connection->select()core/vendor/illuminate/database/Query/Builder.php on line 2385
Illuminate\Database\Connection->run()core/vendor/illuminate/database/Connection.php on line 376
Illuminate\Database\Connection->runQueryCallback()core/vendor/illuminate/database/Connection.php on line 672

[q2apro]

Replacing the function getDocumentChildren() with the version from v2.0.4 removes the bug!

Function can can be found in file /core/src/Core.php

The previous function works without problems:

    public function getDocumentChildren(
        $parentid = 0,
        $published = 1,
        $deleted = 0,
        $fields = '*',
        $where = '',
        $sort = 'menuindex',
        $dir = 'ASC',
        $limit = ''
    ) {

        $cacheKey = md5(print_r(func_get_args(), true));
        if (isset($this->tmpCache[__FUNCTION__][$cacheKey])) {
            return $this->tmpCache[__FUNCTION__][$cacheKey];
        }

        if ($published === 'all') {
            $published = '';
        } else {
            $published = 'AND sc.published = ' . $published;
        }
        if ($deleted === 'all') {
            $deleted = '';
        } else {
            $deleted = 'AND sc.deleted = ' . $deleted;
        }

        if ($where != '') {
            $where = 'AND ' . $where;
        }

        // modify field names to use sc. table reference
        $fields = 'sc.' . implode(',sc.', array_filter(array_map('trim', explode(',', $fields))));
        if ($sort == '') {
            $sort = '';
        } else {
            $sort = 'sc.' . implode(',sc.', array_filter(array_map('trim', explode(',', $sort))));
        }

        // get document groups for current user
        if ($docgrp = $this->getUserDocGroups()) {
            $docgrp = implode(',', $docgrp);
        }

        // build query
        if ($this->isFrontend()) {
            if (!$docgrp) {
                $access = ('sc.privateweb=0');
            } else {
                $access = sprintf('sc.privateweb=0 OR dg.document_group IN (%s)', $docgrp);
            }
        } else {
            $access = ('1="' . $_SESSION['mgrRole'] . '" OR sc.privatemgr=0') . (!$docgrp ? '' : ' OR dg.document_group IN (' . $docgrp . ')');
        }

        $tblsc = $this->getDatabase()->getFullTableName('site_content');
        $tbldg = $this->getDatabase()->getFullTableName('document_groups');

        $result = $this->getDatabase()->select("DISTINCT {$fields}", "{$tblsc} sc
                LEFT JOIN {$tbldg} dg on dg.document = sc.id",
            "sc.parent = '{$parentid}' {$published} {$deleted} {$where} AND ({$access}) GROUP BY sc.id",
            ($sort ? "{$sort} {$dir}" : ""), $limit);

        $resourceArray = $this->getDatabase()->makeArray($result);

        $this->tmpCache[__FUNCTION__][$cacheKey] = $resourceArray;

        return $resourceArray;
    }

The new function from 3.1.10 does not work.

[q2apro]

The same problem and fix with getAllChildren().

Version from EVO2:

    public function getAllChildren($id = 0, $sort = 'menuindex', $dir = 'ASC', $fields = 'id, pagetitle, description, parent, alias, menutitle') {
        static $cached = array();
        $cacheKey = md5(print_r(func_get_args(), true));
        if (isset($cached[$cacheKey])) {
            return $cached[$cacheKey];
        }

        $cached[$cacheKey] = false;

        // modify field names to use sc. table reference
        $fields = 'sc.' . implode(',sc.', array_filter(array_map('trim', explode(',', $fields))));
        $sort = 'sc.' . implode(',sc.', array_filter(array_map('trim', explode(',', $sort))));
        // get document groups for current user
        if ($docgrp = $this->getUserDocGroups()) {
            $docgrp = implode(',', $docgrp);
        }
        // build query
        if ($this->isFrontend()) {
            if (!$docgrp) {
                $access = 'sc.privateweb=0';
            } else {
                $access = sprintf('sc.privateweb=0 OR dg.document_group IN (%s)', $docgrp);
            }
        } else {
            if (!$docgrp) {
                $access = sprintf(
                    "1='%s' OR sc.privatemgr=0"
                    , $_SESSION['mgrRole']
                );
            } else {
                $access = sprintf(
                    "1='%s' OR sc.privatemgr=0 OR dg.document_group IN (%s)"
                    , $_SESSION['mgrRole']
                    , $docgrp
                );
            }
        }
        $result = $this->getDatabase()->select(
            'DISTINCT ' . $fields
            , sprintf(
                '%s sc
                LEFT JOIN %s dg on dg.document = sc.id'
                , $this->getDatabase()->getFullTableName('site_content')
                , $this->getDatabase()->getFullTableName('document_groups')
            )
            , sprintf(
                "sc.parent=%d AND (%s) GROUP BY sc.id"
                , (int)$id
                , $access
            )
            , $sort . ' ' . $dir
        );
        $resourceArray = $this->getDatabase()->makeArray($result);
        $cached[$cacheKey] = $resourceArray;

        return $resourceArray;
    }

The previous version works.

[q2apro]

Will there be a fix?

So that those normal queries will work with EVO CMS 3.10+ again?

$children = $modx->getDocumentChildren($docid, 1, 0, 'id, pagetitle, description, alias, parent', '', 'pagetitle', 'ASC');

[q2apro]

I used the updater and got version 3.1.10.

How to tell the updater it should use the community edition?

When will it be fixed? With 3.1.11?