Error after upgrade from Craft CMS 3.1

[nettum]

After upgrade from craft cms 3.1 I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.structureId' in 'group statement'
in /.../vendor/yiisoft/yii2/db/Command.php at line 1293

from the following code:

                Similar::getInstance()->similar->find([
                    'element' => $entry,
                    'context' => $entry->articleCategories,
                    'criteria' => [
                        'siteId' => Craft::$app->getSites()->getCurrentSite()->id,
                        'section' => 'articles',
                        'type' => 'fullArticle',
                        'limit' => 1,
                    ]
                ])

After commenting out the code above the entry loads as normal.

Full stack:

Database Exception – yii\db\Exception
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.structureId' in 'group statement'
The SQL being executed was: SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) AS `count`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`entries`.`typeId`='6') AND (`entries`.`sectionId`='6') AND ((elements.id != '2511') AND (`relations`.`targetId`='1579')) AND (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2020-06-08 12:02:00') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2020-06-08 12:02:00'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
GROUP BY `elements`.`id`, `structureelements`.`structureId`, `structureelements`.`lft`
ORDER BY `entries`.`postDate` DESC) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE `relations`.`targetId`='1579'
GROUP BY `relations`.`sourceId`, `elements`.`id`
ORDER BY `count` DESC
LIMIT 1
Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'structureelements.structureId' in 'group statement'
)
↵
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.structureId' in 'group statement'
in /.../vendor/yiisoft/yii2/db/Command.php at line 1293

Any suggestions? $entry->articleCategories is a category field and the error only happens when I have a category entry in articleCategories field

[khalwat]

Probably from here: https://github.com/nystudio107/craft-similar/blob/v1/src/services/Similar.php#L144

That code was added by @svale in this PR: #17

I'm not sure what it's attempting to do

[nettum]

Did try to change it back to only $query->subQuery->addGroupBy('structureelements.lft'); but then I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.lft' in 'group statement'

Removing the if statement all together make the code work again, but unsure if that will have implications elsewhere?

🤔

[stevehurst]

I'm getting the same database error on Tags 1.0.7 and Craft 3.4.24

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'structureelements.structureId' in 'group statement'
The SQL being executed was: SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) AS `count`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`entries`.`sectionId`='10') AND ((elements.id != '22398') AND (`relations`.`targetId`='24389')) AND (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2020-06-17 14:52:00') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2020-06-17 14:52:00'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
GROUP BY `elements`.`id`, `structureelements`.`structureId`, `structureelements`.`lft`
ORDER BY `entries`.`postDate` DESC) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE `relations`.`targetId`='24389'
GROUP BY `relations`.`sourceId`, `elements`.`id`
ORDER BY `count` DESC

[JMKelley]

Getting a similar error as noted in this SO question:
https://craftcms.stackexchange.com/questions/36162/similar-plugin-filter-results-by-section

[khalwat]

I'm going to see about devoting some time to fixing this up in the near term

[khalwat]

This should be fixed in the dev-develop branch

You can try it now by setting your semver in your composer.json to look like this:

    "nystudio107/craft-similar": "dev-develop as 1.1.0”,

Then do a composer update

Let me know how you go...

[khalwat]

Fixed in https://github.com/nystudio107/craft-similar/releases/tag/1.1.0