雄辩与嵌套 whereHas [英] Eloquent with nested whereHas

问题描述

目前我的模型集合中有这个 whereHas:

$query = self::whereHas('club', function($q) use ($search){$q->whereHas('owner', function($q) 使用($search){$q->where('name', 'LIKE', '%'. $search .'%');});});

我的印象是上面的代码可能是这样的:

$query = self::whereHas('club.owner', function($q) use ($search){$q->where('name', 'LIKE', '%'. $search .'%');});

我知道这已经很强大了，但即便如此，如果我有 5 层深的嵌套关系，事情会变得很糟糕.

更新:

正如评论中所说，我最终没有把我的问题说清楚，我很抱歉.

我将尝试使用一个简单的示例，考虑 $owner->club->membership->product->package，现在我想从所有者那里搜索某个包，它会是这样的:

$query = self::whereHas('club', function($q) use ($search){$q->whereHas('membership', function($q) use ($search){$q->whereHas('product', function($q) 使用($search){$q->whereHas('package', function($q) 使用($search){$q->where('alias', 'LIKE', '%'. $search .'%');});//包裹});//产品});//成员资格});//俱乐部

这是正确的吗?有捷径吗?

解决方案

更新:PR 刚刚合并到 4.2，所以现在可以在 点嵌套 表示法>has 方法( ->has('relation1.relation2) ->whereHas('relation1.relation2, .. )

您的问题仍然有点不清楚，或者您误解了 whereHas() 方法，因为它用于过滤模型(在这种情况下为用户)并仅获取具有符合搜索条件的相关模型的模型.p>

您似乎想从给定 User 的上下文中找到 Packages，因此无需使用 whereHas 方法.

无论如何，这取决于关系 (1-1,1-m,m-m)，这可能很容易，也可能非常困难，而且效率不高.正如我所说，加载嵌套关系意味着对于每一级嵌套都会有另一个数据库查询，所以在这种情况下，您最终会得到 5 个查询.

不管是什么关系，你都可以像这样反转这个链，因为它会更容易:

<小时>

这在 atm 不起作用，因为 whereHas() 不处理点嵌套关系！

//给定 $user 和 $search:$packages = Package::where('alias','like',"%$search%")->whereHas('product.membership.club.user', function ($q) use ($user) {$q->whereId($user->id);})->get();

<小时>

如您所见，这更具可读性，仍然运行 5 个查询.同样通过这种方式，您可以获得 $packages，它是您想要的模型的单个集合.

虽然从用户的上下文中你会得到这样的东西(再次取决于关系):

$user|-俱乐部||-会员资格|||-产品||||-包|||-另一个产品||||-包|||-yetAnotherProduct|||-包||-另一个会员......

你明白了，不是吗?

您可以从 Collection 中获取包，但这会很麻烦.反过来会更容易.

所以您的问题的答案只需加入表格:

//假设关系是最容易处理的:1-many$packages = Package::where('alias','like',"%$search%")->join('products','packages.product_id','=','products.id')->join('memberships','products.membership_id','=','memberships.id')->join('clubs','memberships.club_id','=','clubs.id')->where('clubs.user_id','=',$user->id)->get(['packages.*']);//除了包表，不要选择任何东西

当然你可以用一个很好的方法来包装它，这样你就不必每次执行这样的搜索时都写这个.这个查询的性能肯定会比上面显示的单独的 5 个查询要好得多.显然这样你只加载包，没有其他相关模型.

Currently I have this whereHas in a collection of my model:

$query = self::whereHas('club', function($q) use ($search)
{
    $q->whereHas('owner', function($q) use ($search)
    {
        $q->where('name', 'LIKE', '%'. $search .'%');
    });

});

I was under the impression the code above could be as such:

$query = self::whereHas('club.owner', function($q) use ($search)
{
    $q->where('name', 'LIKE', '%'. $search .'%');    
});

I'm aware this is already a lot of power, but even then, if I have a nested relationship 5 levels deep, things will get ugly.

Update:

As stated in the comments, I ended up not making my question clear, I apologize.

I will try to use a simple example, consider $owner->club->membership->product->package, now from owners I want to search a certain package, it would be something like this:

$query = self::whereHas('club', function($q) use ($search)
{
    $q->whereHas('membership', function($q) use ($search)
    {
        $q->whereHas('product', function($q) use ($search)
        {
            $q->whereHas('package', function($q) use ($search)
            {
                $q->where('alias', 'LIKE', '%'. $search .'%');
            });//package
        });//product
    });//membership
});//club

Is this correct? Is there a shortcut?

解决方案

Update: the PR has been just merged to 4.2, so now it's possible to use dot nested notation in has methods ( ->has('relation1.relation2) ->whereHas('relation1.relation2, .. )

Your question remains a bit unclear or you misunderstand whereHas() method as it is used to filter models (users in this case) and get only those that have related models fitting search conditions.

It seems that you want to find Packages from the context of a given User, so no need to use whereHas method.

Anyway depending on the relations (1-1,1-m,m-m) this can be easy or pretty hard and not very efficient. As I stated, loading nested relations means that for every level of nesting comes another db query, so in this case you end up with 5 queries.

Regardless of the relations you can invert this chain like this, as it will be easier:

---

edit: This is not going to work atm as whereHas() doesn't process dot nested relations!

// given $user and $search:
$packages = Package::where('alias','like',"%$search%")
  ->whereHas('product.membership.club.user', function ($q) use ($user) {
    $q->whereId($user->id);
  })->get();

---

As you can see this is much more readable, still runs 5 queries. Also this way you get $packages, which is a single Collection of the models you wanted.

While from the context of a user you would get something like this (depending on the relations again):

$user
 |-club
 |  |-membership
 |  |  |-product
 |  |  |  |-packages
 |  |  |-anotherProduct
 |  |  |  |-packages
 |  |  |-yetAnotherProduct
 |  |     |-packages
 |  |-anotherMembership
  .....

You get the point, don't you?

You could fetch the packages from the Collection, but it would be cumbersome. It's easier the other way around.

So the answer to your question would be simply joining the tables:

// Let's assume the relations are the easiest to handle: 1-many
$packages = Package::where('alias','like',"%$search%")
  ->join('products','packages.product_id','=','products.id')
  ->join('memberships','products.membership_id','=','memberships.id')
  ->join('clubs','memberships.club_id','=','clubs.id')
  ->where('clubs.user_id','=',$user->id)
  ->get(['packages.*']); // dont select anything but packages table 

Of course you can wrap it in a nice method so you don't have to write this everytime you perform such search. Performance of this query will be definitely much better than separate 5 queries shown above. Obviously this way you load only packages, without other related models.

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