I am new for Eloquent Query Builder and I have some questions:
Goal: Create a query that will get me recipes that match the specified parameters from selectors:
important nuance: in selectors you can't select a subcategory without first selecting a category (dependent dropdown logic using livewire).
After selecting parameters in selectors URL looks like this:
http://127.0.0.1:8000/recipes?dish_category=2&dish_subcategory=37&cuisine=1&menu=1
Problem:
I noticed that I can still remove the category id in the url and leave only the subcategory:
http://127.0.0.1:8000/recipes?dish_category=&dish_subcategory=37&cuisine=1&menu=1
How to achieve behavior where a user can only enter, for example, "cuisine" in selector, and the query selects recipes that match only that parameter?
Code:
Currently I have this code, it shows me correct data, but I am not sure it is a proper way for filtering data using URL parameters:
$recipes = Recipe::with('user', 'cuisine')
->where('dish_category_id', $request->query('dish_category'))
->orWhere('dish_subcategory_id', $request->query('dish_subcategory'))
->orWhere('cuisine_id', $request->query('cuisine'))
->orWhere('menu_id', $request->query('menu'))
->get();
dd($recipes);
and dd($recipes)
shows me this:
Illuminate\Database\Eloquent\Collection {#1085 ▼ // app\Http\Controllers\RecipeController.php:26
#items: array:2 [▼
0 =>
App\Models
\
Recipe {#1116 ▶}
1 =>
App\Models
\
Recipe {#1082 ▶}
]
#escapeWhenCastingToString: false
}
(2 recipes found with the parameters specified by the user, also, if the user, for example, enters only the parameter Category: Broth, then the same result is obtained)
Conclusion:
The current code works, but I need it not only to work, but also to be written correctly, also I need to take into account the Problems described above
Would be grateful for advices and help.
EDIT 01:
I have tried approach with when()
method:
$recipes = DB::table('recipes')
->when($dish_category, function ($query, $dish_category){
$query->where('dish_category_id', $dish_category);
})
->when($dish_subcategory, function ($query, $dish_subcategory){
$query->where('dish_subcategory_id', $dish_subcategory);
})
->when($cuisine, function ($query, $cuisine){
$query->where('cuisine_id', $cuisine);
})
->when($menu, function ($query, $menu){
$query->where('menu_id', $menu);
})
->get();
it works, but I am still able to paste dish_subcategory_id
in the URL (I am not sure it is a good behaviour when I don't allow user select dish_subcategory
without dish_category
in selectors)
The correct way would be to if you want to allow to use dish_subcategory_id
only when dish_category
is set :
$recipes = DB::table('recipes')
->when($dish_category, function ($query) use ($dish_category, $dish_subcategory) {
$query
->where('dish_category_id', $dish_category)
->when($dish_subcategory, function ($query) use ($dish_subcategory) {
$query->where('dish_subcategory_id', $dish_subcategory);
});
})
->when($cuisine, function ($query) use ($cuisine) {
$query->where('cuisine_id', $cuisine);
})
->when($menu, function ($query) use ($menu) {
$query->where('menu_id', $menu);
})
->get();
Keep in mind this is not the same as the initial query with orWhere
(but might be actually what you want to achieve).
Edit: used use()
to add needed variable to method scope