search

nystudio107 / craft-similar

Similar for Craft lets you find elements, Entries, Categories, Commerce Products, etc, that are similar, based on... other related elements.
https://nystudio107.com/
MIT License
26 stars 5 forks source link

MySQL Error #8

Closed AdamChlan closed 6 years ago

AdamChlan commented 6 years ago

We are seeing a MySQL error on our production environment, but not elsewhere. Production MySQL is 5.7.22, which is higher than our others. Here is what we are seeing:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'prod_abcxyz.structureelements.lft' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /srv/users/serverpilot/apps/abcxyz/vendor/yiisoft/yii2/db/Command.php:1258
khalwat commented 6 years ago

This is definitely coming from the Similar plugin SQL? Related:

https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106

AdamChlan commented 6 years ago

Here is the rest of the error, where I'm seeing the Similar plugin referenced. I ended up commenting out the Similar template code for now, and all is running well.

I'll check out the link you provided to see what else may be going on.

#0 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()
#1 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT `element...')
#2 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/db/Query.php(161): yii\db\Query->all(NULL)
#5 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1086): craft\db\Query->all(NULL)
#6 /srv/users/serverpilot/apps/letsreimagine/vendor/nystudio107/craft-similar/src/services/Similar.php(98): craft\elements\db\ElementQuery->all()
#7 /srv/users/serverpilot/apps/letsreimagine/vendor/nystudio107/craft-similar/src/variables/SimilarVariable.php(33): nystudio107\similar\services\Similar->find(Array)
#8 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Extension/Core.php(1619): nystudio107\similar\variables\SimilarVariable->find(Array)
#9 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/helpers/Template.php(73): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig_Source), Object(nystudio107\similar\variables\SimilarVariable), 'find', Array, 'method', false, false)
#10 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/a3/a3c586c23618225db82a1327ae838acd95cab5a567ebe72c94c51908c88305af.php(349): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig_Source), Object(nystudio107\similar\variables\SimilarVariable), 'find', Array, 'method')
#11 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(189): __TwigTemplate_93661c7b87d1538cf329a34fce70868f132f5310f6f15dec52dcb5b650e5914a->block_content(Array, Array)
#12 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/e1/e17c77055d20ca28a8f80950f79eb04cebf363309a5fac4cb6fd3c80388ea817.php(41): Twig_Template->displayBlock('content', Array, Array)
#13 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(189): __TwigTemplate_d1074a723d921b47b21c8d8934b82c2ea8dd4623c51bad5a2353251f2b90563a->block_body(Array, Array)
#14 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/5f/5fa2276a948ed3207b6732e8d9e542d2328e8decd7fb32057604c4b6f6e982e7.php(243): Twig_Template->displayBlock('body', Array, Array)
#15 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_47198495b1421bb86436726410d9fd4d0979305891dbb3cf8ddf0ef931a47b3b->doDisplay(Array, Array)
#16 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#17 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#18 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#19 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/e1/e17c77055d20ca28a8f80950f79eb04cebf363309a5fac4cb6fd3c80388ea817.php(30): craft\web\twig\Template->display(Array, Array)
#20 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_d1074a723d921b47b21c8d8934b82c2ea8dd4623c51bad5a2353251f2b90563a->doDisplay(Array, Array)
#21 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#22 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#23 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#24 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/a3/a3c586c23618225db82a1327ae838acd95cab5a567ebe72c94c51908c88305af.php(35): craft\web\twig\Template->display(Array, Array)
#25 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_93661c7b87d1538cf329a34fce70868f132f5310f6f15dec52dcb5b650e5914a->doDisplay(Array, Array)
#26 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#27 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#28 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#29 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(375): craft\web\twig\Template->display(Array)
#30 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Environment.php(289): Twig_Template->render(Array)
#31 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/View.php(330): Twig_Environment->render('events/_entry', Array)
#32 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/View.php(377): craft\web\View->renderTemplate('events/_entry', Array)
#33 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Controller.php(155): craft\web\View->renderPageTemplate('events/_entry', Array)
#34 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/controllers/TemplatesController.php(78): craft\web\Controller->renderTemplate('events/_entry', Array)
#35 [internal function]: craft\controllers\TemplatesController->actionRender('events/_entry', Array)
#36 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#37 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#38 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('render', Array)
#39 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('render', Array)
#40 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('templates/rende...', Array)
#41 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('templates/rende...', Array)
#42 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Application.php(271): yii\web\Application->handleRequest(Object(craft\web\Request))
#43 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#44 /srv/users/serverpilot/apps/letsreimagine/public/index.php(21): yii\base\Application->run()
#45 {main}

Next yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'prod_letsreimagine.structureelements.lft' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
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`
LEFT JOIN `structureelements` `structureelements` ON `structureelements`.`elementId` = `elements`.`id`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`elements_sites`.`siteId`='1') AND ((elements.id != '890') AND (`relations`.`targetId` IN ('16', '12', '21', '19'))) AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2018-08-08 14:31:19') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2018-08-08 14:31:19'))) AND (`elements_sites`.`enabled`=TRUE)
GROUP BY `elements`.`id`
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC
LIMIT 4) `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 `structureelements` `structureelements` ON `structureelements`.`elementId` = `subquery`.`elementsId`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
GROUP BY `relations`.`sourceId`
ORDER BY `count` DESC in /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT `element...')
#1 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT `element...')
#2 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()
#4 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/db/Query.php(161): yii\db\Query->all(NULL)
#5 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1086): craft\db\Query->all(NULL)
#6 /srv/users/serverpilot/apps/letsreimagine/vendor/nystudio107/craft-similar/src/services/Similar.php(98): craft\elements\db\ElementQuery->all()
#7 /srv/users/serverpilot/apps/letsreimagine/vendor/nystudio107/craft-similar/src/variables/SimilarVariable.php(33): nystudio107\similar\services\Similar->find(Array)
#8 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Extension/Core.php(1619): nystudio107\similar\variables\SimilarVariable->find(Array)
#9 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/helpers/Template.php(73): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig_Source), Object(nystudio107\similar\variables\SimilarVariable), 'find', Array, 'method', false, false)
#10 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/a3/a3c586c23618225db82a1327ae838acd95cab5a567ebe72c94c51908c88305af.php(349): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig_Source), Object(nystudio107\similar\variables\SimilarVariable), 'find', Array, 'method')
#11 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(189): __TwigTemplate_93661c7b87d1538cf329a34fce70868f132f5310f6f15dec52dcb5b650e5914a->block_content(Array, Array)
#12 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/e1/e17c77055d20ca28a8f80950f79eb04cebf363309a5fac4cb6fd3c80388ea817.php(41): Twig_Template->displayBlock('content', Array, Array)
#13 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(189): __TwigTemplate_d1074a723d921b47b21c8d8934b82c2ea8dd4623c51bad5a2353251f2b90563a->block_body(Array, Array)
#14 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/5f/5fa2276a948ed3207b6732e8d9e542d2328e8decd7fb32057604c4b6f6e982e7.php(243): Twig_Template->displayBlock('body', Array, Array)
#15 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_47198495b1421bb86436726410d9fd4d0979305891dbb3cf8ddf0ef931a47b3b->doDisplay(Array, Array)
#16 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#17 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#18 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#19 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/e1/e17c77055d20ca28a8f80950f79eb04cebf363309a5fac4cb6fd3c80388ea817.php(30): craft\web\twig\Template->display(Array, Array)
#20 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_d1074a723d921b47b21c8d8934b82c2ea8dd4623c51bad5a2353251f2b90563a->doDisplay(Array, Array)
#21 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#22 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#23 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#24 /srv/users/serverpilot/apps/letsreimagine/storage/runtime/compiled_templates/a3/a3c586c23618225db82a1327ae838acd95cab5a567ebe72c94c51908c88305af.php(35): craft\web\twig\Template->display(Array, Array)
#25 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(390): __TwigTemplate_93661c7b87d1538cf329a34fce70868f132f5310f6f15dec52dcb5b650e5914a->doDisplay(Array, Array)
#26 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(49): Twig_Template->displayWithErrorHandling(Array, Array)
#27 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(367): craft\web\twig\Template->displayWithErrorHandling(Array, Array)
#28 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/twig/Template.php(31): Twig_Template->display(Array, Array)
#29 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Template.php(375): craft\web\twig\Template->display(Array)
#30 /srv/users/serverpilot/apps/letsreimagine/vendor/twig/twig/lib/Twig/Environment.php(289): Twig_Template->render(Array)
#31 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/View.php(330): Twig_Environment->render('events/_entry', Array)
#32 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/View.php(377): craft\web\View->renderTemplate('events/_entry', Array)
#33 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Controller.php(155): craft\web\View->renderPageTemplate('events/_entry', Array)
#34 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/controllers/TemplatesController.php(78): craft\web\Controller->renderTemplate('events/_entry', Array)
#35 [internal function]: craft\controllers\TemplatesController->actionRender('events/_entry', Array)
#36 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#37 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#38 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('render', Array)
#39 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('render', Array)
#40 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('templates/rende...', Array)
#41 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('templates/rende...', Array)
#42 /srv/users/serverpilot/apps/letsreimagine/vendor/craftcms/cms/src/web/Application.php(271): yii\web\Application->handleRequest(Object(craft\web\Request))
#43 /srv/users/serverpilot/apps/letsreimagine/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#44 /srv/users/serverpilot/apps/letsreimagine/public/index.php(21): yii\base\Application->run()
#45 {main}
Additional Information:
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'prod_letsreimagine.structureelements.lft' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)
joshuabaker commented 6 years ago

This is related to counting without grouping. elements.id needs to be grouped in the main/outer query.

toddpadwick commented 6 years ago

I am having this issue also.... i am relatively new to craft templating. is there a simple work around for this? SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'scotscape.structureelements.lft' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by The SQL being executed was: SELECTelements.id,elements_sites.siteId, COUNT(*) as count FROM (SELECTelements.idASelementsId,elements_sites.idASelementsSitesId,content.idAScontentId FROMcraft_elements`elements INNER JOIN craft_entries entries ON entries.id = elements.id INNER JOIN craft_elements_sites elements_sites ON elements_sites.elementId = elements.id INNER JOIN craft_content content ON content.elementId = elements.id LEFT JOIN craft_structureelements structureelements ON structureelements.elementId = elements.id LEFT JOIN craft_relations ON elements.id=craft_relations.sourceId WHERE (elements_sites.siteId='1') AND ((elements.id != '42') AND (craft_relations.targetId IN ('7', '8'))) AND (content.siteId='1') AND (elements.archived=FALSE) AND (((elements.enabled=TRUE) AND (elements_sites.enabled=TRUE)) AND (entries.postDate <= '2018-09-06 11:24:16') AND ((entries.expiryDate IS NULL) OR (entries.expiryDate > '2018-09-06 11:24:16'))) AND (elements_sites.enabled=TRUE) GROUP BY elements.id ORDER BY structureelements.lft, entries.postDate DESC) subquery INNER JOIN craft_entries entries ON entries.id = subquery.elementsId INNER JOIN craft_elements elements ON elements.id = subquery.elementsId INNER JOIN craft_elements_sites elements_sites ON elements_sites.id = subquery.elementsSitesId INNER JOIN craft_content content ON content.id = subquery.contentId LEFT JOIN craft_structureelements structureelements ON structureelements.elementId = subquery.elementsId LEFT JOIN craft_relations ON elements.id=craft_relations.sourceId WHERE craft_relations.targetId IN ('7', '8') GROUP BY craft_relations.sourceId ORDER BY count DESC`

AdamChlan commented 6 years ago

@khalwat did my code provided in https://github.com/nystudio107/craft-similar/issues/8#issuecomment-411947586 help at all? Do you think this is something that would be addressed in the plugin, or are you thinking it's a fix that happens on the server?

khalwat commented 6 years ago

@AdamChlan That link doesn't seem to work?

AdamChlan commented 6 years ago

@khalwat hmm, was just referencing my comment with additional error code. Here you go:

https://github.com/nystudio107/craft-similar/issues/8#issuecomment-411947586

svale commented 6 years ago

I ran into the same issue. It seems a possible fix, or at least a workaround, is to expand the groupBy statements as @joshuabaker was suggesting in #10, but then also in the sub-query. So in src/services/Similar.php replacing:

$query->query->groupBy('{{%relations}}.sourceId'); with $query->query->groupBy(['{{%relations}}.sourceId', 'elements.id']);

and

$query->subQuery->groupBy('elements.id'); with $query->subQuery->groupBy(['structureelements.lft', 'elements.id']);

For me this sorted SQL errors for entries with and without relations.

Om a side note, if it can be of any use, I also ran into a case where the SQL-query returned a record of a null-values that were passed on to getElementById() which then failed. Guarded against with an extra if{}:

        foreach ($results as $config) {
            if($config['id'] && $config['siteId']) {
                $model = $elements->getElementById($config['id'], $elementClass, $config['siteId']);
                if ($model) {
                    // The `count` property is added dynamically by our CountBehavior behavior
                    /** @noinspection PhpUndefinedFieldInspection */
                    $model->count = $config['count'];
                    $models[] = $model;
                }
            }
khalwat commented 6 years ago

@svale that's fantastic... do you want to PR it?

svale commented 6 years ago

Sure. I waited with a PR because I wasn't sure this was the best way to go about it. But as long as it works... :-)

I'll get around to it when I'm back at the office over the weekend.

khalwat commented 6 years ago

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

Thanks to @svale