How to make two Laravel Eloquent queries into a single one (so I hit the database once instead of 2 times)

Question

Say I have 3 tables in my database.

'my_recipe', 'my_inventory' and 'ingredient'.

The 'my_recipe' table stores a list of raw_id's based on the 'ingredient' table and the 'quantity' need for the recipe. The 'my_inventory' table stores a list of raw_id's and 'have_quantity'.

So let's take a look at what I currently have at the moment. I have the following 2 queries:

First Query:

$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();

Second Query:

$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();

The first query returns results that look something like this:

{"user":"jack","raw_id":"853","quantity":2,"ingredient_name":"apple"},
{"user":"jack","raw_id":"853","quantity":4,"ingredient_name":"peach"}

The second query returns results that look something like this:

{"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"}

Notice in the second query results I have to get the sum of the ingredients based on the 'ingredient_name' for my ideal output.

How can I get my ideal output in a single query?

My ideal output would look something like this:

{"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}

It's basically the results of the first query with 'have_quantity' totals from the second query.

EDIT:

my_recipe Model:

'user', 'raw_id', 'quantity'

my_inventory Model:

'user', 'raw_id', 'have_quantity'

ingredient Model:

'raw_id', 'ingredient_name'

Note: In the ingredient model there can be rows with the same 'ingredient_name' but have different 'raw_id'.

Answer 1

Based on our chat conversation I managed to get some extra information on the table structure and what was needed to do to get the wanted results.

For those interested the information can be found here

Anyway I ended up creating the query like this:

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;

Now converting into the needed structure:

$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();

Answer 2

Let's try this:

$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();

Answer 3

Maybe this can solve you problem

//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();