php - 在单个查询中填充一对多的结果

Question

我有两个表:activity 和 tag。一个 activity 有很多 tags。我需要优化我们当前的设置，我们在表中找到所有事件，然后遍历每个事件以找到所有标签。最后我们将内容处理为 JSON。

这是一个例子:

-- Query that we execute one time
SELECT `id`, `name`
FROM `activity`

-- Query that we execute N times
SELECT `id`, `name`, `color`
FROM `tag`
WHERE `tag`.`activityId` = $activityId

所以我们的最终代码看起来像这样:

$result = mysqli_query("
    SELECT `id`, `name`
    FROM `activity`
");

$data = array();

for ($i=0; $i<mysqli_num_rows($result); $i++) {
    $row = mysqli_fetch_assoc($result);

    $data[$i]["activityId"] = $row["id"];
    $data[$i]["name"] = $row["name"];
    $data[$i]["tags"] = array();

    $activityId = $row["id"];

    $resultTags = mysqli_query("
        SELECT `id`, `name`, `color`
        FROM `tag`
        WHERE `tag`.`activityId` = $activityId
    ");

    for ($j=0; $j<mysqli_num_rows($resultTags); $j++) {
        $row = mysqli_fetch_assoc($resultTags);

        $data[$i]["tags"][$j]["name"] = $row["name"];
        $data[$i]["tags"][$j]["color"] = $row["color"];
    }
}

PS:这是一个伪代码，我们的查询和表要复杂得多，但这基本上就是我们问题的本质。

是否有可能使用内部查询来实现这一点，或者甚至更好，只使用连接？

由于我们需要填充大约 5 个表，因此我们最终为每个事件执行 5 个查询。

谢谢。

Answer 1

当然只是JOIN 事件表上的标签表。

您的查询应如下所示。

SELECT activity.id, activity.name, tag.name AS tagName, tag.color AS tagColor
FROM activity
JOIN tag ON tag.activityId = activity.id;

事件表的主键是id的想法并且每一行都是唯一的(即名称永远不会为任何给定的 activity.id 更改)，那么即使没有标签， JOIN 也是安全的。我们至少会回复 N N 所在的行是tag.activityId的数量具有相应 id 的行在activity .

所以在 PHP 中，您可以像这样使用单个查询来构建数组。

$data = [];
foreach($result as $row) {

    $tags = ["name" => $row["tagName"], "color" => $row["tagColor"]];

    if (isset($data[$row["id"]])) {
        $data[$row["id"]]["tags"][] = $tags; 
    } else {
        $data[$row["id"]] = [
            [
                "id"   => $row["id"],
                "name" => $row["name"],
                "tags" => [$tags],
            ],
        ];
    }

}

因为事件表中的信息永远不会改变，所以我们唯一需要附加的就是标签，我们通过检查isset($data[$row["id"]])来完成。在循环内。

所以从这个 SQLFiddle您可以看到两个表的工作示例，其中我们在 activity 中有 4 行。和 tag 中的 6 行 table 。这给了我们总共 6 行。由于事件表中的一个 id 没有任何标签，因此它被排除在结果集中。要返回带有 0 个标签的行，您可以使用 LEFT JOIN相反 like this .

SELECT activity.id, activity.name,
       tag.activityId AS tagId, tag.name AS tagName, tag.color AS tagColor
FROM activity
LEFT JOIN tag ON tag.activityId = activity.id;

这意味着我们得到空标签行的空值。所以我们可以修改 PHP 看起来像这样。

$data = [];
foreach($result as $row) {

    $tags = empty($row["tagId"]) ?: ["name" => $row["tagName"], "color" => $row["tagColor"]];

    if (isset($data[$row["id"]]) && $tags) {
        $data[$row["id"]]["tags"][] = $tags; 
    } else {
        $data[$row["id"]] = [
            [
                "id"   => $row["id"],
                "name" => $row["name"],
                "tags" => $tags ? [$tags] : [],
            ],
        ];
    }

}

因此您使用 json_encode 从 PHP 最终输出，以及 SQLFiddle 中的示例数据，应该看起来像这样。

    "1": {        "id": 1,        "name": "foo",        "tags": [            {                "tagName": "tag-a",                "tagColor": "red"            },            {                "tagName": "tag-b",                "tagColor": "green"            },            {                "tagName": "tag-c",                "tagColor": "blue"            }        ]    },    "2": {        "id": 2,        "name": "bar",        "tags": [            {                "tagName": "tag-a",                "tagColor": "orange"            },            {                "tagName": "tag-b",                "tagColor": "red"            }        ]    },    "3": {        "id": 3,        "name": "baz",        "tags": [            {                "tagName": "tag-a",                "tagColor": "purple"            }        ]    },    "4": {        "id": 4,        "name": "quix",        "tags": []    }}