社区首页 >问答首页 >如何将两个Laravel雄辩的查询合并成一个查询(所以我只访问数据库一次，而不是两次)

问如何将两个Laravel雄辩的查询合并成一个查询(所以我只访问数据库一次，而不是两次)
EN

Stack Overflow用户

提问于 2017-08-04 23:27:21

回答 3查看 1.1K关注 0票数 1

假设我的数据库中有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’的行。

laravel-query-builder

php

mysql

laravel

eloquent

回答 3

Stack Overflow用户

回答已采纳

发布于 2017-11-17 13: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();

票数 2

Stack Overflow用户

发布于 2017-08-05 00: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();

票数 0

Stack Overflow用户

发布于 2017-11-20 08: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();