Laravel Eloquent Builder 的使用、源码分析总结

阅读数:58 2019 年 9 月 22 日 23:03

Laravel Eloquent Builder 的使用、源码分析总结

1. 为何要使用 Query Builder

Query builder 的最大好处就是,对于 SQL 的 select from where join group by order by limit offset having on 等关键字都转换为了类的方法,简化了 SQL 的使用成本,大大简化了代码量,原先一些操作数据库相关的一次性的 servicelogic 相关的函数,可以替换为直接 Builder 操作数据库。

Laravel 中关键字都实现在了下面两个类中:

\Illuminate\Database\Query\Builder

\Illuminate\Database\Query\JoinClause

2. 创建库 & Model

接着上篇文章对评论系统设计的一点总结,总结一下 Laravel Eloquent Builder 的一些用法。

首先用下面的 MySQL 语句创建存储评论的数据库表,并生成 Laravel 对应的 Model,用于检索数据库中的数据。

复制代码
1CREATE TABLE `Comment` (
2 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键, 评论 id',
3 `replied_id` int(11) NOT NULL DEFAULT '0' COMMENT '被评论 id',
4 `replied_root_id` int(11) NOT NULL DEFAULT '0' COMMENT '直接评论 id',
5 `content` text COMMENT '评论内容',
6 `status` int(11) DEFAULT NULL '评论状态',
7 `from_user_id` int(11) NOT NULL DEFAULT '0' COMMENT '评论人 id',
8 `from_user_name` varchar(20) NOT NULL DEFAULT '' COMMENT '评论人姓名',
9 `to_user_id` int(11) DEFAULT '0' COMMENT '被评论人 id',
10 `to_user_name` varchar(20) NOT NULL DEFAULT '' COMMENT '被评论人姓名',
11 `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP '创建时间',
12 PRIMARY KEY (`id`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8
1<?php
2/**
3 * Created by PhpStorm.
4 * User: yangzhen
5 * Date: 2018/4/3
6 * Time: 20:26
7 */
8
9namespace App\Model;
10
11
12use Illuminate\Database\Eloquent\Model;
13
14/**
15 * App\Model\Comment
16 *
17 * @property int $id 主键, 评论 id
18 * @property int $replied_id 被评论 id
19 * @property int $replied_root_id 直接评论 id
20 * @property string|null $content 评论内容
21 * @property int|null $status
22 * @property int $from_user_id 评论人 id
23 * @property string $from_user_name 评论人姓名
24 * @property int|null $to_user_id 被评论人 id
25 * @property string $to_user_name 被评论人姓名
26 * @property string $create_at
27 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereContent($value)
28 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereCreateAt($value)
29 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereFromUserId($value)
30 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereFromUserName($value)
31 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereId($value)
32 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereRepliedId($value)
33 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereRepliedRootId($value)
34 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereStatus($value)
35 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereToUserId($value)
36 * @method static \Illuminate\Database\Eloquent\Builder|\App\Model\Comment whereToUserName($value)
37 */
38class Comment extends Model
39{
40 protected $table = 'Comment';
41 protected $primaryKey = 'id';
42 public $timestamps = false;
43
44}
{1}

3. 使用案例

好,现在假想下面一个场景:

查询直接评论 id 分别为 10,11,12 的最近 7 天、评论内容含有关键字知识、发表评论用户名为 soull11201 或被评论用户名为 soul11201、按照创建时间倒排后的前 10 条数据,并分别计算每个直接评论下面一共含有多少条数据。

粗暴的构造 sql 如下:。

复制代码
1-- 10
2select * from Comment
3where
4 content = '知识'
5 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
6 and replied_root_id = 10
7 order by create_at desc
8 limit 10;
9
10select count(1) replied_root_id10_total_num from Comment
11where
12 content = '知识'
13 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
14 and replied_root_id = 10
15
16-- 11
17select * from Comment
18where
19 content = '知识'
20 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
21 and replied_root_id = 11
22 order by create_at desc
23 limit 10;
24
25select count(1) replied_root_id10_total_num from Comment
26where
27 content = '知识'
28 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
29 and replied_root_id = 11
30
31-- 12
32select * from Comment
33where
34 content = '知识'
35 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
36 and replied_root_id = 12
37 order by create_at desc
38 limit 10;
39
40select count(1) replied_root_id10_total_num from Comment
41where
42 content = '知识'
43 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')
44 and replied_root_id = 12

根据上面的 sql 构造,转换成如下的 Eloquent Builder 使用的代码:

复制代码
1<?php
2/**
3 * Created by PhpStorm.
4 * User: yangzhen
5 * Date: 2018/4/3
6 * Time: 20:46
7 */
8
9
10$replied_root_ids = [10, 11, 12];
11
12// 获取一个 \Illuminate\Database\Eloquent\Builder 实例
13$query = \App\Model\Comment::query();
14
15$query->where('content','知识')
16 ->where(function (\Illuminate\Database\Eloquent\Builder $builder){
17 //$builder 这是一个新的 builder 作为 $query 一个嵌入的查询 builder , 否则的话 orWhere 根本无法实现(因为 or 的优先级问题),
18 $builder->where('from_user_name', 'soul11201');
19 $builder->orWhere('to_user_name', 'soul11201');
20 });
21
22
23$coments = [];
24$total_num = [];
25
26foreach ($replied_root_ids as $replied_root_id) {
27
28 $new_query = \App\Model\Comment::whereRepliedRootId($replied_root_id)
29 ->addNestedWhereQuery($query);
30
31 // 此处先用来查询总条数
32 $total_num[$replied_root_id] = $new_query->count();
33 // 然后用来查询 10 条信息,顺序反之不可。
34 $coments[$replied_root_id] = $new_query->orderBy('create_at', 'desc')
35 ->limit(10)
36 ->get()
37 ->all();
38}

4. 执行流程分析

\Illuminate\Database\Eloquent\Builder::where()

复制代码
1 /**
2 * Add a basic where clause to the query.
3 *
4 * @param string|array|\Closure $column
5 * @param string $operator
6 * @param mixed $value
7 * @param string $boolean
8 * @return $this
9 */
10 public function where($column, $operator = null, $value = null, $boolean = 'and')
11 {
12 if ($column instanceof Closure) {
13 // 返回一个新的 Eloquent Builder
14 $query = $this->model->newQueryWithoutScopes();
15 // 匿名函数调用,
16 // 当 where 条件有复杂的条件表达式的时候
17 // 比如解决上面 表达式中 (from_user_name = 'soul11201' or to_user_name = 'soul11201') or 优先级的问题
18 // 直接使用 where() 无法解决,只能使用一个新的 Builder 来嵌入到原先的 Builder 中
19 $column($query);
20 //$this->query 是类 \Illuminate\Database\Query\Builder 的实例
21 // 将新的 Eloquent builder 的 Query\Builder 最为一个整体嵌入到原先 Eloquent Builder 的 `Query\Builder`的 where 表达式中,
22 // 就可以解决上面 or 优先级的问题
23 $this->query->addNestedWhereQuery($query->getQuery(), $boolean);
24 } else {
25 $this->query->where(...func_get_args());
26 }
27
28 return $this;
29 }

mixin
因为 \Illuminate\Database\Eloquent\Builder mixin 类\Illuminate\Database\Query

\Illuminate\Database\Eloquent\Builder::count()
\Illuminate\Database\Eloquent\Builder::orderby()
\Illuminate\Database\Eloquent\Builder::limit()

都是利用魔术方法 __call 间接使用的\Illuminate\Database\Query 的方法

复制代码
1 /**
2 * The base query builder instance.
3 *
4 * @var \Illuminate\Database\Query\Builder
5 */
6 protected $query;
7
8 ...
9 此处省略
10 ...
11
12 public function __call($method, $parameters)
13 {
14 ...
15 此处省略
16 ...
17 $this->query->{$method}(...$parameters);
18
19 return $this;
20 }

\Illuminate\Database\Eloquent\Builder::get()
\Illuminate\Database\Eloquent\Builder 是 \Illuminate\Database\Eloquent\Mdel 子类 与\Illuminate\Database\Query\Builder 沟通的桥梁。其中一个作用就是对\Illuminate\Database\Query\Builder 查询的数组结果 (由\Illuminate\Support\Collection 进行包裹) 渲染成\Illuminate\Database\Eloquent\Model 子类的对象数组结果 (由\Illuminate\Database\Eloquent\Collection 进行包裹)。

复制代码
1 /**
2 * Execute the query as a "select" statement.
3 *
4 * @param array $columns
5 * @return \Illuminate\Database\Eloquent\Collection|static[]
6 */
7 public function get($columns = ['*'])
8 {
9 // 应用其他注入构造条件
10 $builder = $this->applyScopes();
11
12 // If we actually found models we will also eager load any relationships that
13 // have been specified as needing to be eager loaded, which will solve the
14 // n+1 query issue for the developers to avoid running a lot of queries.
15 if (count($models = $builder->getModels($columns)) > 0) {
16 $models = $builder->eagerLoadRelations($models);
17 }
18
19 return $builder->getModel()->newCollection($models);
20 }
21
22 /**
23 * Get the hydrated models without eager loading.
24 *
25 * @param array $columns
26 * @return \Illuminate\Database\Eloquent\Model[]
27 */
28 public function getModels($columns = ['*'])
29 {
30 return $this->model->hydrate(
31 $this->query->get($columns)->all()
32 )->all();
33 }

Illuminate\Support\Collection::all()
\Illuminate\Database\Eloquent\Collection 是 Illuminate\Support\Collection 的子类,all() 方法指向的是同一个方法,直接返回其所包裹的数组。

作者介绍:
杨振振,技术保障部,17 年 8 月加入链家。先后参与过热链计划、移动审批、圣诞大屏、邮箱代理等项目。

本文转载自公众号贝壳产品技术(ID:gh_9afeb423f390)。

原文链接:

https://mp.weixin.qq.com/s/3ANed5AO1gf4se8g-rrysw

评论

发布