php - 如何在 LEFT JOIN 中返回多行

Question

我有一种情况，假设我正在尝试获取有关某些食物的信息。然后我需要显示所有信息以及该食物中的所有成分。

通过我的查询，我得到了一个数组中的所有信息，但只有第一个成分...

   myFoodsArr = 
        [0]
          foodDescription = "the description text will be here"
          ratingAverage = 0
          foodId = 4
          ingredient = 1
          ingAmount = 2
          foodName = "Awesome Food name"
          typeOfFood = 6
          votes = 0

我想得到这样的东西......

        myFoodsArr = 
            [0]
              foodDescription = "the description text will be here"
              ratingAverage = 0
              foodId = 4
              ingArr = {ingredient: 1, ingAmount: 4}, {ingredient: 3, ingAmount: 2}, {ingredient: 5, ingAmount: 1}
              foodName = "Awesome Food name"
              typeOfFood = 6
              votes = 0

这是我现在正在处理的查询。我如何调整它以返回食物 ID 4，然后还获取该食物的所有成分？同时做其他事情，比如获得该食物的平均评分？

谢谢!

   SELECT a.foodId, a.foodName, a.foodDescription, a.typeOfFood, c.ingredient, c.ingAmount, AVG(b.foodRating) AS ratingAverage, COUNT(b.foodId) as tvotes 
FROM `foods` a 
LEFT JOIN `foods_ratings` b 
   ON a.foodId = b.foodId 
LEFT JOIN `foods_ing` c 
   ON a.foodId=c.foodId 
WHERE a.foodId=4

编辑:

Catcall 引入了这个我从未听说过的“子查询”概念，所以我正在努力使它起作用，看看我是否可以在 1 个查询中轻松地做到这一点。但我只是不断得到错误的返回。这就是我没有运气的尝试..

//I changed some of the column names to help them be more distinct in this example

SELECT a.foodId, a.foodName, a.foodDescription, a.typeOfFood, AVG(b.foodRating) AS ratingAverage, COUNT(b.foodId) as tvotes 
FROM foods a 
LEFT JOIN foods_ratings b ON a.foodId = b.foodId 
LEFT JOIN (SELECT fId, ingredientId, ingAmount 
                 FROM foods_ing 
                 WHERE fId = 4 
                 GROUP BY fId) c ON a.foodId = c.fId 
           WHERE a.foodId = 4";

EDIT 1 more thing related to ROLANDS GROUP_CONCAT/JSON Idea as a solution 4 this

我正在尝试确保我发送回我的 Flash 项目的 JSON 字符串已准备好被正确解析 无效的 JSON 解析输入。 不断弹出..

所以我认为我需要在正确的位置正确使用所有双引号。

但在我的 MySQL 查询字符串中，我试图转义双引号，但随后它使我的 mySQL 变量无法工作，例如......

如果我这样做..

GROUP_CONCAT('{\"ingredient\":', \"c.ingredient\", ',\"ingAmount\":', \"c.ingAmount\", '}')`

我明白了...

{"ingredient":c.ingredient,"ingAmount":c.ingAmount},{"ingredient":c.ingredient,"ingAmount":c.ingAmount},{"ingredient":c.ingredient "ingAmount":c.ingAmount}

如何在不破坏 mysql 的情况下使用所有双引号使 JSON 格式正确？

Answer 1

这应该可以解决问题:

SELECT    food_ingredients.foodId
,         food_ingredients.foodName
,         food_ingredients.foodDescription
,         food_ingredients.typeOfFood
,         food_ingredients.ingredients
,         AVG(food_ratings.food_rating) food_rating
,         COUNT(food_ratings.foodId)    number_of_votes 
FROM      (
           SELECT    a.foodId
           ,         a.foodName
           ,         a.foodDescription
           ,         a.typeOfFood
           ,         GROUP_CONCAT(
                         '{ingredient:', c.ingredient,
                     ,   ',ingAmount:', c.ingAmount, '}'
                     ) ingredients
           FROM      foods a 
           LEFT JOIN foods_ing c 
           ON        a.foodsId = c.foodsId 
           WHERE     a.foodsId=4
           GROUP BY  a.foodId
          ) food_ingredients
LEFT JOIN food_ratings
ON        food_ingredients.foodId = food_ratings.foodId
GROUP BY  food_ingredients.foodId 

请注意，您要执行的查询类型在任何基于 SQL 的数据库中都不是微不足道的。

主要问题是您有一个主数据(食物)和两个细节(成分和评级)。因为这些细节彼此不相关(除了与 master 之外)，它们彼此形成笛卡尔积(仅受它们与 master 的关系约束)。

上面的查询通过两个步骤解决了这个问题:首先，连接到第一个细节(成分)并聚合细节(使用 group_concat 使所有相关成分行成为一行)，然后将该结果连接到第二个细节(评级)并再次聚合。

在上面的示例中，成分以结构化字符串的形式返回，与您的示例中出现的完全一样。如果你想在 PHP 中访问数据，你可以考虑添加更多的语法使其成为有效的 JSON 字符串，这样你就可以使用 php 函数 json_decode() 将其解码为数组:http://www.php.net/manual/en/function.json-decode.php

为此，只需将行更改为:

CONCAT(
    '['
,   GROUP_CONCAT(
        '{"ingredient":', c.ingredient
    ,   ',"ingAmount":', c.ingAmount, '}'
    )
,   ']'
)

(假设 ingredient 和 ingAmount 是数字；如果它们是字符串，您应该将它们加双引号，并转义出现在字符串值中的任何双引号)

如果您为 group_concat_max_len 服务器变量保留默认设置，则将成分与 GROUP_CONCAT 串联可能会导致问题。缓解该问题的一种简单方法是将其设置为任何结果的最大理论大小:

SET group_concat_max_len = @@max_allowed_packet;

您可以在打开与 mysql 的连接后执行一次，然后它将在该 session 期间生效。或者，如果您拥有 super 权限，则可以全面更改整个 MySQL 实例的值:

SET GLOBAL group_concat_max_len = @@max_allowed_packet;

您还可以在 my.cnf 或 my.ini 中添加一行，以将 group_concat_max_lenght 设置为某个足够大的任意静态值。见 http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_group_concat_max_len