Eloquent 嵌套 whereHas

2021-12-26 00:00:00 php laravel laravel-4 eloquent

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

$query = self::whereHas('club', function($q) use ($search){$q->whereHas('owner', function($q) use ($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) use ($search){$q->whereHas('package', function($q) use ($search){$q->where('alias', 'LIKE', '%'. $search .'%');});//包裹});//产品});//会员资格});//俱乐部

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

解决方案

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

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

看来您想从给定的用户的上下文中查找包，所以不需要使用 whereHas 方法.

无论如何，取决于关系(1-1,1-m,m-m)，这可能很容易，也可能非常困难，而且效率不高.正如我所说，加载嵌套关系意味着对于每一级嵌套都会出现另一个 db 查询，因此在这种情况下，您最终会得到 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|-俱乐部||-会员|||-产品||||-包|||-另一个产品||||-包|||-又一个产品|||-包||-另一个会员.....

你明白了，不是吗?

您可以从 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.

相关文章