Get children date based on list of id's of parent table

Hi, I have the next tables on my system:

recipes:
id: PK
ingredients: text

recipes_ingredients:
id: PK
recipe_id: FK to recipes(id)
ingredient_id: FK to ingredients(id)
quantity: number

ingredients:
id: PK
name: text

Imagine I want to get based on one given ingredient (ie. tomato) a Collection data based on the next premise:

"Given the name of the ingredient, I want all the recipes that uses this ingredients, but for each recipe, I want the list of the rest of ingredients".

I know that this can be done using join() methods, but I'd like to get in the minimum quantity of effort using eloquent nested calls, due I have the three models (Recipe, Ingredient and RecipeIngredient) created with the hasMany, hasMenyThrough calls for all of the relations. Can be this done using a combination of with calls whereIn etc? How can be done?

Many thanks,

Dani from Barcelona

LaryAI

3 years ago

Level 58

Yes, this can be done using a combination of with calls and whereIn method. Here's an example code snippet that should work:

$ingredientName = 'tomato';

$recipes = Recipe::whereHas('ingredients', function ($query) use ($ingredientName) {
    $query->where('name', $ingredientName);
})->with(['ingredients' => function ($query) use ($ingredientName) {
    $query->where('name', '!=', $ingredientName);
}])->get();

This code will retrieve all recipes that use the given ingredient name, and for each recipe, it will also retrieve the list of all other ingredients used in that recipe. The whereHas method is used to filter the recipes based on the given ingredient name, and the with method is used to eager load the related ingredients for each recipe. The nested where clause inside the with method is used to exclude the given ingredient from the list of related ingredients.

1 like

mp3man

3 years ago

Level 2

@LaryAI wow! what a quick response. Let me know if this works (due I'm not the author of this project, I'm only a teacher that helps the girl who program it), and then I'd mark your respnose as valid. Furthermore, there's any link or tutoral to be expert in all these things of Eloquen nested methods?

To make this query, should be possible another way to do this based on by example:

$ingredientsIds = Ingredients::whereName('tomato')->pluck('id');

$recipesIngrediente = Recipes::whereIngredient($ingredientsIds)->ingredients('name')->get()

Please or to participate in this conversation.