所以,我得到了一些数据,这些数据旨在显示某种成分(成分表),并且可以有不同的名称(ingredient_name表)。以下是一些模拟数据:
ingredient
id | mock_name
-------------
1 | "The First Ingredient"
2 | "The second Ingredient"
ingredient_name
--------------
id | ingredient_id | name
1 | 2 | "Milk"
2 | 2 | "Mjolk"
3 | 1 | "Steak"
因此,我想要发生的是用户插入一个成分ID,然后输出成分数据(在本例中为"ingredient.mock_name"和成分别名(ingredient_name.name)。
我已经用Symfony/Doctrine设置了所有的实体,存储库等。这就是我现在拥有的:
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
'SELECT I, IGN.name
FROM AppBundle:IngredientName IGN
INNER JOIN AppBundle:Ingredient I WITH IGN.ingredientID=I.id
WHERE IGN.ingredientID=:ingID'
)->setParameters(['ingID' => 2]);
$ingredients = $query->getResult();
这给了我这个结果:
{
"0":{
"id":2,
"mock_name":"The second Ingredient",
},
"name":"Mjolk"
}
我希望得到的是这样的:
{
"0":{
"id":2,
"mock_name":"The second Ingredient",
},
"name":"Mjolk",
"name":"Milk" <---- This value also
}
SQL联接从来都不是我的强项,用原则思考并不能让事情变得更容易。这里有人能为我指出正确的方向吗?
我想要的是这样的东西,但如果可能的话,只有一个 SQL 语句:
$query = $em->createQuery(
'SELECT IGN.name
FROM AppBundle:IngredientName IGN
INNER JOIN AppBundle:Ingredient I WITH IGN.ingredientID=I.id
WHERE IGN.ingredientID=:ingID'
)->setParameters(['ingID' => $ingredientIdentifier]);
$ingredientsNames = $query->getResult();
$query = $em->createQuery(
'SELECT I
FROM AppBundle:IngredientName IGN
INNER JOIN AppBundle:Ingredient I WITH IGN.ingredientID=I.id
WHERE IGN.ingredientID=:ingID'
)->setParameters(['ingID' => $ingredientIdentifier]);
$ingredients = $query->getResult();
$ingredients = array_merge($ingredients, ['ingredientNames' => $ingredientsNames]);
返回:
{
"0":{
"id":2,
"mock_name":"The second Ingredient"
},
"ingredientNames":[
{
"name":"Milk"
},
{
"name":"Mjolk"
}
]
}
我对Symfony/Doctrine不是很熟悉,但是看看你的json结果,不可能让同一个键重复两次。
我相信"名称":"牛奶"起初被检索到,但后来被"名称"覆盖:"雷神之锤"。