illuminate/database 使用 三

这次住主要看下构建查询使用。

一 原理

根据代码调用，创建Illuminate\Database\Capsule\Manager对象，设置配置，设置全局化，启动Eloquent。全局话后执行静态调用table()方法。

Manager对象中设置有table()方法，其通过获取对应链接对象调用其tabel()方法。

以mysql为例，通过Illuminate\Database\Connectors\ConnectionFactory工厂类返回Illuminate\Database\MySqlConnection类。Illuminate\Database\Connection为MySqlConnection父类，包含table()方法，返回Illuminate\Database\Query\Builder对象，参数包括Illuminate\Database\Query\Grammars\Grammar类对象和Illuminate\Database\Query\Processors\Processor类对象。Grammar和Processor都是默认的类，可以再次设置，这俩类都是用于拼接sql语句。

二 查询方法

Illuminate\Database\Query\Builder 类包含如下公用方法:

查询

select($columns = ['*'])

selectSub($query, $as)

selectRaw($expression, array $bindings = [])

addSelect($column)

from($table, $as = null)

fromSub($query, $as)

fromRaw($expression, $bindings = [])

去重

distinct()

连接

join($table, $first, $operator = null, $second = null, $type = 'inner', $where = false)

joinSub($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false)

joinWhere($table, $first, $operator, $second, $type = 'inner')

leftJoin($table, $first, $operator = null, $second = null)

leftJoinWhere($table, $first, $operator, $second)

leftJoinSub($query, $as, $first, $operator = null, $second = null)

rightJoin($table, $first, $operator = null, $second = null)

rightJoinWhere($table, $first, $operator, $second)

rightJoinSub($query, $as, $first, $operator = null, $second = null)

crossJoin($table, $first = null, $operator = null, $second = null)

crossJoinSub($query, $as)

查询条件

and

mergeWheres($wheres, $bindings)

where($column, $operator = null, $value = null, $boolean = 'and')

whereColumn($first, $operator = null, $second = null, $boolean = 'and')

whereRaw($sql, $bindings = [], $boolean = 'and')

whereIn($column, $values, $boolean = 'and', $not = false)

whereNotIn($column, $values, $boolean = 'and')

whereIntegerInRaw($column, $values, $boolean = 'and', $not = false)

whereIntegerNotInRaw($column, $values, $boolean = 'and')

whereNull($columns, $boolean = 'and', $not = false)

whereNotNull($columns, $boolean = 'and')

whereBetween($column, array $values, $boolean = 'and', $not = false)

whereBetweenColumns($column, array $values, $boolean = 'and', $not = false)

whereNotBetween($column, array $values, $boolean = 'and')

whereNotBetweenColumns($column, array $values, $boolean = 'and')

whereDate($column, $operator, $value = null, $boolean = 'and')

whereTime($column, $operator, $value = null, $boolean = 'and')

whereDay($column, $operator, $value = null, $boolean = 'and')

whereYear($column, $operator, $value = null, $boolean = 'and')

whereMonth($column, $operator, $value = null, $boolean = 'and')

addWhereExistsQuery(self $query, $boolean = 'and', $not = false)

whereRowValues($columns, $operator, $values, $boolean = 'and')

addDateBasedWhere($type, $column, $operator, $value, $boolean = 'and')

whereNested(Closure $callback, $boolean = 'and')

forNestedWhere()

addNestedWhereQuery($query, $boolean = 'and')

whereExists(Closure $callback, $boolean = 'and', $not = false)

whereNotExists(Closure $callback, $boolean = 'and')

whereJsonContains($column, $value, $boolean = 'and', $not = false)

whereJsonDoesntContain($column, $value, $boolean = 'and')

whereJsonLength($column, $operator, $value = null, $boolean = 'and')

or

orWhere($column, $operator = null, $value = null)

orWhereColumn($first, $operator = null, $second = null)

orWhereRaw($sql, $bindings = [])

orWhereIn($column, $values)

orWhereNotIn($column, $values)

orWhereIntegerInRaw($column, $values)

orWhereIntegerNotInRaw($column, $values)

orWhereNull($column)

orWhereBetween($column, array $values)

orWhereBetweenColumns($column, array $values)

orWhereNotBetween($column, array $values)

orWhereNotBetweenColumns($column, array $values)

orWhereNotNull($column)

orWhereDate($column, $operator, $value = null)

orWhereTime($column, $operator, $value = null)

orWhereDay($column, $operator, $value = null)

orWhereMonth($column, $operator, $value = null)

orWhereYear($column, $operator, $value = null)

orWhereExists(Closure $callback, $not = false)

orWhereNotExists(Closure $callback)

orWhereRowValues($columns, $operator, $values)

orWhereJsonContains($column, $value)

orWhereJsonDoesntContain($column, $value)

orWhereJsonLength($column, $operator, $value = null)

dynamicWhere($method, $parameters)

orWhereFullText($columns, $value, array $options = [])

分组

groupBy(...$groups)

groupByRaw($sql, array $bindings = [])

分组查询条件

having($column, $operator = null, $value = null, $boolean = 'and')

orHaving($column, $operator = null, $value = null)

havingBetween($column, array $values, $boolean = 'and', $not = false)

havingRaw($sql, array $bindings = [], $boolean = 'and')

orHavingRaw($sql, array $bindings = [])

排序

orderBy($column, $direction = 'asc')

orderByDesc($column)

latest($column = 'created_at') //时间倒叙

oldest($column = 'created_at')//等同于latest

inRandomOrder($seed = '')//随机排序

reorder($column = null, $direction = 'asc')//重新定义排序

分页

skip($value)//设置排序的便宜量的别名

offset($value)//设置偏移量

take($value)//同limit

limit($value)

forPage($page, $perPage = 15)

forPageBeforeId($perPage = 15, $lastId = 0, $column = 'id')

forPageAfterId($perPage = 15, $lastId = 0, $column = 'id')

paginate($perPage = 15, $columns = ['*'], $pageName = 'page', $page = null)

simplePaginate($perPage = 15, $columns = ['*'], $pageName = 'page', $page = null)

cursorPaginate($perPage = 15, $columns = ['*'], $cursorName = 'cursor', $cursor = null)

getCountForPagination($columns = ['*'])

联合

union($query, $all = false)

unionAll($query)

锁行

lock($value = true)

lockForUpdate()

sharedLock()

获取查询结果

find($id, $columns = ['*'])

get($columns = ['*']) //获取多行

value($column) //获取结果第一行

查看sql

toSql()

返回对象

cursor() //返回\Illuminate\Support\LazyCollection对象

pluck($column, $key = null)//返回\Illuminate\Support\Collection对象

implode($column, $glue = '')//等同于pluck

结果处理

exists()//确定当前查询是否有数据

doesntExist()//等同于exists

existsOr(Closure $callback)//没结果执行回调

doesntExistOr(Closure $callback)//等同于existsOr

聚合

count($columns = '*')

min($column)

max($column)

sum($column)

avg($column)

average($column)//等同于avg

numericAggregate($function, $columns = ['*'])

添加

insert(array $values)

insertOrIgnore(array $values)

insertGetId(array $values, $sequence = null)

insertUsing(array $columns, $query)

修改

update(array $values)

updateFrom(array $values)

updateOrInsert(array $attributes, array $values = [])

upsert(array $values, $uniqueBy, $update = null)

increment($column, $amount = 1, array $extra = [])//加定量值

decrement($column, $amount = 1, array $extra = [])//减定量值

delete($id = null)//删除

truncate()//清空表

newQuery()//获取\Illuminate\Database\Query\Builder对象

forSubQuery()//等同于newQuery

raw($value)//创建原始表达式

clone() //返回克隆查询对象

cloneWithout(array $properties)//返回克隆查询对象不包括给定属性

cloneWithoutBindings(array $except)//返回克隆查询对象 不包括给定绑定

dump()//框架自带输出函数 包含此方法的框架外调用会失败 但是可以自己实现

dd()//框架自带输出函数 包含此方法的框架外调用会失败 但是可以自己实现