join 语句

join 语句对数据库进行连接操作，join 函数的连接条件可以非常简单：

  1. DB::table('services')->select('*')->join('translations AS t', 't.item_id', '=', 'services.id');

也可以比较复杂：

  1. DB::table('users')->select('*')->join('contacts', function ($j) {
  2.         $j->on('users.id', '=', 'contacts.id')->orOn('users.name', '=', 'contacts.name');
  3.     });
  4.     //select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" or "users"."name" = "contacts"."name"
  6.     $builder = $this->getBuilder();
  7.     DB::table('users')->select('*')->from('users')->joinWhere('contacts', 'col1', function ($j) {
  8.         $j->select('users.col2')->from('users')->where('users.id', '=', 'foo')
  9.     });
  10.     //select * from "users" inner join "contacts" on "col1" = (select "users"."col2" from "users" where "users"."id" = foo)

还可以更加复杂：

  1. DB::table('users')->select('*')->leftJoin('contacts', function ($j) {
  2.         $j->on('users.id', '=', 'contacts.id')->where(function ($j) {
  3.             $j->where('contacts.country', '=', 'US')->orWhere('contacts.is_partner', '=', 1);
  4.         });
  5.     });
  6.     //select * from "users" left join "contacts" on "users"."id" = "contacts"."id" and ("contacts"."country" = 'US' or "contacts"."is_partner" = 1)
  8.     DB::table('users')->select('*')->leftJoin('contacts', function ($j) {
  9.         $j->on('users.id', '=', 'contacts.id')->where('contacts.is_active', '=', 1)->orOn(function ($j) {
  10.             $j->orWhere(function ($j) {
  11.                 $j->where('contacts.country', '=', 'UK')->orOn('contacts.type', '=', 'users.type');
  12.             })->where(function ($j) {
  13.                 $j->where('contacts.country', '=', 'US')->orWhereNull('contacts.is_partner');
  14.             });
  15.         });
  16.     });
  17.     //select * from "users" left join "contacts" on "users"."id" = "contacts"."id" and "contacts"."is_active" = 1 or (("contacts"."country" = 'UK' or "contacts"."type" = "users"."type") and ("contacts"."country" = 'US' or "contacts"."is_partner" is null))

其实 join 语句与 where 语句非常相似，将 join 语句的连接条件看作 where 的查询条件完全可以，接下来我们看看源码。

join 语句

从上面的示例代码可以看出，join 函数的参数多变，第二个参数可以是列名，也有可能是闭包函数。当第二个参数是列名的时候，第三个参数可以是闭包，还可以是符号 =、>=。

  1. public function join($table, $first, $operator = null, $second = null, $type = 'inner', $where = false)
  2. {
  3.     $join = new JoinClause($this, $type, $table);
  5.     if ($first instanceof Closure) {
  6.         call_user_func($first, $join);
  8.         $this->joins[] = $join;
  10.         $this->addBinding($join->getBindings(), 'join');
  11.     }
  13.     else {
  14.         $method = $where ? 'where' : 'on';
  16.         $this->joins[] = $join->$method($first, $operator, $second);
  18.         $this->addBinding($join->getBindings(), 'join');
  19.     }
  21.     return $this;
  22. }

可以看到，程序首先新建了一个 JoinClause 类对象，这个类实际上继承 queryBuilder，也就是说 queryBuilder 上的很多方法它都可以直接用，例如 where、whereNull、whereDate 等等。

  1. class JoinClause extends Builder
  2. {
  3. }

如果第二个参数是闭包函数的话，就会像查询组一样根据查询条件更新 $join。

如果第二个参数是列名，那么就会调用 on 方法或 where 方法。这两个方法的区别是，on 方法只支持 whereColumn方法和 whereNested，也就是说只能写出 join on col1 = col2 这样的语句，而 where 方法可以传递数组、子查询等等.

  1. public function on($first, $operator = null, $second = null, $boolean = 'and')
  2. {
  3.     if ($first instanceof Closure) {
  4.         return $this->whereNested($first, $boolean);
  5.     }
  7.     return $this->whereColumn($first, $operator, $second, $boolean);
  8. }
  10. public function orOn($first, $operator = null, $second = null)
  11. {
  12.     return $this->on($first, $operator, $second, 'or');
  13. }

grammer——compileJoins

接下来我们来看看如何编译 join 语句：

  1. protected function compileJoins(Builder $query, $joins)
  2. {
  3.     return collect($joins)->map(function ($join) use ($query) {
  4.         $table = $this->wrapTable($join->table);
  6.         return trim("{$join->type} join {$table} {$this->compileWheres($join)}");
  7.     })->implode(' ');
  8. }

可以看到，JoinClause 在编译中是作为 queryBuild 对象来看待的。

union 语句

union 用于合并两个或多个 SELECT 语句的结果集。Union 因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用 Union All。

我们在 laravel 中可以这样使用：

  1. $query = DB::table('users')->select('*')->where('id', '=', 1);
  2. $query->union(DB::table('users')->select('*')->where('id', '=', 2));
  3. //(select * from `users` where `id` = 1) union (select * from `users` where `id` = 2)

还可以添加多个 union 语句：

  1. $query = DB::table('users')->select('*')->where('id', '=', 1);
  2. $query->union(DB::table('users')->select('*')->where('id', '=', 2));
  3. $query->union(DB::table('users')->select('*')->where('id', '=', 3));      
  4. //(select * from "users" where "id" = 1) union (select * from "users" where "id" = 2) union (select * from "users" where "id" = 3)

union 语句可以与 orderBy 相结合：

  1. $query = DB::table('users')->select('*')->where('id', '=', 1);
  2. $query->union(DB::table('users')->select('*')->where('id', '=', 2));
  3. $query->orderBy('id', 'desc');
  4. //(select * from `users` where `id` = ?) union (select * from `users` where `id` = ?) order by `id` desc

union 语句可以与 limit、offset 相结合：

  1. $query = DB::table('users')->select('*');
  2. $query->union(DB::table('users')->select('*'));
  3. $builder->skip(5)->take(10);
  4. //(select * from `users`) union (select * from `dogs`) limit 10 offset 5

union 函数

union 函数比较简单：

  1. public function union($query, $all = false)
  2. {
  3.     if ($query instanceof Closure) {
  4.         call_user_func($query, $query = $this->newQuery());
  5.     }
  7.     $this->unions[] = compact('query', 'all');
  9.     $this->addBinding($query->getBindings(), 'union');
  11.     return $this;
  12. }

grammer——compileUnions

语法编译器对 union 的处理：

  1. public function compileSelect(Builder $query)
  2. {
  3.     $sql = parent::compileSelect($query);
  5.     if ($query->unions) {
  6.         $sql = '('.$sql.') '.$this->compileUnions($query);
  7.     }
  9.     return $sql;
  10. }
  12. protected function compileUnions(Builder $query)
  13. {
  14.     $sql = '';
  16.     foreach ($query->unions as $union) {
  17.         $sql .= $this->compileUnion($union);
  18.     }
  20.     if (! empty($query->unionOrders)) {
  21.         $sql .= ' '.$this->compileOrders($query, $query->unionOrders);
  22.     }
  24.     if (isset($query->unionLimit)) {
  25.         $sql .= ' '.$this->compileLimit($query, $query->unionLimit);
  26.     }
  28.     if (isset($query->unionOffset)) {
  29.         $sql .= ' '.$this->compileOffset($query, $query->unionOffset);
  30.     }
  32.     return ltrim($sql);
  33. }
  35. protected function compileUnion(array $union)
  36. {
  37.     $conjuction = $union['all'] ? ' union all ' : ' union ';
  39.     return $conjuction.'('.$union['query']->toSql().')';
  40. }

可以看出，union 的处理比较简单，都是调用 query->toSql 语句而已。值得注意的是，在处理 union 的时候，要特别处理 order、limit、offset。

orderBy 语句

orderBy 语句用法很简单，可以设置多个排序字段，也可以用原生排序语句：

  1. DB::table('users')->select('*')->orderBy('email')->orderBy('age', 'desc');
  3. DB::table('users')->select('*')->orderBy('email')->orderByRaw('age desc');

orderBy 函数

如果当前查询中有 union 的话，排序的变量会被放入 unionOrders 数组中，这个数组只有在 compileUnions 函数中才会被编译成 sql 语句。否则会被放入 orders 数组中，这时会被 compileOrders 处理：

  1. public function orderBy($column, $direction = 'asc')
  2. {
  3.     $this->{$this->unions ? 'unionOrders' : 'orders'}[] = [
  4.         'column' => $column,
  5.         'direction' => strtolower($direction) == 'asc' ? 'asc' : 'desc',
  6.     ];
  8.     return $this;
  9. }

grammer——compileOrders

orderBy 的编译也很简单：

  1. protected function compileOrders(Builder $query, $orders)
  2. {
  3.     if (! empty($orders)) {
  4.         return 'order by '.implode(', ', $this->compileOrdersToArray($query, $orders));
  5.     }
  7.     return '';
  8. }
  10. protected function compileOrdersToArray(Builder $query, $orders)
  11. {
  12.     return array_map(function ($order) {
  13.         return ! isset($order['sql'])
  14.                     ? $this->wrap($order['column']).' '.$order['direction']
  15.                     : $order['sql'];
  16.     }, $orders);
  17. }

limit offset forPage 语句

limit offset 或者 skip take 用法很简单，有趣的是，laravel 考虑了负数的情况：

  1. DB::select('*')->from('users')->offset(5)->limit(10);
  3. DB::select('*')->from('users')->skip(5)->take(10);
  5. DB::select('*')->from('users')->skip(-5)->take(-10);
  7. DB::select('*')->from('users')->forPage(5, 10);

limit offset 函数

和 orderBy 一样，如果当前查询中有 union 的话，limit / offset 会被放入 unionLimit / unionOffset 中，在编译 union 的时候解析：

  1. public function take($value)
  2. {
  3.     return $this->limit($value);
  4. }
  6. public function limit($value)
  7. {
  8.     $property = $this->unions ? 'unionLimit' : 'limit';
  10.     if ($value >= 0) {
  11.         $this->$property = $value;
  12.     }
  14.     return $this;
  15. }
  17. public function skip($value)
  18. {
  19.     return $this->offset($value);
  20. }
  22. public function offset($value)
  23. {
  24.     $property = $this->unions ? 'unionOffset' : 'offset';
  26.     $this->$property = max(0, $value);
  28.     return $this;
  29. }
  31. public function forPage($page, $perPage = 15)
  32. {
  33.     return $this->skip(($page - 1) * $perPage)->take($perPage);
  34. }

grammer——compileLimit compileOffset

这个不能再简单了：

  1. protected function compileLimit(Builder $query, $limit)
  2. {
  3.     return 'limit '.(int) $limit;
  4. }
  6. protected function compileOffset(Builder $query, $offset)
  7. {
  8.     return 'offset '.(int) $offset;
  9. }

group 语句

groupBy 语句的参数形式有多种： 

  1. DB::select('*')->from('users')->groupBy('email');
  3. DB::select('*')->from('users')->groupBy('id', 'email');
  5. DB::select('*')->from('users')->groupBy(['id', 'email']);
  7. DB::select('*')->from('users')->groupBy(new Raw('DATE(created_at)'));

groupBy 函数很简单，仅仅是为 $this->groups 成员变量合并数组：

  1. public function groupBy(...$groups)
  2. {
  3.     foreach ($groups as $group) {
  4.         $this->groups = array_merge(
  5.             (array) $this->groups,
  6.             Arr::wrap($group)
  7.         );
  8.     }
  10.     return $this;
  11. }

语法编译器的处理：

  1. protected function compileGroups(Builder $query, $groups)
  2. {
  3.     return 'group by '.$this->columnize($groups);
  4. }

having 语句

having 语句的用法也很简单。大致有 having、orHaving、havingRaw、orHavingRaw 这几个函数：

  1. DB::select('*')->from('users')->having('email', '>', 1);
  3. DB::select('*')->from('users')->groupBy('email')->having('email', '>', 1);
  5. DB::select('*')->from('users')->having('email', 1)->orHaving('email', 2);
  7. DB::select('*')->from('users')->havingRaw('user_foo < user_bar');
  9. DB::select('*')->from('users')->having('baz', '=', 1)->orHavingRaw('user_foo < user_bar');

having 函数

having 函数大致与 whereColumn 相同：

  1. public function having($column, $operator = null, $value = null, $boolean = 'and')
  2. {
  3.     $type = 'Basic';
  5.     list($value, $operator) = $this->prepareValueAndOperator(
  6.         $value, $operator, func_num_args() == 2
  7.     );
  9.     if ($this->invalidOperator($operator)) {
  10.         list($value, $operator) = [$operator, '='];
  11.     }
  13.     $this->havings[] = compact('type', 'column', 'operator', 'value', 'boolean');
  15.     if (! $value instanceof Expression) {
  16.         $this->addBinding($value, 'having');
  17.     }
  19.     return $this;
  20. }

havingRaw 函数：

  1. public function havingRaw($sql, array $bindings = [], $boolean = 'and')
  2. {
  3.     $type = 'Raw';
  5.     $this->havings[] = compact('type', 'sql', 'boolean');
  7.     $this->addBinding($bindings, 'having');
  9.     return $this;
  10. }

grammer——compileHavings

语法编译器：

  1. protected function compileHavings(Builder $query, $havings)
  2. {
  3.     $sql = implode(' ', array_map([$this, 'compileHaving'], $havings));
  5.     return 'having '.$this->removeLeadingBoolean($sql);
  6. }
  8. protected function compileHaving(array $having)
  9. {
  10.     if ($having['type'] === 'Raw') {
  11.         return $having['boolean'].' '.$having['sql'];
  12.     }
  14.     return $this->compileBasicHaving($having);
  15. }
  17. protected function compileBasicHaving($having)
  18. {
  19.     $column = $this->wrap($having['column']);
  21.     $parameter = $this->parameter($having['value']);
  23.     return $having['boolean'].' '.$column.' '.$having['operator'].' '.$parameter;
  24. }

when / tap / unless 语句

when 语句可以根据条件来判断是否执行查询条件，unless 与 when 相反，第一个参数是 false 才会调用闭包函数执行查询，tap 指定 when 的第一参数永远为真：

  1. $callback = function ($query, $condition) {
  2.     $this->assertEquals($condition, 'truthy');
  4.     $query->where('id', '=', 1);
  5. };
  7. $default = function ($query, $condition) {
  8.     $this->assertEquals($condition, 0);
  10.     $query->where('id', '=', 2);
  11. };
  13. DB::select('*')->from('users')->when('truthy', $callback, $default)->where('email', 'foo');
  15. DB::select('*')->from('users')->tap($callback)->where('email', 'foo');
  17. DB::select('*')->from('users')->unless('truthy', $callback, $default)->where('email', 'foo');

when、unless、tap 函数的实现：

  1. public function when($value, $callback, $default = null)
  2. {
  3.     if ($value) {
  4.         return $callback($this, $value) ?: $this;
  5.     } elseif ($default) {
  6.         return $default($this, $value) ?: $this;
  7.     }
  9.     return $this;
  10. }
  12. public function unless($value, $callback, $default = null)
  13. {
  14.     if (! $value) {
  15.         return $callback($this, $value) ?: $this;
  16.     } elseif ($default) {
  17.         return $default($this, $value) ?: $this;
  18.     }
  20.     return $this;
  21. }
  23. public function tap($callback)
  24. {
  25.     return $this->when(true, $callback);
  26. }

Aggregate 查询

聚合方法也是 sql 的重要组成部分，laravel 提供 count、max、min、avg、sum、exist 等聚合方法：

  1. DB::table('users')->count();//select count(*) as aggregate from "users"
  3. DB::table('users')->max('id');//select max("id") as aggregate from "users"
  5. DB::table('users')->min('id');//select min("id") as aggregate from "users"
  7. DB::table('users')->sum('id');//select sum("id") as aggregate from "users"
  9. DB::table('users')->exists();//select exists(select * from "users") as "exists"

这些聚合函数实际上都是调用 aggregate 函数：

  1. public function count($columns = '*')
  2. {
  3.     return (int) $this->aggregate(__FUNCTION__, Arr::wrap($columns));
  4. }
  6. public function aggregate($function, $columns = ['*'])
  7. {
  8.     $results = $this->cloneWithout(['columns'])
  9.                     ->cloneWithoutBindings(['select'])
  10.                     ->setAggregate($function, $columns)
  11.                     ->get($columns);
  13.     if (! $results->isEmpty()) {
  14.         return array_change_key_case((array) $results[0])['aggregate'];
  15.     }
  16. }

可以看出来，aggregate 函数复制了一份 queryBuilder，只是缺少了 select、bingding 成员变量:

  1. public function cloneWithout(array $properties)
  2. {
  3.     return tap(clone $this, function ($clone) use ($properties) {
  4.         foreach ($properties as $property) {
  5.             $clone->{$property} = null;
  6.         }
  7.     });
  8. }
  10. public function cloneWithoutBindings(array $except)
  11. {
  12.     return tap(clone $this, function ($clone) use ($except) {
  13.         foreach ($except as $type) {
  14.             $clone->bindings[$type] = [];
  15.         }
  16.     });
  17. }
  19. protected function setAggregate($function, $columns)
  20. {
  21.     $this->aggregate = compact('function', 'columns');
  23.     if (empty($this->groups)) {
  24.         $this->orders = null;
  26.         $this->bindings['order'] = [];
  27.     }
  29.     return $this;
  30. }

exist 聚合函数和其他不一样，它的流程与 whereExist 大致相同：

  1. public function exists()
  2. {
  3.     $results = $this->connection->select(
  4.         $this->grammar->compileExists($this), $this->getBindings(), ! $this->useWritePdo
  5.     );
  7.     if (isset($results[0])) {
  8.         $results = (array) $results[0];
  10.         return (bool) $results['exists'];
  11.     }
  13.     return false;
  14. }

grammer——compileAggregate

laravel 的聚合函数具有独占性，也就是说调用聚合函数后，不能再 select 其他的列:

  2. protected function compileAggregate(Builder $query, $aggregate)
  3. {
  4.     $column = $this->columnize($aggregate['columns']);
  6.     if ($query->distinct && $column !== '*') {
  7.         $column = 'distinct '.$column;
  8.     }
  10.     return 'select '.$aggregate['function'].'('.$column.') as aggregate';
  11. }
  13. protected function compileColumns(Builder $query, $columns)
  14. {
  15.     if (! is_null($query->aggregate)) {
  16.         return;
  17.     }
  19.     $select = $query->distinct ? 'select distinct ' : 'select ';
  21.     return $select.$this->columnize($columns);
  22. }

可以看到，如果存在聚合函数，那么编译 select 的 compileColumns 函数将不会运行。

first / find / value / pluck / implode

从数据库中取出后数据，我们可以使用 laravel 提供给我们的一些函数进行包装处理。

first 函数可以让我们只查询第一条：

  1. DB::table('users')->where('id', '=', 1)->first();

find 函数，可以利用数据库表的主键来查询第一条：

  1. DB::table('users')->find(1);

pluck 函数可以取查询记录的某一列：

  1. DB::table('users')->where('id', '=', 1)->pluck('foo');/['bar', 'baz']

pluck 函数取查询记录的某一列的同时，还可以设置列名的 key： 

  1. DB::table('users')->where('id', '=', 1)->pluck('foo', 'id');//[1 => 'bar', 10 => 'baz']

value 函数可以取第一条数据的某一列：

  1. DB::table('users')->where('id', '=', 1)->value('foo');//bar

implode 函数可以将多条数据的某一列拼成字符串：

  1. DB::table('users')->where('id', '=', 1)->implode('foo', ',');//'bar,baz'

first 函数

find 函数，使用了 limit 1 的 sql 语句：

  1. public function first($columns = ['*'])
  2. {
  3.     return $this->take(1)->get($columns)->first();
  4. }

find 函数

find 函数实际利用主键调用 first 函数：

  1. public function find($id, $columns = ['*'])
  2. {
  3.     return $this->where('id', '=', $id)->first($columns);
  4. }

pluck 函数

pluck 函数主要对得到的数据调用 pluck 函数：

  1. public function pluck($column, $key = null)
  2. {
  3.     $results = $this->get(is_null($key) ? [$column] : [$column, $key]);
  5.     return $results->pluck(
  6.         $this->stripTableForPluck($column),
  7.         $this->stripTableForPluck($key)
  8.     );
  9. }

implod 函数

implod 函数对一维数组调用 implod 函数：

  1. public function implode($column, $glue = '')
  2. {
  3.     return $this->pluck($column)->implode($glue);
  4. }

chunk 语句

如果你需要操作数千条数据库记录，可以考虑使用 chunk 方法。这个方法每次只取出一小块结果，并会将每个块传递给一个闭包处理。

  1. DB::table('users')->orderBy('id')->chunk(100, function ($users) {
  2.     foreach ($users as $user) {
  3.         //
  4.     }
  5. });

你可以从 闭包 中返回 false，以停止对后续分块的处理：

  1. DB::table('users')->orderBy('id')->chunk(100, function ($users) {
  2.     // Process the records...
  3.     if (...) {
  4.         return false;
  5.     } 
  6. });

如果不想按照主键 id 来进行分块，我们还可以自定义分块主键：

  1. DB::table('users')->orderBy('id')->chunkById(100, function ($users) {
  2.     foreach ($users as $user) {
  3.         //
  4.     }
  5. }, 'someIdField');

chunk 函数

chunk 函数的实现实际上是 forPage 函数，当从数据库获得数据后，先判断是否拿到了数据，如果拿到了就会继续执行闭包函数，否则就会中断程序。执行闭包函数后，需要判断返回状态。若取出的数据小于分块的条数，说明数据已经全部获取完毕，结束程序。

  1. public function chunk($count, callable $callback)
  2. {
  3.     $this->enforceOrderBy();
  5.     $page = 1;
  7.     do {
  8.         $results = $this->forPage($page, $count)->get();
  10.         $countResults = $results->count();
  12.         if ($countResults == 0) {
  13.             break;
  14.         }
  16.         if ($callback($results, $page) === false) {
  17.             return false;
  18.         }
  20.         unset($results);
  22.         $page++;
  23.     } while ($countResults == $count);
  25.     return true;
  26. }
  28. protected function enforceOrderBy()
  29. {
  30.     if (empty($this->query->orders) && empty($this->query->unionOrders)) {
  31.         $this->orderBy($this->model->getQualifiedKeyName(), 'asc');
  32.     }
  33. }

enforceOrderBy 函数是用于数据按照主键的大小进行排序。

chunkById 函数

chunkById 函数与 chunk 函数唯一不同的是 forPage 函数被换成了 forPageAfterId 函数，目的是替换主键：

  1. public function chunkById($count, callable $callback, $column = 'id', $alias = null)
  2. {
  3.     $alias = $alias ?: $column;
  5.     $lastId = 0;
  7.     do {
  8.         $clone = clone $this;
  10.         $results = $clone->forPageAfterId($count, $lastId, $column)->get();
  12.         $countResults = $results->count();
  14.         if ($countResults == 0) {
  15.             break;
  16.         }
  18.         if ($callback($results) === false) {
  19.             return false;
  20.         }
  22.         $lastId = $results->last()->{$alias};
  24.         unset($results);
  25.     } while ($countResults == $count);
  27.     return true;
  28. }

forPageAfterId 函数实际上是把 offset 函数删除，并按照自定义的列来排序，每次获取最后一条数据的自定义列的数值，利用 where 条件不断获取下一部分分块数据：

  1. public function forPageAfterId($perPage = 15, $lastId = 0, $column = 'id')
  2. {
  3.     $this->orders = $this->removeExistingOrdersFor($column);
  5.     return $this->where($column, '>', $lastId)
  6.                 ->orderBy($column, 'asc')
  7.                 ->take($perPage);
  8. }
  10. protected function removeExistingOrdersFor($column)
  11. {
  12.     return Collection::make($this->orders)
  13.                 ->reject(function ($order) use ($column) {
  14.                     return isset($order['column'])
  15.                            ? $order['column'] === $column : false;
  16.                 })->values()->all();
  17. }