mysql - 如何在 pdo 查询中进行内部选择？

Question

我不确定如何准确命名这个问题，而且由于我无法找到更好的标题，因此我无法搜索太多以前可能被问过的相同问题。

我的数据库中有一对多关系，数据分布在几个表中。

public function get_recipe_data($id){
    $sth = $this->db->prepare('SELECT * FROM
                               recipe as recipe
                               LEFT JOIN recipe_introduction as introduction
                               ON recipe.id = introduction.recipe_id
                               LEFT JOIN recipe_ingredients as ingredients
                               ON recipe.id = ingredients.recipe_id
                               LEFT JOIN recipe_ingredients_notes as notes
                               ON recipe.id = notes.recipe_id
                               LEFT JOIN recipe_macros as macros
                               ON recipe.id = macros.recipe_id
                               WHERE recipe.id = :id
    ');
    $sth->bindValue(':id', $id, PDO::PARAM_INT);
    $sth->execute();

    return $sth->fetch(PDO::FETCH_ASSOC);
}

public function convertCategoryId($category_id){
    $sth = $this->db->prepare('SELECT name FROM recipe_categories WHERE id = :category_id');
    $sth->bindValue(':category_id', $category_id, PDO::PARAM_INT);
    $sth->execute();

    return $sth->fetch(PDO::FETCH_ASSOC);
}

在配方表中，有一列包含类别名称的 id，并引用另一个表。

配方表

+----+-------+-------------+
| id | title | category_id |
+--------------------------+
| 1  |  ...  |      1      |
+----+-------+-------------+
| 2  |  ...  |      3      |
+----+-------+-------------+

我还有另一张 table

食谱类别

+----+------------+
| id |    name    |
+----+------------+
| 1  |  breakfast |
+----+------------+
| 2  |    lunch   |
+----+------------+
| 3  |   dinner   |
+----+------------+
| .. |    ...     |
+----+------------+

现在我必须返回上面第一个查询的结果，然后一旦获得结果，我就在类中运行另一个方法，传递第一个查询中的category_id，然后将其与recipe_categories匹配，但是效率太低了。

有没有办法让我能够从第一个查询中直接选择名称？

Answer 1

只需将类别表连接到配方表，这样您就可以在单个查询中访问类别名称。

SELECT
      *
FROM recipe AS recipe
INNER JOIN recipe_categories AS rcat ON recipe.category_id = rcat.id
LEFT JOIN recipe_introduction AS introduction ON recipe.id = introduction.recipe_id
LEFT JOIN recipe_ingredients AS ingredients ON recipe.id = ingredients.recipe_id
LEFT JOIN recipe_ingredients_notes AS notes ON recipe.id = notes.recipe_id
LEFT JOIN recipe_macros AS macros ON recipe.id = macros.recipe_id
WHERE recipe.id = id

注意。虽然 PHP 用户通常的做法是使用“select *”，但最佳做法是您应该指定您想要返回的每一列，而不是随意处理。