The query I want to write:

I have three models:

• A Chef hasMany FoodItems.
• A FoodItem hasMany FoodItemAvailabilityDays.

In my query I want to look up a chef by a given slug. I want to return all of the chef's food items that are available on a specific date.

Example:

Imagine the Chef with slug "bob-smith" sells three food items: Lasagne, Burger and Burrito.

1. The Lasagne is available on 2022-01-01 and 2022-01-02
2. The Burger is available on 2022-01-02
3. The Burrito is available on 2022-01-03

IF I pass in the slug "bob-smith" and the date "2022-01-02"

THEN the query should return the "bob-smith" chef with the Lasange and Burger food items.

Model relationships:

// Chef.php
class Chef extends Model
{
  public function foodItems()
  {
    return $this->hasMany(FoodItem::class);
  }
}

// FoodItem.php
class FoodItem extends Model
{
  public function availabilityDays()
  {
    return $this->hasMany(FoodItemAvailabilityDay::class);
  }
}

CodePudding user response：

I found an/the answer

$chef = Chef::where('slug', $slug)
      ->with(['foodItems' => function ($food_item_query) use ($selected_date) {
        $food_item_query->whereHas('availabilityDays', function ($day_query) use ($selected_date) {
          $day_query->where('date', $selected_date);
        });
      }])
      ->firstOrFail();

CodePudding user response：

You should define a HasManyThrough relationship between Chef and FoodItemAvailabilityDays like this:

class Chef extends Model
{
  public function foodItems()
  {
    return $this->hasMany(FoodItem::class);
  }

  public function foodItemAvailabilityDays()
  {
    return $this->hasManyThrough(FoodItemAvailabilityDays::class, Chef::class);
  }
}

class FoodItem extends Model
{
  public function chef()
  {
    return $this->belongsTo(Chef::class);
  }

  public function availabilityDays()
  {
    return $this->hasMany(FoodItemAvailabilityDay::class);
  }
}

class FoodItemAvailabilityDay extends Model
{
  public function foodItems()
  {
    return $this->belongsTo(FoodItem::class);
  }
}

Now you can query the relationship directly:

$chef = Chef::whereSlug($slug)
    ->with(['foodItemAvailabilityDays' => fn($q) => $q->where('date', $selectedDate)])
    ->firstOrFail();