Sort Order & Space Sort Order

[luke-]

After merging this https://github.com/humhub/humhub/pull/6247 following error appears in the log in combination with the REST API.

ERROR  Sun, 07 May 2023 17:19:19 +0200 yii\db\IntegrityException
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'sort_order' in order clause is ambiguous in /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute()
#2 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(436): yii\db\Command->queryInternal()
#3 /srv/humhub/protected/vendor/yiisoft/yii2/db/Query.php(506): yii\db\Command->queryScalar()
#4 /srv/humhub/protected/vendor/yiisoft/yii2/db/ActiveQuery.php(352): yii\db\Query->queryScalar()
#5 /srv/humhub/protected/vendor/yiisoft/yii2/db/Query.php(368): yii\db\ActiveQuery->queryScalar()
#6 /srv/www/htdocs/community/modules/rest/components/BaseController.php(155): yii\db\Query->count()
#7 /srv/www/htdocs/community/modules/rest/components/BaseContentController.php(109): humhub\modules\rest\components\BaseController->handlePagination()
#8 [internal function]: humhub\modules\rest\components\BaseContentController->actionFindByContainer()
#9 /srv/humhub/protected/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#10 /srv/humhub/protected/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#11 /srv/humhub/protected/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()
#12 /srv/humhub/protected/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction()
#13 /srv/humhub/protected/vendor/yiisoft/yii2/base/Application.php(384): yii\web\Application->handleRequest()
#14 /srv/www/htdocs/community/index.php(24): yii\base\Application->run()
#15 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'sort_order' in order clause is ambiguous
The SQL being executed was: SELECT COUNT(*) FROM (SELECT DISTINCT `post`.* FROM `post` LEFT JOIN `content` ON `post`.`id` = `content`.`object_id` LEFT JOIN `contentcontainer` ON `content`.`contentcontainer_id` = `contentcontainer`.`id` LEFT JOIN `user` ON `content`.`created_by` = `user`.`id` LEFT JOIN `content_tag_relation` ON `content`.`id` = `content_tag_relation`.`content_id` LEFT JOIN `content_tag` ON `content_tag_relation`.`tag_id` = `content_tag`.`id` LEFT JOIN `space` ON contentcontainer.pk=space.id AND contentcontainer.class='humhub\\modules\\space\\models\\Space' LEFT JOIN `user` `cuser` ON contentcontainer.pk=cuser.id AND contentcontainer.class='humhub\\modules\\user\\models\\User' LEFT JOIN `space_membership` ON contentcontainer.pk=space_membership.space_id AND contentcontainer.class='humhub\\modules\\space\\models\\Space' AND space_membership.user_id=10686 WHERE ((`contentcontainer`.`pk`=6) AND (`contentcontainer`.`class`='humhub\\modules\\space\\models\\Space')) AND (`content`.`state`=1) AND (space.id IS NOT NULL AND ( space_membership.status=3 OR (content.visibility=1 AND space.visibility != 0) ) OR cuser.id IS NOT NULL AND ( (content.visibility = 1) OR (content.visibility = 0 AND content.contentcontainer_id=10477))OR content.created_by=10686 OR content.contentcontainer_id IS NULL) AND (`content_tag`.`id` IS NOT NULL) AND (`content_tag`.`name` IN ('HumHub', 'Enterprise', 'Marketplace')) AND (`content`.`object_model`='humhub\\modules\\post\\models\\Post') AND (`content`.`object_model`='humhub\\modules\\post\\models\\Post') AND (`content`.`object_model`='humhub\\modules\\post\\models\\Post') ORDER BY `content`.`created_at` DESC, `sort_order`) `c` in /srv/humhub/protected/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()
#1 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute()
#2 /srv/humhub/protected/vendor/yiisoft/yii2/db/Command.php(436): yii\db\Command->queryInternal()
#3 /srv/humhub/protected/vendor/yiisoft/yii2/db/Query.php(506): yii\db\Command->queryScalar()
#4 /srv/humhub/protected/vendor/yiisoft/yii2/db/ActiveQuery.php(352): yii\db\Query->queryScalar()
#5 /srv/humhub/protected/vendor/yiisoft/yii2/db/Query.php(368): yii\db\ActiveQuery->queryScalar()
#6 /srv/www/htdocs/community/modules/rest/components/BaseController.php(155): yii\db\Query->count()
#7 /srv/www/htdocs/community/modules/rest/components/BaseContentController.php(109): humhub\modules\rest\components\BaseController->handlePagination()
#8 [internal function]: humhub\modules\rest\components\BaseContentController->actionFindByContainer()
#9 /srv/humhub/protected/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#10 /srv/humhub/protected/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#11 /srv/humhub/protected/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()
#12 /srv/humhub/protected/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction()
#13 /srv/humhub/protected/vendor/yiisoft/yii2/base/Application.php(384): yii\web\Application->handleRequest()
#14 /srv/www/htdocs/community/index.php(24): yii\base\Application->run()
#15 {main}
Additional Information:
Array
(
[0] => 23000
[1] => 1052
[2] => Column 'sort_order' in order clause is ambiguous
)

[yurabakhtin]

@luke- Fixed in PR https://github.com/humhub/humhub/pull/6299.

The error occured on call rest api point of finding contents by container and filter by topics, i.e. on call two methods CotentActiveRecord->contentContainer()->contentTag() in the same time, because the join $this->joinWith('content.tags') added ->orderBy('sort_order'). I think we should find all ->orderBy('sort_order') in core and all modules and add table prefix, because we already detected the same issue from the wiki modules side.

[luke-]

@yurabakhtin Thanks! :-)