Eloquent ORM 常见使用场景整理

小艾

PHP/Laravel

发布于：Jun 9, 2022

1.1k words

4 min

以下实例，都是基于 Eloquent ORM，可以直接在 Laravel 下直接使用。

场景一

用户表增加了一个字段，因为项目中有许多指定部分列的查询，现在希望项目中所有查询用户信息的地方都可以自动加上这个字段。

解决方案：重写用户模型 booted 方法，并闭包自定义全局查询作用域。

protected static function booted()
{
    static::addGlobalScope("uid", function (Builder $builder) {
        $columns = $builder->getQuery()->columns;
        if (!is_null($columns)) {
            $builder->addSelect(["is_vip",]);
        }
    });
}

这样，当执行以下查询时，生成 SQL 如下：

1
2
3

$user = UserModel::select(["uid", "nickname"])->find(1);

// select `uid`, `nickname`, `is_vip` from `user` where `user`.`uid` = '1' limit 1

场景二

一对多关联（用户表与文章表）：如何获最新一条记录或统计关联数据的合计。

以用户与文章之间的一对多关联为例，如果用户列表需要返回用户发布最新文章的标题，那么该如何进行查询？

这个场景下使用连接查询是不行的，因为涉及到被驱动表的排序和限定查询问题。

思路：基于子查询结合关联模型进行查询。

public function index()
{
    $users = User::addSelect([
        'last_post_title' => Post::select(['title'])
            ->whereColumn('uid', 'users.uid')
            ->orderByDesc('created_at')
            ->limit(1)
    ])
      ->orderByDesc('uid')
      ->get();
}

生成 SQL 如下：

1	select (select title from `posts` where `uid` = `user`.`uid` order by `created_at` desc limit 1 ) as `last_post_title`,`user`.* from `user` order by `uid` desc ;

对某个字段进行合计也是一样的：

public function index()
{
UserModel::addSelect([
      "amount" => OrderModel::selectRaw('sum(total_price)')
            ->whereColumn('uid', 'user.uid')
            ->orderByDesc("total_price")
            ->limit(1)
    ])
      ->orderByDesc("amount")
      ->get();
}

生成 SQL 如下：

1	select (select SUM(total_price) from `order` where `uid` = `user`.`uid` order by `total_price` desc limit 1 ) as `amount`,`user`.* from `user` order by `amount` desc ;

场景三

聚合统计：统计订单表中不同状态下的订单数量。

思路一：对订单状态进行分组，然后通过代码逻辑统计不同状态下订单数量。

public function index()
{
    OrderModel::selectRaw("count(order_id), user_order_status")
      ->groupBy("user_order_status")
      ->get();
}

生成 SQL：

1	select count(order_id), user_order_status from `order` group by `user_order_status

思路一：将多次聚合统计查询合并为一次查询：

public function index()
{
    OrderModel::selectRaw('COUNT(CASE WHEN `user_order_status` = 0 then 1 END) AS draft_count')
            ->selectRaw('COUNT(CASE WHEN `user_order_status` = 1 then 1 END) AS audit_count')
            ->selectRaw('COUNT(CASE WHEN `user_order_status` = 2 then 1 END) AS normal_count')
            ->first();
}

生成 SQL 如下：

select COUNT(CASE WHEN `user_order_status` = 0 then 1 END) AS draft_count, COUNT(CASE WHEN `user_order_status` = 1 then 1 END) AS audit_count, COUNT(CASE WHEN `user_order_status` = 2 then 1 END) AS normal_count from `order` limit 1

场景四

一对一关联排序（用户主表与用户辅表）：用户列表可根据用户辅表的某个字段进行排序。

思路一：子查询

public function index()
{
UserModel::select(['uid', 'nickname'])
      ->orderBy(UserInfoModel::select('broken_number')
          ->whereColumn('uid', 'user.uid')
          ->orderBy('broken_number')
          ->limit(1)
      )
      ->paginate(20);
}

生成 SQL 如下：

1	select `uid`, `nickname` from `user` order by (select `broken_number` from `user_info` where `uid` = `user`.`uid` order by `broken_number` desc limit 1) desc limit 20 offset 0;

思路二：关联查询

public function index()
{
    UserModel::select("user.*")
        ->join('user_info', 'user_info.uid', '=', 'user.uid')
        ->orderBy('user_info.broken_number')
        ->paginate(20);
}

生成 SQL 如下：

1	select `user`.* from `user` inner join `user_info` on `user_info`.`uid` = `user`.`uid` order by `user_info`.`broken_number` asc limit 20 offset 0

场景五

一对多关联排序（用户表与订单表）：用户列表展示用户对应创建订单的金额，并根据金额大小进行排序。

一对多关联的场景不能使用连接查询，因为如果被驱动表中没有关联的数据，驱动表的记录也不会出现在结果列表中，同时涉及到排序和限定查询问题。

思路：子查询

public function index()
{
    UserModel::addSelect([
       "cost_amount" => OrderModel::selectRaw('sum(total_price)')
            ->whereColumn('uid', 'user.uid')
            ->orderByDesc("total_price")
            ->limit(1)
    ])
        ->orderByDesc("cost_amount")
        ->paginate(20);
}

生成 SQL 如下：

select `user`.*, (select sum(total_price) from `order` where `uid` = `user`.`uid` and `order`.`is_deleted` = '0' order by `total_price` desc limit 1) as `cost_amount` from `user` order by `cost_amount` desc limit 20 offset

场景六

基于子查询结合关联模型进行模糊匹配。

思路：通过一个 EXISTS 子查询实现基于关联模型字段对 User 模型实例的筛选。

public function index()
{
     $result = OrderModel::whereHas("user", function ($query) {
            $query->where('mobile', '*******');
        })
            ->orderByDesc("order_id")
            ->paginate(20);
}

生成 SQL 如下：

1	select * from `order` where exists (select * from `user` where `order`.`uid` = `user`.`uid` and `mobile` = '*******') order by `order_id` desc limit 20 offset 0