我有一个情况,让我们说,我试图获得一些食物的信息。然后我需要显示所有的信息加上食物中的所有成分。
通过我的查询,我得到了数组中的所有信息,但只有第一个成分。。。
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";
再编辑一个与ROLANDS GROUP_CONCAT/JSON Idea相关的内容作为解决方案4
我正试图确保我发送回Flash项目的JSON字符串已经准备好被正确解析,而
Invalid JSON parse input.
不断出现。。所以我想我需要把所有的双引号放在正确的位置。
但在我的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}
如何使用所有双引号使JSON格式正确,而不破坏mysql?
最佳答案
这应该可以做到:
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的数据库中,您要执行的查询类型都不是微不足道的。
主要的问题是你有一个主人(食物)和两个细节(配料和评级)。因为这些细节彼此不相关(除了与母版的关系),所以它们彼此形成笛卡尔积(仅受它们与母版的关系约束)。
上面的查询通过两步来解决这个问题:首先,连接到第一个细节(成分)并聚合细节(使用group_concat生成所有相关成分行的一行),然后将结果连接到第二个细节(评级)并再次聚合。
在上面的示例中,成分以结构化字符串的形式返回,与您的示例中显示的完全相同。如果您想访问PHP中的数据,可以考虑添加更多的语法,使其成为有效的JSON字符串,这样就可以使用PHP函数将其解码为数组
为此,只需将行更改为:
CONCAT(
'['
, GROUP_CONCAT(
'{"ingredient":', c.ingredient
, ',"ingAmount":', c.ingAmount, '}'
)
, ']'
)
(这假定
json_decode()
和ingredient
是数字;如果它们是字符串,则应双引号,并转义字符串值中出现的任何双引号)如果您保留
ingAmount
服务器变量的默认设置,则成分与GROUP_CONCAT
的连接可能会导致问题。减轻这个问题的一个简单方法是将其设置为任何结果的最大理论尺寸:SET group_concat_max_len = @@max_allowed_packet;
您可以在打开到mysql的连接后执行一次此操作,然后它将在会话期间生效。或者,如果您拥有超级权限,则可以全面更改整个MySQL实例的值:
SET GLOBAL group_concat_max_len = @@max_allowed_packet;
您还可以在my.cnf或my.ini中添加一行,将group_concat_max_lenght设置为足够大的任意静态值。见http://www.php.net/manual/en/function.json-decode.php