Closed
Description
This is a (multiple allowed):
-
bug
-
enhancement
-
feature-discussion (RFC)
-
CakePHP Version: 4.1.0-RC1
-
Platform and Target: PHP 7.4, MySQL 5.7
What you did
// Put something into a things table
INSERT INTO `things` (`id`, `title`)
VALUES
(40, 'Test');
// Put stuff into a platforms table
INSERT INTO `platforms2` (`id`, `name_slug`)
VALUES
(12, 'pc'),
(13, 'mac');
// Add a link between the two
INSERT INTO `things_platforms2` (`game_id`, `platform_id`)
VALUES
(40, 12),
(40, 13);
// Try to match wherever a Thing is either pc or mac
ThingsTable
$query = $this->getTable('Things')
->find()
->matching(
'Platforms2',
fn($q) => $q->where([
'Platforms2.name_slug IN' => ['pc', 'mac'],
])
);
What happened
This creates the sql (snipped db name) which, when run, returns the same record twice.
SELECT `Things`.`id` AS `Things__id`, `Things`.`title` AS `Things__title`, `ThingsPlatforms2`.`thing_id` AS `ThingsPlatforms2__thing_id`, `ThingsPlatforms2`.`platforms2_id` AS `ThingsPlatforms2__platforms2_id`, `Platforms2`.`id` AS `Platforms2__id`, `Platforms2`.`name_slug` AS `Platforms2__name_slug` FROM `things` `Things` INNER JOIN `things_platforms2` `ThingsPlatforms2` ON `Things`.`id` = (`ThingsPlatforms2`.`thing_id`) INNER JOIN `platforms2` `Platforms2` ON (`Platforms2`.`name_slug` in ('pc','mac') AND `Platforms2`.`id` = (`ThingsPlatforms2`.`platforms2_id`))
What you expected to happen
I'd expect that adding the following, that a DISTINCT would be added to the select, but it adds a GROUP BY, which doesn't remove duplicates.
->distinct(['Things.id', 'ThingsPlatforms2.platforms2_id'])
The SQL that would remove duplicates would be
SELECT DISTINCT Things.id, Things.* FROM `things` `Things` INNER JOIN `things_platforms2` `ThingsPlatforms2` ON `Things`.`id` = (`ThingsPlatforms2`.`thing_id`) INNER JOIN `platforms2` `Platforms2` ON (`Platforms2`.`name_slug` in ('pc','mac') AND `Platforms2`.`id` = (`ThingsPlatforms2`.`platforms2_id`))
Is there a reason distinct() adds a GROUP BY clause and not a DISTINCT clause as someone would expect? Even the doc in Query->distinct says:
Adds a `DISTINCT` clause to the query to remove duplicates from the result set.
I've tried this, but it doesn't work, so right now I have no idea what to do. enableAutoFields also adds too many fields, so I have to remove that and manually add all the fields the query needs.
->enableAutoFields()
->select(['Things__id' => 'DISTINCT(Things.id)'])