SQL error related to GROUP BY

[icreatestuff]

Hello,

I'm getting the following SQL error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test-site.elements_sites.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The stack trace seems to indicate it's originating from line 103 in the Similar.php service which is
$results = $query->all();. I suspect it's something in the setup of that $query but couldn't see anywhere that GROUP By was being used.

In case it helps the full query that's been compiled is

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`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=9)
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`entries`.`sectionId`='15') AND ((elements.id != '2782') AND (`relations`.`targetId` IN ('2777', '2778'))) AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2020-02-24 18:01:00') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2020-02-24 18:01: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 `structureelements`.`lft`, `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`
INNER JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=9)
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE `relations`.`targetId` IN ('2777', '2778')
GROUP BY `relations`.`sourceId`, `elements`.`id`
ORDER BY `count` DESC
LIMIT 4

which is being built from this twig code

{% set ids = entry.tags.ids() %}
{% set limitCriteria = craft.entries.section('news').with(['coverImage', 'person']).limit(4) %}
{% set similarEntries = craft.similar.find({ element: entry, context: ids, criteria: limitCriteria }) %}

Craft v3.4.8
Similar v1.0.6
MySQL 5.7.26
PHP 7.2.20

[khalwat]

mmmm not sure what the issue is here; PRs would be welcome!

[goodmixer]

Having same issue locally. Disabling strict mode on MySQL got rid of the error but not always possible on shared hosting environments.

[robmcfadden]

Having issue as well. Haven't had time to dig into the Craft CMS changelog, but Similar broke right after upgrading to the latest. Was working just before upgrading to the latest. I can post more details if needed, but a bit busy at the moment.

[robmcfadden]

Figured out how to work around my problem.

Was getting... Column not found: 1054 Unknown column 'structureelements.structureId' in 'group statement'

And had this code...

{% set ids = entry.productCategories.ids()|merge(entry.learnCategories.ids()) %}
{% set limitCriteria = craft.entries.section('articles').limit(4) %}
{% set relatedArticles = craft.similar.find({ element: entry, context: ids, criteria: limitCriteria }) %}

Removing the section parameter from limitCriteria got rid of the error. I can make this work for my needs. I'm realizing now that this wasn't actually the same error that was reported here. Sorry if I bloated the comments unnecessarily. :/

[icreatestuff]

@robmcfadden No worries! I actually have a very similar setup in the template so tried also removing the section parameter but it had no affect, I still got the error.

@khalwat It's a bit over my head to be honest, is there anything that needs flagging up with the Craft dev team on this?

[jangidgirish]

Same issue with me.

[khalwat]

Happy to have a PR to fix the issue!

[icreatestuff]

Just a quick update to say this remains an issue in Craft v3.4.10.1

Interestingly on MySQL 5.6.47 my code is also broken unless I remove the .section parameter (as @robmcfadden suggested). On MySQL 5.7.26 it's broken with or without that parameter.

[brianlarson]

I just ran into this as well after updating from Craft 3.3 to Craft 3.4. I'd PR if my brain was bigger but it's a pea. Happening locally with MySQL v5.7.22.

{% set ids = entry.blogCategories.ids()|merge(entry.blogTags.ids()) %}
{% set entries = craft.similar.find({ 
  element: entry, 
  context: ids, 
  criteria: craft.entries.limit(4)
}) ?? null %}

[agrigg]

I'm getting the same error as @robmcfadden and removing the section parameter fixed it for me as well.

[khalwat]

I've been unusually busy lately, and this doesn't look like a simple problem to solve. Would love it if anyone from the community is able to help out to diagnose and fix this.

[klick]

Had the same error and changing the section parameter from .section('section') to .sectionId(2) solved it for me.

[brianlarson]

Sill having this issue on Similar v1.0.6 but it looks like in the changelog it was fixed? MySQL v5.7.22, Craft 3.4.20. I'd be down this being a paid plugin fer sure! 👍

[khalwat]

Still accepting PRs! @brianlarson have you tried what @klick mentioned above?

[brianlarson]

I did but unfortunately it didn't werk in my case!

[joshuabaker]

A combination of running the good ol’ SET SESSION sql_mode trick (below via a module) and setting the sectionId instead of section seems to work for us.

try {
    Craft::$app
        ->getDb()
        ->createCommand('SET SESSION sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";')
        ->execute();
} catch (Exception $exception) {
    // do nothing
}

There might be an official Craft way of doing this since initSql was removed.

[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...

[brettburwell]

@khalwat Really appreciate you taking a look at this. I can confirm that the update corrected the issue on my end.

[khalwat]

Thanks, will let a few others hopefully verify before releasing... anyone able to test on Postgres?

[khalwat]

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