Laravel Eloquent group by 与数据透视表和关系

Question

我正在为餐车事件创建这个平台。有一批参加多项事件的参展商。每辆餐车都有自己提供的菜肴菜单，分为多个类别。

问题/我想要实现的目标

I would like to make a menu for each event, looping through all exhibitors (who attend) and then showing the dishes by category.

Something like this;

/menu/{eventid}

Dish category 1

• dish from exhibitor A
• dish from exhibitor B

Dish category 2

• dish from exh A
• dish from exh C
• dish from exh D

...

模型

事件模型

class Event extends Model
{
    protected $table = "events";

    public function exhibitors()
    {
        return $this->belongsToMany('App\Exhibitor', 'events_exhibitors');
    }

盘子模型

class Dish extends Model
{
    //

    protected $table = "dishes";    


    public function category()
    {
        return $this->hasOne('App\Category', 'id', 'category_id');
    }

    public function exhibitor()
    {
        return $this->belongsTo('App\Exhibitor');
    }
}

参展商模型

class Exhibitor extends Model
{
    protected $table = "exhibitors";

    public function events()
    {
        return $this->belongsToMany('App\Event', 'events_exhibitors');
    }

    public function dishes()
    {
        return $this->hasMany('App\Dish');
    }
}

数据库结构

有一个数据透视表来记录哪些餐车去哪些事件。到目前为止，我认为(希望)我的关系正在发挥作用。我希望这张图片足以说明问题；

我尝试过的

我已经尝试了几种方法，但我认为我对 Laravel eloquent 的见解缺乏理解这个问题背后的逻辑。

$dishes = Event::where('id', $id)
            ->with(['exhibitors.dishes' => function($q) {
            $q->select('dishes.dish_data');
        }])->get();

或者

$dishes = Event::with(array('exhibitor.dish') => function($query) use ($sub){
            $query->where('name',$sub);}))->get();

我完全不知道如何通过 Eloquent 来完成此操作，也不知道这将如何在 View 中工作。

Answer 1

尝试这样的事情:

它将获取与事件相关的所有参展商的 ID。然后它将获取所有类别，其中所连接的菜肴具有在参展商 ID 数组中的 Exhibitor_id。

类别模型

class Category extends Model
{
    protected $table = "categories";    


    public function dishes()
    {
        return $this->hasMany('App\Dish', 'category_id');
    }
}

Controller 操作

$event = Event::findOrFail($id);

$exhibitorIds = $event->exhibitors()->select('exhibitors.id')->get()->pluck('id')->all();

$categories = Category::whereHas('dishes', function($query) use ($exhibitorIds) {
    $query->whereIn('exhibitor_id', $exhibitorIds);
})->with(['dishes' => function($query) use ($exhibitorIds) {
    $query->whereIn('exhibitor_id', $exhibitorIds);
}])->get();

return view('events.menu', compact('event', 'categories') );

查看

@foreach($categories as $category)
<h2>{{ $category->name }}</h2>
<ul>
   @foreach($category->dishes as $dish)
       <li>{{ $dish->dish_data }}</li>
   @endforeach
</ul>
@endforeach