phplaraveleloquentlaravel-query-builder

Eloquent query for filter with multiple params from url (Some parameters may not be specified)


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:

  1. Dish Category
  2. Dish Subcategory
  3. Cuisine
  4. Menu

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:

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)


Solution

  • 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