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;101112use Illuminate\Database\Eloquent\Model;1314/**15 * App\Model\Comment16 *17 * @property int $id 主键,评论id18 * @property int $replied_id 被评论id19 * @property int $replied_root_id 直接评论id20 * @property string|null $content 评论内容21 * @property int|null $status22 * @property int $from_user_id 评论人id23 * @property string $from_user_name 评论人姓名24 * @property int|null $to_user_id 被评论人id25 * @property string $to_user_name 被评论人姓名26 * @property string $create_at27 * @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 Model39{40 protected $table = 'Comment';41 protected $primaryKey = 'id';42 public $timestamps = false;4344}
复制代码
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; 910select 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 1516-- 1117select * 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 desc23 limit 10;2425select 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 3031-- 1232select * from Comment 33where 34 content = '知识' 35 and (from_user_name = 'soul11201' or to_user_name = 'soul11201')36 and replied_root_id = 1237 order by create_at desc38 limit 10;3940select 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 910$replied_root_ids = [10, 11, 12];1112//获取一个 \Illuminate\Database\Eloquent\Builder 实例13$query = \App\Model\Comment::query();1415$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 });212223$coments = [];24$total_num = [];2526foreach ($replied_root_ids as $replied_root_id) {2728 $new_query = \App\Model\Comment::whereRepliedRootId($replied_root_id)29 ->addNestedWhereQuery($query);3031 //此处先用来查询总条数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 Builder14 $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 }2728 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 ...1112 public function __call($method, $parameters)13 {14 ...15 此处省略16 ...17 $this->query->{$method}(...$parameters);1819 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();1112 // If we actually found models we will also eager load any relationships that13 // have been specified as needing to be eager loaded, which will solve the14 // 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 }1819 return $builder->getModel()->newCollection($models);20 }2122 /**23 * Get the hydrated models without eager loading.24 *25 * @param array $columns26 * @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
评论