欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

join 改成 wherehas

星期五, 2017-05-05 17:55 — adminshiping1
目前，我有这个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 .'%');
            });//package
        });//product
    });//membership
});//club

它是否正确？有捷径吗？
 

   
什么是问题和期望？ -  Jarek Tkaczyk 4月18日14时13分
   
所以你的问题是为什么我不能用“club.owner”作为领域？ -  David Barker 4月18日14时13分03分
   
因为下一个嵌套级别对于嵌套关系运行另一个WHERE id IN（）子句的查询。你想用LIKE子句搜索所有相关的模型？ -  Jarek Tkaczyk 4月18日14时13分12分
   
我已经更新了这个问题。@deczo可以做你说的话吗？在这里，我只是想搜索一个相关的模型。但是，如果我想像你说的那样呢？ -  user2094178 Apr 18 '14 at 15:24
   
这将导致许多具有EXIST（）的子查询，坏主意。 -  叶启伦Leon 5月18日16时9分15分

正确答案
 

更新：公关已被合并到4.2，所以现在可以在方法（ ）中使用点嵌套符号，has->has('relation1.relation2) ->whereHas('relation1.relation2, ..

您的问题仍然有些不清楚，或者您误以为Hass（）方法用于过滤模型（在这种情况下为用户），并且仅获得具有适合搜索条件的相关模型的那些。

看来你想从给定的用户的上下文中找到Packages，所以不需要使用whereHas方法。

无论如何取决于关系（1-1,1-m，mm），这可以很容易或相当困难，而不是很有效率。正如我所说，加载嵌套关系意味着对于每个级别的嵌套来说都是另一个数据库查询，所以在这种情况下，最终得到5个查询。

不管关系如何，你可以这样反转这个链，因为这样会更容易：

编辑：这不会像atH那样工作atHas（）不处理点嵌套关系！

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

你可以看到这是更可读的，仍然运行5个查询。也可以这样获得$包，这是您想要的模型的一个集合。

从使用者的角度来看，你会得到这样的东西（取决于关系再次）：

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

你得到点，不是吗？

您可以从集合中获取包，但这将是麻烦的。其他方式更容易。

所以你的问题的答案只是加入表：

// 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

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

 
 
   
什么是问题和期望？ -  Jarek Tkaczyk 4月18日14时13分
   
所以你的问题是为什么我不能用“club.owner”作为领域？ -  David Barker 4月18日14时13分03分
   
因为下一个嵌套级别对于嵌套关系运行另一个WHERE id IN（）子句的查询。你想用LIKE子句搜索所有相关的模型？ -  Jarek Tkaczyk 4月18日14时13分12分
   
我已经更新了这个问题。@deczo可以做你说的话吗？在这里，我只是想搜索一个相关的模型。但是，如果我想像你说的那样呢？ -  user2094178 Apr 18 '14 at 15:24
   
这将导致许多具有EXIST（）的子查询，坏主意。 -  叶启伦Leon 5月18日16时9分15分

这是非常好的德佐先生，我从你的答案中学到了很多。你的第一句话很准确。 -  user2094178 Apr 18 '14 at 16:58
   
我不明白它是否支持whereHas()？因为从我的经验，它不是.. -  Ortix92 Jan 11 '15 at 20:45
1 
已经支持了一个星期，因为我的最新公关被合并。然而，目前只有在@dev分支机构上可用，因为那时还没有新的版本。 -  Jarek Tkaczyk 1月11日15日22:44
   
@JarekTkaczyk将这些代码实际合并到我的项目中的“正确”方式是什么？我需要更改某些内容composer.json以确保它下载包含此代码的框架版本吗？ -  flyingL123 1月30日'15在6:31
   
您可以使用4.2.*@dev选项 -  Jarek Tkaczyk Jan 30'15 at 8:32
   
作为参考，我在L4.1中需要一个类似的解决方案，我已经使用了嵌套whereHas()方法。STH。喜欢：Model::whereHas('releation', function($q1){ $q1->whereHas('subRelation', function($q2){ $q2->where('...'); }); });它工作，但是，你可以看到它看起来很丑陋。 -  阿尔达8月5日，15日18时36分
   
感谢您的答案@JarekTkaczyk我尝试了点数嵌套符号与laravel 5.2，我不能相信这些复杂的联接在哪里容易。灿烂！ -  Neel Sep 16 '16 at 19:07
来自 http://stackoverflow.com/questions/23153678/eloquent-with-nested-wherehas


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?

   
shareimprove this question
 
   
What is the issue and what you expect? – Jarek Tkaczyk Apr 18 '14 at 13:00
   
So your question is why can't I use "club.owner" as the field? – David Barker Apr 18 '14 at 13:03
   
Because every next level of nesting runs another query with WHERE id IN () clause for the nested relation. You want to search all related models with LIKE clause? – Jarek Tkaczyk Apr 18 '14 at 13:12
   
I've updated the question. @deczo Is it possible to do what you say? Here I'm just trying to search in a single related model. But what if I wanted to do as you say? – user2094178 Apr 18 '14 at 15:24
   
this will result in many subqueries with EXIST(), bad idea. – Yap Kai Lun Leon May 18 '16 at 9:15
up vote10down voteaccepted

正确答案
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.

shareimprove this answer
 
   
This is very nice Mr. deczo, I learned a lot from your answer. Your first sentence is very accurate. – user2094178 Apr 18 '14 at 16:58
   
I don't get it.. is it supported for whereHas() or not? Because from what I experience it is not.. – Ortix92Jan 11 '15 at 20:45
1 
It's been supported for a week now - since my latest PR was merged. However it's available currently only on @dev branch, for there hasn't been new release since then yet. – Jarek Tkaczyk Jan 11 '15 at 22:44
   
@JarekTkaczyk what is the "proper" way to actually incorporate this code into my project? Do I need to change something in composer.json to ensure it downloads the version of the framework that includes this code? – flyingL123 Jan 30 '15 at 6:31
   
You can use 4.2.*@dev option – Jarek Tkaczyk Jan 30 '15 at 8:32
   
As a reference, I had needed a similar solution back in L4.1, and I had used nested whereHas()methods for this. Sth. like: Model::whereHas('releation', function($q1){ $q1->whereHas('subRelation', function($q2){ $q2->where('...'); }); }); It worked, however as you can see it looks quite ugly. – Arda Aug 5 '15 at 18:36
   
Thanks for the answer @JarekTkaczyk I tried the dot nested notation with laravel 5.2 and I cant believe how easy these otherwise complicated joins where. Splendid! – Neel Sep 16 '16 at 19:07
来自 http://stackoverflow.com/questions/23153678/eloquent-with-nested-wherehas
普通分类: 
laravel
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2