php - 如何将两个 Laravel Eloquent 查询合并为一个查询(所以我访问数据库一次而不是两次)

Question

假设我的数据库中有 3 个表。

“我的食谱”、“我的库存”和“成分”。

'my_recipe' 表存储了一个基于'ingredient' 表和配方需要的'quantity' 的raw_id 列表。 'my_inventory' 表存储 raw_id 和 'have_quantity' 的列表。

那么让我们来看看我目前拥有的东西。我有以下 2 个查询:

第一个查询:

$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”总数。

编辑:

我的食谱模型:

'user', 'raw_id', 'quantity'

我的库存模型:

'user', 'raw_id', 'have_quantity'

成分型号:

'raw_id', 'ingredient_name'

注意:在配料模型中，可以有具有相同“ingredient_name”但具有不同“raw_id”的行。

Answer 1

根据我们的聊天对话，我设法获得了一些关于表结构的额外信息，以及需要做什么才能获得想要的结果。

对于那些感兴趣的人，可以找到信息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();