雄辩的嵌套WHERE语句 [英] Eloquent nested WHERE statement

问题描述

对于旨在拍摄某些对象的在线竞赛，我有个用户上传个对象的图片，其中这些对象具有一个类别和子类别.

For an online competition with the goal of photographing certain objects I've got users uploading pictures of objects, where the objects have a category and a subcategory.

用户的仪表板应如下所示:

A user's dashboard should look something like this:

  ------------------------------------------------ 
  |                Category                      |  
  ------------------------------------------------  
  |   -----------------------------------------  |
  |   | Subcat.          |  Subcat.           |  |
  |   |------------------|--------------------|  |
  |   |Object1 | Object2 | Object1 | Object2  |  |
  |   |------------------|--------------------|  |
  |   |  Pic1  |  Pic1   | Pic1    | Pic1     |  |
  |   |  Pic2  |  Pic2   | Pic2    | Pic2     |  |
  |   ----------------------------------------   |
  |                                              |
  ------------------------------------------------  

为了能够在适当的循环中输出图片，我在模型之间安装了 hasMany 关系.这样我就可以通过

In order to be able to output the pictures in the appropriate loop, I installed hasMany relationships between the models. So I can get an object with a convenient hierarchy by

$userPics = Category::with(['subcats','subcats.objects','subcats.objects.pics'])->get();

问题:

对于仪表板，我当然只希望来自特定用户的图像: Pictures 模型包含一个 user_id 字段.我可以直接获取用户的图片:Pictures::where('user_id',$user->id)->get();，但是然后在对象中没有结构来创建上面显示的所需视图.

For the dashboard of course I only want the images from a certain user: the Pictures model contains a user_id field. I could get the user's pictures directly: Pictures::where('user_id',$user->id)->get();, but then I don't have the structure in the object to create the desired view shown above.

我该怎么做:

$userPics = Category::with([...])->where('user_id',$user->id)->get();

非常感谢您提供任何提示！

Many thanks for any tips!

推荐答案

您要获取包含属于指定用户的图片的类别，因此您可以这样做(有点冗长):

You want to get categories with pictures that belong to specified user, so you do this (a bit verbose):

Category::with(['subcats', 'subcats.objects' => function ($q) use ($user) {
        $q->with(['pictures' => function ($q) use ($user) {
            $q->where('user_id', $user->id);
        }]);
    }])
    ->whereHas('subcats', function ($q) use ($user) {
        $q->whereHas('objects', function ($q) use ($user) {
            $q->whereHas('pictures', function ($q) use ($user) {
                $q->where('user_id', $user->id);
            });
        });
    })
    ->get();

您需要在此处使用嵌套的with()闭包，因为使用点表示法可以加载两个以上级别的关系.

You need to use nested with() closures here, because you can load more than two levels of relationship with using dot notation.

如果不需要加载图片和子类别数据，只需删除with()部分.

If you don't need to load pictures and subcategories data, just remove with() part.

如果您还想过滤子类别，请在with()中为subcats添加一个闭包.

If you also want to filter subcategories, add a closure to with() for subcats.

这篇关于雄辩的嵌套WHERE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！