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}

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

创作场景

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