使用GROUP_CONCAT进行LEFT JOIN的奇怪结果

I want to get some properties for each of my users. Each user can have multiple devices and be registered to multiple projects, and of course vice versa (so it's a many-to-many relationship).

我想为每个用户获取一些属性。每个用户可以拥有多个设备并注册到多个项目，当然反之亦然（因此它是多对多关系）。

I have three relevant tables for this: The table linking users to devices, the table linking users to projects, and the devices table. I have left the users table out of this for now, in order to simply the query (so I just iterate the query for each user that I get from a separate query).

我有三个相关的表：将用户链接到设备的表，将用户链接到项目的表和设备表。我暂时离开了这个用户表，以便简单地查询（所以我只是迭代查询我从单独的查询得到的每个用户）。

What I want is to be able to display a list of devices for each user, and for each user/device pair—a list of projects. This works with the following query:

我想要的是能够为每个用户以及每个用户/设备对显示项目列表 - 项目列表。这适用于以下查询：

SELECT `user_devices`.*, `devices`.`id`, `devices`.`type`, `devices`.`number`, `user_projects`.`project_id` AS `projects`
        FROM `user_devices`
        LEFT JOIN `devices` ON `user_devices`.`device_id` = `devices`.`id`
        LEFT JOIN `user_projects` ON `user_devices`.`user_id` = `user_projects`.`user_id`
            AND `user_devices`.`device_id` = `user_projects`.`device_id`
        WHERE `user_devices`.`user_id` = {$user_id};

However, it then includes a row for each project even with the same user/device combination, which requires post-processing (I'm using PHP), which is annoying. I therefore decided to utilize GROUP_CONCAT like so:

然而，它甚至包含相同的用户/设备组合的每个项目的行，这需要后处理（我使用PHP），这很烦人。因此我决定像这样使用GROUP_CONCAT：

SELECT `user_devices`.*, `devices`.`id`, `devices`.`type`, `devices`.`number`, GROUP_CONCAT(`user_projects`.`project_id`) AS `projects`
        FROM `user_devices`
        LEFT JOIN `devices` ON `user_devices`.`device_id` = `devices`.`id`
        LEFT JOIN `user_projects` ON `user_devices`.`user_id` = `user_projects`.`user_id`
            AND `user_devices`.`device_id` = `user_projects`.`device_id`
        WHERE `user_devices`.`user_id` = {$user_id};

However, what this does now is return just one row no matter what, including NULL rows (seemingly ignoring the LEFT JOIN which is supposed to only take rows that include values from the first table). It does include the list of projects correctly, but lists just one device per user even if that user has multiple devices.

但是，现在这样做只返回一行，包括NULL行（似乎忽略了LEFT JOIN，它应该只占用包含第一个表值的行）。它确实包括项目列表，但每个用户只列出一个设备，即使该用户有多个设备。

I think it might be my misunderstanding of exactly how LEFT JOIN works with group functions, but I can't figure it out.

我认为这可能是我对LEFT JOIN如何使用组函数的误解，但我无法弄明白。

1 个解决方案

#1

Turns out it was indeed my misunderstanding of GROUP_CONCAT. Turns out it can't work without some corresponding GROUP BY.

事实证明这确实是我对GROUP_CONCAT的误解。事实证明，没有一些相应的GROUP BY，它就无法工作。

Since in this case logically the projects need to be shown by device, it can be grouped by devices.number:

因为在这种情况下逻辑上项目需要由设备显示，所以它可以按devices.number分组：

GROUP BY `devices`.`number`

#1