给 Eloquent 的 whereHas 加个 where in 的优化

原文地址:www.jianshu.com/p/ec2a93755e10

用 Laravel 很久了,whereHas 简直是连表大杀器,本来需要写大量 SQL 的查询用 whereHas 都可以很快的实现。不过在一些场景里,遇到了严重的性能问题。

我们有个A表,大约是百万级数据,与之关联的有个B表,大约万级数据。在做关联查询的时候我们自然使用 A::whereHas('b', function(){...})

后来发现了许多慢查询,仔细一看发现,Laravel 的 whereHas 在生成 SQL 的时候会使用 select * from A where exists ( select * from b where ... ) 。当我们的左表远远大于右表时,A 表就成了性能瓶颈。

最直接的方法当然是拆成两条 SQL,但是嫌麻烦,还得一条条优化。再加上我们很多 SQL 都是靠各种工具生成,所以改起来也挺麻烦。

于是就考虑加了个 whereHasIn 的方法,接口参数跟 whereHas 一致,只不过在生成 SQL 的时候会生成 select * from A where A.id in (select id from B)。这样就不需要改什么 SQL 了,只要在调用 A::whereHas() 的地方加两个字符变成 A::whereHasIn() 就搞定了。在实际中,我们这条查询的耗时从几秒一下降低到了20毫秒。

下面是一个实现的 demo,暂时只支持 一对多的情况。如果大家有什么更好的想法,一起讨论讨论。

<?php

use Illuminate\Database\Eloquent\Relations;

abstract class AbstractModel
{
    /**
     * whereHas 的 where in 实现
     *
     * @param \Illuminate\Database\Eloquent\Builder $builder
     * @param string $relationName
     * @param callable $callable
     * @return Builder
     *
     * @throws Exception
     */
    public function scopeWhereHasIn($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    }
}

补充一:评论区 @overtrue 提供了用 macro 的方式,更优雅,支持场景更好。可以直接拿去用。感谢 overtrue!

补充二:社区大佬 @Jiangqh 已经封装成了扩展包,大家可直接拿去用 [扩展包] Laravel-wherehasin 提升 ORM 关联关系查询性能

补充三:帖子写了两年了,没想到还有这么多人在看在点赞。看来大家对这种小工具还是挺需要的,打算拉个群,如果你对各种奇技淫巧感兴趣,可以也一起玩。我的微信:Um1seVpVSnNZV05y(Base64Decode * 2)。还有个快死了的电报群:t.me/BetterCodeGroup

本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由 leo 于 4年前 加精
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
讨论数量: 65

可以考虑使用 macro 特性来增加该方法:

use \Illuminate\Database\Query\Builder;
.
.
.
Builder::macro('whereHasIn', function($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    });

你可以将上面这段代码写到 AppServiceProvider 中

这样你就不需要修改原有的模型类了,也不需要创建 Model 基类。

3年前 评论
MyMasterHydy 2年前
kis龍 1年前
Artisan丶 1年前

这个原始的SQL语句还真没注意过

4年前 评论
nff93

可以向官方提PR

3年前 评论

虽然知道有这个性能问题,但是一直没有去改造……感谢分享! :stuck_out_tongue_winking_eye: :+1:

3年前 评论

file

报错了 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has_in' in 'where clause' (SQL: select * from tags where has_in = tagsname)

3年前 评论
chris_zqw 2年前
ahkai 7个月前

改成 join会不会更好点?

3年前 评论

@linzi 看报错是 tag 没有加这个方法。这个函数我是加在 baseModel 里的。

3年前 评论

@luffyzhao 这种场景下,性能都差不太多。对 laravel 封装的 join 用的不多,改的时候也没想太多。

3年前 评论

@linzi 咦,这个 tagsname 是个 relation 么?

3年前 评论

@huiren 是的 用whereHas可以 用whereHasIn 就报错啦

3年前 评论
ALMAS

把这个方法Macro到Builder更好用觉得,不用到处添加trait,不过还没试试是否可行。

3年前 评论

@ALMAS Laravel 提供了 scope 功能来做这个事,其实也相当于定制版的 macro 了 :joy:

3年前 评论

@linzi 执行过程可以参考 \Illuminate\Database\Eloquent\Builder::__call ,优先找 scope 方法,找不到再找 Query。

所以这个错误应该是 scope 没命中上。

方便的话可以把更详细的错误私信给我

3年前 评论

这个文件是添加到哪里呢 ,楼主能写详细点吗

3年前 评论

可以考虑使用 macro 特性来增加该方法:

use \Illuminate\Database\Query\Builder;
.
.
.
Builder::macro('whereHasIn', function($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    });

你可以将上面这段代码写到 AppServiceProvider 中

这样你就不需要修改原有的模型类了,也不需要创建 Model 基类。

3年前 评论
MyMasterHydy 2年前
kis龍 1年前
Artisan丶 1年前

@linzi 参考我的回复

3年前 评论

return Doctor::whereHas('user',function($query){
$query->select('id');
})->toSql();

直接 select id 会比你们那个快吗@huiren @overtrue

3年前 评论

@linzi 感觉上区别不大,这个问题的核心是 where exists 会扫左表。跟右表怎么处理关系不大。不过你可以试试。

3年前 评论

没有看懂scopewhereHasIn这个函数,以及它要怎么调用?

3年前 评论

@阳光的小mi 不好意思最近比较忙没空上来。

scope 是 eloquent 提供的一个类似于 plugin 的功能。能把方法集成到 builder 里。

直接 $query->whereHasIn(...) 就行了。

具体的 scope 的问题,可以详细看一下文档~

3年前 评论

@huiren 感谢回复,问题已解决。

3年前 评论

@阳光的小mi 请问你是怎么解决的,能把你的whereHasIn部分代码发出来吗?

3年前 评论

@liyq 在你要调用的模型类里面添加下面这个scope方法,直接用就行

 public function scopeWhereHasIn(Builder $builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */

        $relation = Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        if($nextRelation){
            $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);
        } else {
            $in = $relation->getQuery()->where($callable);
        }

        if ($relation instanceof BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        } elseif ($relation instanceof HasMany){
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new \Exception(__METHOD__ . " 不支持 " . get_class($relation));
    }
3年前 评论

没有理解 $build这三个参数数在使用 whereHas();的时候传的 第二个参数是个字符串 指的是什么?

3年前 评论

@overtrue $builder, $relationName, callable $callable 这三个值怎样传递 一直报参数缺少

3年前 评论

@阳光的小mi Builder $builder, $relationName, callable $callable方法里的三个参数分别可以说明下吗,特别是第一个builder

3年前 评论

{"errCode":40000,"errMsg":"App\Model\AssociateUser::scopeWhereHasIn 不支持 Illuminate\Database\Eloquent\Relations\BelongsTo"}

3年前 评论

关于如何使用?

  • 将上述scope加到需要调用的model中去,至于是写个基类让其他model继承,或者使用trait,或是直接复制对应model自行决定;
  • 使用方式和whereIn一样,$builder不用传,只需要传两个参数即可;
    关于方法的参数说明?
    /**
     * whereHas 的 where in 实现
     *
     * @param \Illuminate\Database\Eloquent\Builder $builder
     * @param string $relationName
     * @param callable $callable
     * @return Builder
     *
     * @throws Exception
     */
    public function scopeWhereHasIn(Builder $builder, $relationName, callable $callable)
    {
    }
  • $builder 是 \Illuminate\Database\Eloquent\Builder的对象,跟其他的Eloquent提供的方法一样,可以直接通过模型调用;
  • $relationName 是模型关联定义的函数名,定义请看laravel文档-模型关联
  • $callable 是闭包,和where,whereIn一样,你可以在这里传入一个闭包继续处理
    @mingzheng
3年前 评论
MyMasterHydy 2年前

@阳光的小mi 感谢回复,确实是可以。目前还不支持belongsTo,hasOne的对应关联关系调用是吗·

3年前 评论

@mingzheng 支持啊 哥 belongsTo hasOne这些类你要在头文件引用 要不你 instanceof不到就扔出异常啦
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\HasMany;

3年前 评论

还有人吗,请教下注册macro方式如何使用,注册后跟whereHas一样调用时会报少参数

file

而把$builder传进去又会报另一个错
file

调用方式和注册方式
file

file

2年前 评论

file
我写了一个trait,在模型里引入后这样调用的,报错会生成 一个不存在的字段 has_id = ? ,请问是这样调用的嘛?

Laravel

2年前 评论

大概搞定了,使用macro方式来扩展需要改动代码,调用方式也改变了,除了关系名与回调函数,还需要传query对象、model对象

2年前 评论
snowlyg 2年前
MyMasterHydy (作者) 2年前

macro注册方式
file

具体的实现代码
class WhereHasIn
{
protected $builder, $relationName, $callable;

/**
 * WhereHasIn constructor.
 * @param \Illuminate\Database\Eloquent\Builder $builder laravel构建查询的对象,如DockerModel::query()可获得
 * @param string $relationName 关系名,与whereHas写法一样
 * @param callable $callable 闭包,与whereHas写法一样
 */
public function __construct(Builder $builder,string $relationName, callable $callable)
{
    $this->builder = $builder;
    $this->relationName = $relationName;
    $this->callable = $callable;
}

/**
 * 进行whereHasIn方式构造查询对象并返回
 * @param Builder $builder
 * @param string $relationName
 * @param callable $callable
 * @return Builder
 * @throws Exception
 */
protected function whereHasIn(Builder $builder,string $relationName, callable $callable)
{
    if (!$relationName)return $builder;
    $relationNames = explode('.', $relationName);
    $nextRelation = implode('.', array_slice($relationNames, 1));

    $method = $relationNames[0];
    $model = $builder->getModel();
    /** @var Relations\BelongsTo|Relations\HasOne $relation */
    $relation = Relations\Relation::noConstraints(function () use ($method,$model) {
        return $model->$method();
    });
    /** @var Builder $in,$in是关联模型的构造查询对象 */
    $in = $this->whereHasIn($relation->getQuery(),$nextRelation, $callable);

// $in = $relation->getQuery()->whereHasIn($relation->getQuery(),$relation->getModel(),$nextRelation, $callable);
//调用闭包函数,并将关联模型的构造查询对象传入
call_user_func($callable,$in);
if ($relation instanceof Relations\BelongsTo) {
return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
} elseif ($relation instanceof Relations\HasOne) {
return $builder->whereIn($model->getKeyName(), $in->select($relation->getForeignKeyName()));
}

    throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
}

public function execute()
{
    try {
        return $this->whereHasIn($this->builder,$this->relationName, $this->callable);
    } catch (Exception $e) {
        throw $e;
    }
}

}

调用方式与whereHas一样

file

2年前 评论

您好,拜读了您的《给 Eloquent 的 whereHas 加个 where in 的优化》的文章,对我很有启发,但是实现时出现一个问题。我有两张表,A 和 B,当我在 A 表的 Model 中加了 whereHasIn 方法后,使用 A::whereHasIn ('b',function (...)) 时,出现了 Column not found: 1054 Unknown column 'has_in' in 'where clause' 错误,此时 SQL 是,select * from A where b_id in (select id from B where has_in ) 。
然后我就在 B 表的 Model 中也加入了 whereHasIn 方法,此时错误是 Call to undefined method Illuminate\Database\Query\Builder,能请教一下是什么原因吗?或者您有什么 Demo 可供我参考,感谢百忙之后查看我的私信,期待您的回复!

2年前 评论
snowlyg 2年前
august1 (作者) 2年前
snowlyg 2年前
august1 (作者) 2年前
snowlyg 2年前
huiren (楼主) 2年前
august1 (作者) 2年前
snowlyg 2年前
snowlyg 2年前
august1 (作者) 2年前
elseif ($relation instanceof Relations\HasOne) {
    return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
}

//应改为
return $builder->whereIn($relation->getLocalKeyName(), $in->select($relation->getForeignKeyName()));

这里错了吧, 发出来之前希望测试一下正确性

2年前 评论
Rxy-development 2年前
oneSmile (作者) 2年前
Rxy-development 2年前

您好,写法暂时不支持belongsToMany吗?

2年前 评论
还不出来 1年前

我总结了一下:

  1. 首先楼主@huiren 这里使用的是laravel里ORM的【自定义全局作用域】功能, 所以你需要先大概了解一下,不过对理解后面的代码影响不大。
    public function scopeWhereHasIn($builder, $relationName, callable $callable)
    {内容省略,看楼主写的}
  1. 关于报错,很多人看完了【置顶】的@overtrue 代码以后说:“这玩意怎么不能用啊/报错啦/缺少参数”,原因是你直接照搬代码,你仔细看看代码

    楼主@huiren 写的方法是在模型基类中,用的是上述1的全局作用域方法写的代码

    置顶@overtrue 写的方法是在AppServiceProvider中,用的是Builder::macro绑定宏扩展方法写的代码,官方的文档中没注意到有这个,但是有别人写了,感觉通俗易懂,可以点链接看看。

    两种方式都不在同一个class类中,$this都不是同一东西,代码怎么能照搬呢,置顶是教你们宏特性扩展方法添加whereHasIn,所以千万别照搬。不然你就会遇到楼上各种报错。

  1. 关于使用调用时,第一个参数$builder是个啥? ,我用下面的代码解释,不知道你能不能懂
    $query  = User::query();    //$query就是$builder,打印一下,自己看看
    $result = $query->whereHasIn($query,'post',function(){})

4.关于性能说明,使用whereHas打印SQL,不难发现这个子查询使用了exists方法,这就导致了楼上说的全表扫描的结果

select * from `table_A` where exists (
    select `id`, `name`, `age` from `table_B` 
        where `table_A`.`bid` = `table_B`.`id` 
        and (`name` like ? or `age` = ?) 
        and `table_B`.`deleted_at` is null
) 
and `table_A`.`deleted_at` is null

而使用自定义的whereHasIn打印SQL,使用的是in方法,的确提升了很高的性能。

select * from `table_A` where `bid` in (
    select `id` from `table_B` 
        where (`name` like ? or `age` = ?)
) and `table_A`.`deleted_at` is null

5.最后,贴代码,直接在AppServiceProvider中的boot方法里写

public function boot()
    {
       //绑定whereHasIn方法
        Builder::macro('whereHasIn', function ($builder, $relationName, callable $callable) {
            $relationNames = explode('.', $relationName);   //获取关联表名称  如User.post或post
            $nextRelation  = implode('.', array_slice($relationNames, 1));  //获取连续关联的方法名
            $method        = $relationNames[0];
            $model         = $builder->getModel(); //获取当前模型

            /** @var Relations\BelongsTo|Relations\HasOne $relation */
            $relation = Relations\Relation::noConstraints(function () use ($model, $method) {
                return $model->$method();
            });

            /** @var Builder $in */
            if ($nextRelation) {
                $in = $relation->getQuery()->whereHasIn($builder, $nextRelation, $callable);
            } else {
                $in = $relation->getQuery()->where($callable);
            }

           if ($relation instanceof Relations\BelongsTo) {
                return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
            } elseif ($relation instanceof Relations\HasOne) {
                return $builder->whereIn($builder->getModel()->getKeyName(), $in->select($relation->getForeignKeyName()));
            } elseif ($relation instanceof Relations\HasMany) {
                return $builder->whereIn($builder->getModel()->getKeyName(), $in->select($relation->getForeignKeyName()));
            }

           throw new \Exception(__METHOD__ . " 不支持 " . get_class($relation));
        });
    }

自行调用如下:

$query  = TableA::query();
$result = $query->whereHasIn($query, 'TableB', function ($query) use ($search_text) {
            $query->select('id', 'name')
                ->where('name', 'like', "%{$search_text}%");
        })
            ->with(['TableC', 'TableD'])
            ->get();
1年前 评论
还不出来 1年前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!