laravel几个关联，经常用到的解析，关联查询
2019-05-28 本文已影响0人
空气KQ
查询存在的关联关系
比如我查询站点和接口关系，
hasOne
执行的SQL语句
site模型关联
/**
* 找出他的CSK_Api
* @return \Illuminate\Database\Eloquent\Relations\HasOne
*/
public function cskApis(){
return $this->hasOne('App\Models\CskMerchant','site_id','id');
}
查询最少有一个接口对应
Site::with('cskApis')->has('cskApis')->pluck('name', 'id'),
SQL
select `name`, `id` from `sites` where exists (select * from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null) and `sites`.`deleted_at` is null
使用到了where exists
查询最少3条呢
Site::with('cskApis')->has('cskApis','>=',3)->pluck('name', 'id'),
SQL
select `name`, `id` from `sites` where (select count(*) from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null) >= 3 and `sites`.`deleted_at` is null
orHas表示，或的意思 ,把查询条件增加到or里面
Site::with('cskApis')->orHas('cskApis')->pluck('name', 'id'),
select `name`, `id` from `sites` where (exists (select * from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null)) and `sites`.`deleted_at` is null
Site::with('cskApis')->orHas('cskApis','>=',3)->pluck('name', 'id'),
select `name`, `id` from `sites` where ((select count(*) from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null) >= 3) and `sites`.`deleted_at` is null
跟上面想反的方法有:
doesntHave 和 orDoesntHave
语句相反，其实都一样，只是语句换了
Site::with('cskApis')->doesntHave('cskApis')->pluck('name', 'id'),
select `name`, `id` from `sites` where not exists (select * from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null) and `sites`.`deleted_at` is null
not exists
然后你想要获取不存在3条医生，那么就不能像刚才那样子了。源码
public function doesntHave($relation, $boolean = 'and', Closure $callback = null)
{
return $this->has($relation, '<', 1, $boolean, $callback);
}
/**
* Add a relationship count / exists condition to the query with an "or".
*
* @param string $relation
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function orDoesntHave($relation)
{
return $this->doesntHave($relation, 'or');
}
还有个方法是has
说吧了那几个函数方法都是调用了这个has，只是做了快捷而已
select `name`, `id` from `sites` where (select count(*) from `csk_merchants` where `sites`.`id` = `csk_merchants`.`site_id` and `csk_merchants`.`deleted_at` is null) <= 3 and `sites`.`deleted_at` is null
Site::with('cskApis')->has('cskApis','<=',3)->pluck('name', 'id')
has方法
public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', Closure $callback = null)
{
if (strpos($relation, '.') !== false) {
return $this->hasNested($relation, $operator, $count, $boolean, $callback);
}
$relation = $this->getRelationWithoutConstraints($relation);
if ($relation instanceof MorphTo) {
throw new RuntimeException('has() and whereHas() do not support MorphTo relationships.');
}
// If we only need to check for the existence of the relation, then we can optimize
// the subquery to only run a "where exists" clause instead of this full "count"
// clause. This will make these queries run much faster compared with a count.
$method = $this->canUseExistsForExistenceCheck($operator, $count)
? 'getRelationExistenceQuery'
: 'getRelationExistenceCountQuery';
$hasQuery = $relation->{$method}(
$relation->getRelated()->newQueryWithoutRelationships(), $this
);
// Next we will call any given callback as an "anonymous" scope so they can get the
// proper logical grouping of the where clauses if needed by this Eloquent query
// builder. Then, we will be ready to finalize and return this query instance.
if ($callback) {
$hasQuery->callScope($callback);
}
return $this->addHasWhere(
$hasQuery, $relation, $operator, $count, $boolean
);
}
public function orHas($relation, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'or');
}
public function doesntHave($relation, $boolean = 'and', Closure $callback = null)
{
return $this->has($relation, '<', 1, $boolean, $callback);
}
public function orDoesntHave($relation)
{
return $this->doesntHave($relation, 'or');
}
public function doesntHave($relation, $boolean = 'and', Closure $callback = null)
{
return $this->has($relation, '<', 1, $boolean, $callback);
}
获取所有至少有一条评论包含foo字样的文章
$posts = Post::whereHas('comments', function ($query) {
$query->where('content', 'like', 'foo%');
})->get();
public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'and', $callback);
}
public function orWhereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'or', $callback);
}
统计关联模型
withCount 方法，该方法会放置一个 {relation}_count 字段到结果模型。例如：
$posts = App\Post::withCount('comments')->get();
foreach ($posts as $post) {
echo $post->comments_count;
}
public function withCount($relations)
{
if (empty($relations)) {
return $this;
}
if (is_null($this->query->columns)) {
$this->query->select([$this->query->from.'.*']);
}
$relations = is_array($relations) ? $relations : func_get_args();
foreach ($this->parseWithRelations($relations) as $name => $constraints) {
// First we will determine if the name has been aliased using an "as" clause on the name
// and if it has we will extract the actual relationship name and the desired name of
// the resulting column. This allows multiple counts on the same relationship name.
$segments = explode(' ', $name);
unset($alias);
if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
[$name, $alias] = [$segments[0], $segments[2]];
}
$relation = $this->getRelationWithoutConstraints($name);
// Here we will get the relationship count query and prepare to add it to the main query
// as a sub-select. First, we'll get the "has" query and use that to get the relation
// count query. We will normalize the relation name then append _count as the name.
$query = $relation->getRelationExistenceCountQuery(
$relation->getRelated()->newQuery(), $this
);
$query->callScope($constraints);
$query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();
if (count($query->columns) > 1) {
$query->columns = [$query->columns[0]];
}
// Finally we will add the proper result column alias to the query and run the subselect
// statement against the query builder. Then we will return the builder instance back
// to the developer for further constraint chaining that needs to take place on it.
$column = $alias ?? Str::snake($name.'_count');
$this->selectSub($query, $column);
}
return $this;
}
整个文件说明
<?php
namespace Illuminate\Database\Eloquent\Concerns;
use Closure;
use RuntimeException;
use Illuminate\Support\Str;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Eloquent\Relations\MorphTo;
use Illuminate\Database\Eloquent\Relations\Relation;
use Illuminate\Database\Query\Builder as QueryBuilder;
trait QueriesRelationships
{
/**
* Add a relationship count / exists condition to the query.
*
* @param string $relation
* @param string $operator
* @param int $count
* @param string $boolean
* @param \Closure|null $callback
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', Closure $callback = null)
{
if (strpos($relation, '.') !== false) {
return $this->hasNested($relation, $operator, $count, $boolean, $callback);
}
$relation = $this->getRelationWithoutConstraints($relation);
if ($relation instanceof MorphTo) {
throw new RuntimeException('has() and whereHas() do not support MorphTo relationships.');
}
// If we only need to check for the existence of the relation, then we can optimize
// the subquery to only run a "where exists" clause instead of this full "count"
// clause. This will make these queries run much faster compared with a count.
$method = $this->canUseExistsForExistenceCheck($operator, $count)
? 'getRelationExistenceQuery'
: 'getRelationExistenceCountQuery';
$hasQuery = $relation->{$method}(
$relation->getRelated()->newQueryWithoutRelationships(), $this
);
// Next we will call any given callback as an "anonymous" scope so they can get the
// proper logical grouping of the where clauses if needed by this Eloquent query
// builder. Then, we will be ready to finalize and return this query instance.
if ($callback) {
$hasQuery->callScope($callback);
}
return $this->addHasWhere(
$hasQuery, $relation, $operator, $count, $boolean
);
}
/**
* Add nested relationship count / exists conditions to the query.
*
* Sets up recursive call to whereHas until we finish the nested relation.
*
* @param string $relations
* @param string $operator
* @param int $count
* @param string $boolean
* @param \Closure|null $callback
* @return \Illuminate\Database\Eloquent\Builder|static
*/
protected function hasNested($relations, $operator = '>=', $count = 1, $boolean = 'and', $callback = null)
{
$relations = explode('.', $relations);
$doesntHave = $operator === '<' && $count === 1;
if ($doesntHave) {
$operator = '>=';
$count = 1;
}
$closure = function ($q) use (&$closure, &$relations, $operator, $count, $callback) {
// In order to nest "has", we need to add count relation constraints on the
// callback Closure. We'll do this by simply passing the Closure its own
// reference to itself so it calls itself recursively on each segment.
count($relations) > 1
? $q->whereHas(array_shift($relations), $closure)
: $q->has(array_shift($relations), $operator, $count, 'and', $callback);
};
return $this->has(array_shift($relations), $doesntHave ? '<' : '>=', 1, $boolean, $closure);
}
/**
* Add a relationship count / exists condition to the query with an "or".
*
* @param string $relation
* @param string $operator
* @param int $count
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function orHas($relation, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'or');
}
/**
* Add a relationship count / exists condition to the query.
*
* @param string $relation
* @param string $boolean
* @param \Closure|null $callback
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function doesntHave($relation, $boolean = 'and', Closure $callback = null)
{
return $this->has($relation, '<', 1, $boolean, $callback);
}
/**
* Add a relationship count / exists condition to the query with an "or".
*
* @param string $relation
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function orDoesntHave($relation)
{
return $this->doesntHave($relation, 'or');
}
/**
* Add a relationship count / exists condition to the query with where clauses.
*
* @param string $relation
* @param \Closure|null $callback
* @param string $operator
* @param int $count
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'and', $callback);
}
/**
* Add a relationship count / exists condition to the query with where clauses and an "or".
*
* @param string $relation
* @param \Closure $callback
* @param string $operator
* @param int $count
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function orWhereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
return $this->has($relation, $operator, $count, 'or', $callback);
}
/**
* Add a relationship count / exists condition to the query with where clauses.
*
* @param string $relation
* @param \Closure|null $callback
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function whereDoesntHave($relation, Closure $callback = null)
{
return $this->doesntHave($relation, 'and', $callback);
}
/**
* Add a relationship count / exists condition to the query with where clauses and an "or".
*
* @param string $relation
* @param \Closure $callback
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function orWhereDoesntHave($relation, Closure $callback = null)
{
return $this->doesntHave($relation, 'or', $callback);
}
/**
* Add subselect queries to count the relations.
*
* @param mixed $relations
* @return $this
*/
public function withCount($relations)
{
if (empty($relations)) {
return $this;
}
if (is_null($this->query->columns)) {
$this->query->select([$this->query->from.'.*']);
}
$relations = is_array($relations) ? $relations : func_get_args();
foreach ($this->parseWithRelations($relations) as $name => $constraints) {
// First we will determine if the name has been aliased using an "as" clause on the name
// and if it has we will extract the actual relationship name and the desired name of
// the resulting column. This allows multiple counts on the same relationship name.
$segments = explode(' ', $name);
unset($alias);
if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
[$name, $alias] = [$segments[0], $segments[2]];
}
$relation = $this->getRelationWithoutConstraints($name);
// Here we will get the relationship count query and prepare to add it to the main query
// as a sub-select. First, we'll get the "has" query and use that to get the relation
// count query. We will normalize the relation name then append _count as the name.
$query = $relation->getRelationExistenceCountQuery(
$relation->getRelated()->newQuery(), $this
);
$query->callScope($constraints);
$query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();
if (count($query->columns) > 1) {
$query->columns = [$query->columns[0]];
}
// Finally we will add the proper result column alias to the query and run the subselect
// statement against the query builder. Then we will return the builder instance back
// to the developer for further constraint chaining that needs to take place on it.
$column = $alias ?? Str::snake($name.'_count');
$this->selectSub($query, $column);
}
return $this;
}
/**
* Add the "has" condition where clause to the query.
*
* @param \Illuminate\Database\Eloquent\Builder $hasQuery
* @param \Illuminate\Database\Eloquent\Relations\Relation $relation
* @param string $operator
* @param int $count
* @param string $boolean
* @return \Illuminate\Database\Eloquent\Builder|static
*/
protected function addHasWhere(Builder $hasQuery, Relation $relation, $operator, $count, $boolean)
{
$hasQuery->mergeConstraintsFrom($relation->getQuery());
return $this->canUseExistsForExistenceCheck($operator, $count)
? $this->addWhereExistsQuery($hasQuery->toBase(), $boolean, $operator === '<' && $count === 1)
: $this->addWhereCountQuery($hasQuery->toBase(), $operator, $count, $boolean);
}
/**
* Merge the where constraints from another query to the current query.
*
* @param \Illuminate\Database\Eloquent\Builder $from
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function mergeConstraintsFrom(Builder $from)
{
$whereBindings = $from->getQuery()->getRawBindings()['where'] ?? [];
// Here we have some other query that we want to merge the where constraints from. We will
// copy over any where constraints on the query as well as remove any global scopes the
// query might have removed. Then we will return ourselves with the finished merging.
return $this->withoutGlobalScopes(
$from->removedScopes()
)->mergeWheres(
$from->getQuery()->wheres, $whereBindings
);
}
/**
* Add a sub-query count clause to this query.
*
* @param \Illuminate\Database\Query\Builder $query
* @param string $operator
* @param int $count
* @param string $boolean
* @return $this
*/
protected function addWhereCountQuery(QueryBuilder $query, $operator = '>=', $count = 1, $boolean = 'and')
{
$this->query->addBinding($query->getBindings(), 'where');
return $this->where(
new Expression('('.$query->toSql().')'),
$operator,
is_numeric($count) ? new Expression($count) : $count,
$boolean
);
}
/**
* Get the "has relation" base query instance.
*
* @param string $relation
* @return \Illuminate\Database\Eloquent\Relations\Relation
*/
protected function getRelationWithoutConstraints($relation)
{
return Relation::noConstraints(function () use ($relation) {
return $this->getModel()->{$relation}();
});
}
/**
* Check if we can run an "exists" query to optimize performance.
*
* @param string $operator
* @param int $count
* @return bool
*/
protected function canUseExistsForExistenceCheck($operator, $count)
{
return ($operator === '>=' || $operator === '<') && $count === 1;
}
}
