[FIX] Changes SQL Query for User Preview

[dhoffmann1979]

Error received: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'typo3.fe_users.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I removed the group by, because there will be only multiple same email adresses if there are multiple users with the same address. I think this is a problem with data quality on the side of the website

[einpraegsam]

Hi Daniel,

this was especially requested from both "Flo". Luxletter should support multi receiver sending, but when a user is included in more then only one usergroup, he should receive only one Newsletter.

What if you keep the group by annotation, but adds ",email" to the select statement: $sql = 'select *,email from ' . User::TABLE_NAME;

Is it still a problem with sql_mode=only_full_group_by?

Alex

[einpraegsam]

Update: This is not so easy to fix (for me) at the moment. The failure is, that all fields from the select should also be listed in the group by statement. This is not a real solution, because there are a lot of fields and they can differ from TYPO3 instance to instance. I played a bit but without solution yet.

Possible workarrounds at the moment:

• Select from only some fields and add them to group by
• Group by via PHP after the statement was fired

[einpraegsam]

Errormessage: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'typo3.fe_users.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Related to default setting in MySQL only_full_group_by. This was not that easy to fix, because we want to send only one newsletter per receiver. An email address can be used in more than only one fe_users record. In addition the fe_users record can also be related to two or even more usergroups that should receive a single newsletter. So we need a grouping function to get single email addresses. And in a perfect world this would be done via SQL and not via PHP to prevent future performance and memory issues. So I played and read a lot but I don't see a solution to still use "group by" even with any_value() or max() or such things. It's not possible to use "select * ... group by field" what is needed to build correct filled extbase models (with group by when only_full_group_by is set). We now go for a grouping function in PHP - yes that's a pitty - to solve the issue. Because we changed QueryResult to array, more locations were updated with this change now

[dhoffmann1979]

Hi Daniel,

this was especially requested from both "Flo". Luxletter should support multi receiver sending, but when a user is included in more then only one usergroup, he should receive only one Newsletter.

What if you keep the group by annotation, but adds ",email" to the select statement: $sql = 'select *,email from ' . User::TABLE_NAME;

Is it still a problem with sql_mode=only_full_group_by?

Alex

The Query used is a simple select without a join on the fe_users table, in my opinion the only way to get a email twice in the result is when there are two entries in fe_users with the same address. If one entry is in more then one selected group it will only be once in the result. So my MR won't be a problem if the user is in more then one group.

[einpraegsam]

Thx, I already pushed the solution to develop and after that we released a new minor version. This should be resolved now.