如何将两个Laravel雄辩的查询合并成一个查询(所以我只访问数据库一次，而不是两次)
如何将两个Laravel雄辩的查询合并成一个查询(所以我只访问数据库一次，而不是两次)
提问于 2017-08-05 07:27:21
假设我的数据库中有3个表。
‘'my_recipe'，'my_inventory’和'ingredient‘。
'my_recipe‘表根据’配料‘表和食谱所需的’数量‘来存储raw_id列表。'my_inventory‘表存储raw_id和'have_quantity’的列表。
那么让我们来看看我目前所拥有的。我有以下两个问题：
第一个查询：
$recipe = DB::table('my_recipe as tA')
->leftJoin('ingredient as tB', 'tA.raw_id', '=', 'tB.raw_id')
->select('tA.user as user', 'tA.raw_id as raw_id', 'tA.quantity as quantity',
'tB.ingredient_name as ingredient_name')
->where('user', '=', $user)
->where('raw_id', '=', $raw_id)
->get();第二个查询：
$inventory = DB::table('my_inventory as tA')
->leftJoin('ingredient as tB', 'tA.raw_id', '=', 'tB.raw_id')
->select('tA.user as user', 'tA.have_quantity as have_quantity',
'tB.ingredient_name as ingredient_name')
->where('user', '=', $user)
->get();第一个查询返回如下所示的结果：
{"user":"jack","raw_id":"853","quantity":2,"ingredient_name":"apple"},
{"user":"jack","raw_id":"853","quantity":4,"ingredient_name":"peach"}第二个查询返回如下所示的结果：
{"user":"jack","have_quantity":30,"ingredient_name":"apple"},
{"user":"jack","have_quantity":20,"ingredient_name":"apple"},
{"user":"jack","have_quantity":10,"ingredient_name":"apple"},
{"user":"jack","have_quantity":1,"ingredient_name":"peach"},
{"user":"jack","have_quantity":1,"ingredient_name":"peach"}请注意，在第二个查询结果中，我必须根据“ingredient_name”获得理想输出的配料总和。
如何在一个查询中获得理想的输出？
我的理想输出应该如下所示：
{"user":"jack","raw_id":"853","quantity":2,"ingredient_name":"apple","have_quantity":60},
{"user":"jack","raw_id":"853","quantity":4,"ingredient_name":"peach","have_quantity":2}它基本上是第一个查询的结果，其中包含第二个查询的'have_quantity‘总数。
编辑：
my_recipe模型：
'user', 'raw_id', 'quantity'my_inventory模型：
'user', 'raw_id', 'have_quantity'配料模型：
'raw_id', 'ingredient_name'注:在配料模型中，可能存在具有相同'ingredient_name‘但具有不同'raw_id’的行。
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-11-17 21:54:24
根据我们的聊天对话，我设法获得了一些关于表结构的额外信息，以及需要做些什么才能获得想要的结果。
对于那些感兴趣的人，可以在here上找到这些信息
无论如何，我最终创建了这样的查询：
SELECT
my_recipe.user AS user,
my_recipe.raw_id AS raw_id,
my_recipe.quantity AS quantity,
ingredient.ingredient_name AS ingredient_name,
IFNULL(SUM(my_inventory.have_quantity),0) AS have_quantity
FROM my_recipe
LEFT JOIN ingredient USING(raw_id)
LEFT JOIN ingredient AS ingredients USING(ingredient_name)
LEFT JOIN my_inventory ON my_inventory.raw_id = ingredients.raw_id
WHERE my_recipe.recipe_id = 853
AND my_recipe.user = 'jack'
AND my_inventory.user = 'jack'
GROUP BY ingredient_name;现在转换为所需的结构：
$inventory = DB::table('my_recipe')
->leftJoin('ingredient', 'my_recipe.raw_id', '=', 'ingredient.raw_id')
->leftJoin('ingredient AS ingredients', 'ingredient.ingredient_name', '=', 'ingredients.ingredient_name')
->leftJoin('my_inventory', 'my_inventory.raw_id', '=', 'ingredients.raw_id')
->select(DB::raw('my_recipe.user AS user,my_recipe.raw_id AS raw_id,my_recipe.quantity AS quantity,ingredient.ingredient_name AS ingredient_name,IFNULL(SUM(my_inventory.have_quantity),0) AS have_quantity'))
->where('my_recipe.recipe_id', '=', $recipe_id)
->where('my_recipe.user', '=', $user)
->where('my_inventory.user', '=', $user)
->groupBy('ingredient_name')
->get();Stack Overflow用户
发布于 2017-08-05 08:01:17
也许这可以解决你的问题
//replace count by sum
$inventory = DB::table('my_inventory as tA')
->leftJoin('ingredient as tB', 'tA.raw_id', '=', 'tB.raw_id')
->leftJoin('my_recipe as tC', 'tC.raw_id', '=', 'tB.raw_id')
->select(DB::raw('tA.user as user, tB.ingredient_name as ingredient_name, SUM(tA.have_quantity) have_quantity'))
->where('user', '=', $user)
->groupBy('tB.ingredient_name, tA.user')
->get();Stack Overflow用户
发布于 2017-11-20 16:31:44
让我们试试这个：
$result = DB::table('ingredient as ing')
->rightJoin('my_recipe as rcp', 'ing.raw_id', '=', 'rcp.raw_id')
->rightJoin('my_inventory as inv', 'ing.raw_id', '=', 'inv.raw_id')
->select(DB::raw('
rcp.user as user,
ing.ingredient_name as ingredient_name,
rcp.have_quantity quantity,
SUM(inv.have_quantity) have_quantity
'))
->where('rcp.user', '=', $user)
->where('rcp.raw_id', '=', $raw_id)
->groupBy('rcp.user, ing.ingredient_name')
->get();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45516774
复制相关文章
相似问题
腾讯云开发者
