Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MySQL Error #8

Closed
AdamChlan opened this issue Aug 8, 2018 · 11 comments
Closed

MySQL Error #8

AdamChlan opened this issue Aug 8, 2018 · 11 comments

Comments

@AdamChlan
Copy link

AdamChlan commented Aug 8, 2018 •
edited
Loading

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
Copy link
Contributor

khalwat commented Aug 9, 2018

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
Copy link
Author

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
Copy link

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

@joshuabaker joshuabaker mentioned this issue Aug 10, 2018
Closed
@toddpadwick
Copy link

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: SELECT elements.id, elements_sites.siteId, COUNT(*) as count FROM (SELECT elements.idASelementsId, elements_sites.idASelementsSitesId, content.idAScontentIdFROMcraft_elements elementsINNER JOINcraft_entries entriesONentries.id=elements.idINNER JOINcraft_elements_sites elements_sitesONelements_sites.elementId=elements.idINNER JOINcraft_content contentONcontent.elementId=elements.idLEFT JOINcraft_structureelements structureelementsONstructureelements.elementId=elements.idLEFT JOINcraft_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.idORDER BYstructureelements.lft, entries.postDateDESC)subqueryINNER JOINcraft_entries entriesONentries.id=subquery.elementsIdINNER JOINcraft_elements elementsONelements.id=subquery.elementsIdINNER JOINcraft_elements_sites elements_sitesONelements_sites.id=subquery.elementsSitesIdINNER JOINcraft_content contentONcontent.id=subquery.contentIdLEFT JOINcraft_structureelements structureelementsONstructureelements.elementId=subquery.elementsIdLEFT JOINcraft_relations ON elements.id=craft_relations.sourceId WHERE craft_relations.targetIdIN ('7', '8') GROUP BYcraft_relations.sourceIdORDER BYcount DESC

@AdamChlan
Copy link
Author

@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
Copy link
Contributor

khalwat commented Sep 10, 2018

@AdamChlan That link doesn't seem to work?

@AdamChlan
Copy link
Author

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

#8 (comment)

@svale
Copy link
Contributor

svale commented Sep 17, 2018

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
Copy link
Contributor

khalwat commented Oct 4, 2018

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

@svale
Copy link
Contributor

svale commented Oct 5, 2018

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.

@svale svale mentioned this issue Oct 8, 2018
Merged
@khalwat
Copy link
Contributor

khalwat commented Oct 8, 2018

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

Thanks to @svale

@khalwat khalwat closed this as completed Oct 8, 2018
@johnnyhawley johnnyhawley mentioned this issue Dec 6, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@joshuabaker @AdamChlan @svale @khalwat @toddpadwick