bug: GROUP BY fails on strict MySQL ONLY_FULL_GROUP_BY #59

New Issue

2026-06-28T15:36:28Z

jmiller commented

2026-06-28 15:36:28 +00:00

Summary

The site LocationsModel uses GROUP BY a.id when joining to the categories junction table, but selects non-aggregated columns (title, address, etc.) that are not in the GROUP BY clause. This works on MySQL with ONLY_FULL_GROUP_BY disabled (the default on many hosts) but fails on strict mode.

Error

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column...

Fix

Either:

Add all selected columns to GROUP BY (verbose but correct)
Use a subquery/EXISTS instead of JOIN + GROUP BY
Use ANY_VALUE() wrapper on non-grouped columns

Option 2 (EXISTS subquery for category filtering) is cleanest and avoids GROUP BY entirely.

Priority

P5 — only affects strict MySQL configurations, but should be fixed for compatibility.

## Summary The site `LocationsModel` uses `GROUP BY a.id` when joining to the categories junction table, but selects non-aggregated columns (title, address, etc.) that are not in the GROUP BY clause. This works on MySQL with `ONLY_FULL_GROUP_BY` disabled (the default on many hosts) but fails on strict mode. ## Error ``` Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column... ``` ## Fix Either: 1. Add all selected columns to GROUP BY (verbose but correct) 2. Use a subquery/EXISTS instead of JOIN + GROUP BY 3. Use `ANY_VALUE()` wrapper on non-grouped columns Option 2 (EXISTS subquery for category filtering) is cleanest and avoids GROUP BY entirely. ## Priority P5 — only affects strict MySQL configurations, but should be fixed for compatibility.

jmiller commented

2026-06-28 15:36:31 +00:00

Branch created: feature/59-bug-group-by-fails-on-strict-mysql-only-

git fetch origin
git checkout feature/59-bug-group-by-fails-on-strict-mysql-only-

Branch created: [`feature/59-bug-group-by-fails-on-strict-mysql-only-`](https://git.mokoconsulting.tech/MokoConsulting/MokoSuiteStoreLocator/src/branch/feature/59-bug-group-by-fails-on-strict-mysql-only-) ```bash git fetch origin git checkout feature/59-bug-group-by-fails-on-strict-mysql-only- ```

jmiller commented

2026-06-28 15:51:30 +00:00

Fixed in site/src/Model/LocationsModel.php:

Replaced INNER JOIN + implicit GROUP BY with EXISTS subquery for category filtering
No GROUP BY needed — each location appears once regardless of how many categories match
Compatible with ONLY_FULL_GROUP_BY strict SQL mode

Fixed in `site/src/Model/LocationsModel.php`: - Replaced `INNER JOIN` + implicit GROUP BY with `EXISTS` subquery for category filtering - No GROUP BY needed — each location appears once regardless of how many categories match - Compatible with `ONLY_FULL_GROUP_BY` strict SQL mode

jmiller closed this issue

2026-06-28 15:52:00 +00:00

jmiller referenced this issue from a commit

2026-06-28 16:22:52 +00:00

fix: resolve all open issues — detail map, clustering, CSP, GROUP BY, cleanup (#34 #57 #58 #59 #60 #61)

jmiller referenced this issue

2026-06-28 18:49:29 +00:00

v1.2.0 — Categories, API, hardening, polish #62

Sign in to join this conversation.