FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

Optimize has() query · JavaDevl/framework@6d5a876 · GitHub

/ framework Public
forked from laravel/framework

Commit 6d5a876

Browse files
acasar
committed
Optimize has() query
1 parent b774c30 commit 6d5a876

11 files changed

Lines changed: 125 additions & 52 deletions

File tree

‎src/Illuminate/Database/Eloquent/Builder.php‎

Lines changed: 48 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -655,7 +655,7 @@ public function orWhere($column, $operator = null, $value = null)
655655
}
656656

657657
/**
658-
* Add a relationship count condition to the query.
658+
* Add a relationship count/exists condition to the query.
659659
*
660660
* @param string $relation
661661
* @param string $operator
@@ -672,7 +672,12 @@ public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', C
672672

673673
$relation = $this->getHasRelationQuery($relation);
674674

675-
$query = $relation->getRelationCountQuery($relation->getRelated()->newQuery(), $this);
675+
// If we only need to check for the existence of the relation, then we can
676+
// optimize the subquery to only run a "where exists" clause instead of
677+
// the full "count" clause. This will make the query run much faster.
678+
$queryType = $this->shouldRunExistsQuery($operator, $count) ? 'getRelationQuery' : 'getRelationCountQuery';
679+
680+
$query = $relation->$queryType($relation->getRelated()->newQuery(), $this);
676681

677682
if ($callback) {
678683
call_user_func($callback, $query);
@@ -684,7 +689,7 @@ public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', C
684689
}
685690

686691
/**
687-
* Add nested relationship count conditions to the query.
692+
* Add nested relationship count/exists conditions to the query.
688693
*
689694
* @param string $relations
690695
* @param string $operator
@@ -712,7 +717,7 @@ protected function hasNested($relations, $operator = '>=', $count = 1, $boolean
712717
}
713718

714719
/**
715-
* Add a relationship count condition to the query.
720+
* Add a relationship count/exists condition to the query.
716721
*
717722
* @param string $relation
718723
* @param string $boolean
@@ -725,7 +730,7 @@ public function doesntHave($relation, $boolean = 'and', Closure $callback = null
725730
}
726731

727732
/**
728-
* Add a relationship count condition to the query with where clauses.
733+
* Add a relationship count/exists condition to the query with where clauses.
729734
*
730735
* @param string $relation
731736
* @param \Closure $callback
@@ -739,7 +744,7 @@ public function whereHas($relation, Closure $callback, $operator = '>=', $count
739744
}
740745

741746
/**
742-
* Add a relationship count condition to the query with where clauses.
747+
* Add a relationship count/exists condition to the query with where clauses.
743748
*
744749
* @param string $relation
745750
* @param \Closure|null $callback
@@ -751,7 +756,7 @@ public function whereDoesntHave($relation, Closure $callback = null)
751756
}
752757

753758
/**
754-
* Add a relationship count condition to the query with an "or".
759+
* Add a relationship count/exists condition to the query with an "or".
755760
*
756761
* @param string $relation
757762
* @param string $operator
@@ -764,7 +769,7 @@ public function orHas($relation, $operator = '>=', $count = 1)
764769
}
765770

766771
/**
767-
* Add a relationship count condition to the query with where clauses and an "or".
772+
* Add a relationship count/exists condition to the query with where clauses and an "or".
768773
*
769774
* @param string $relation
770775
* @param \Closure $callback
@@ -791,11 +796,45 @@ protected function addHasWhere(Builder $hasQuery, Relation $relation, $operator,
791796
{
792797
$this->mergeModelDefinedRelationWheresToHasQuery($hasQuery, $relation);
793798

799+
if($this->shouldRunExistsQuery($operator, $count)) {
800+
$not = ($operator === '<' && $count === 1);
801+
802+
return $this->whereExistsQuery($hasQuery->toBase(), $boolean, $not);
803+
}
804+
805+
return $this->whereCountQuery($hasQuery->toBase(), $operator, $count, $boolean);
806+
}
807+
808+
/**
809+
* Check if we can run an "exists" query to optimize performance.
810+
*
811+
* @param string $operator
812+
* @param int $count
813+
* @return bool
814+
*/
815+
protected function shouldRunExistsQuery($operator, $count)
816+
{
817+
return ($operator === '>=' && $count === 1) || ($operator === '<' && $count === 1);
818+
}
819+
820+
/**
821+
* Add a sub query count clause to the query.
822+
*
823+
* @param \Illuminate\Database\Query\Builder $query
824+
* @param string $operator
825+
* @param int $count
826+
* @param string $boolean
827+
* @return $this
828+
*/
829+
protected function whereCountQuery(QueryBuilder $query, $operator = '>=', $count = 1, $boolean = 'and')
830+
{
794831
if (is_numeric($count)) {
795832
$count = new Expression($count);
796833
}
797834

798-
return $this->where(new Expression('('.$hasQuery->toSql().')'), $operator, $count, $boolean);
835+
$this->query->addBinding($query->getBindings(), 'where');
836+
837+
return $this->where(new Expression('('.$query->toSql().')'), $operator, $count, $boolean);
799838
}
800839

801840
/**
@@ -815,8 +854,6 @@ protected function mergeModelDefinedRelationWheresToHasQuery(Builder $hasQuery,
815854
$hasQuery->mergeWheres(
816855
$relationQuery->wheres, $relationQuery->getBindings()
817856
);
818-
819-
$this->query->addBinding($hasQuery->getBindings(), 'where');
820857
}
821858

822859
/**

‎src/Illuminate/Database/Eloquent/Relations/BelongsTo.php‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -77,35 +77,37 @@ public function addConstraints()
7777
}
7878

7979
/**
80-
* Add the constraints for a relationship count query.
80+
* Add the constraints for a relationship query.
8181
*
8282
* @param \Illuminate\Database\Eloquent\Builder $query
8383
* @param \Illuminate\Database\Eloquent\Builder $parent
84+
* @param array|mixed $columns
8485
* @return \Illuminate\Database\Eloquent\Builder
8586
*/
86-
public function getRelationCountQuery(Builder $query, Builder $parent)
87+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
8788
{
8889
if ($parent->getQuery()->from == $query->getQuery()->from) {
89-
return $this->getRelationCountQueryForSelfRelation($query, $parent);
90+
return $this->getRelationQueryForSelfRelation($query, $parent, $columns);
9091
}
9192

92-
$query->select(new Expression('count(*)'));
93+
$query->select($columns);
9394

9495
$otherKey = $this->wrap($query->getModel()->getTable().'.'.$this->otherKey);
9596

9697
return $query->where($this->getQualifiedForeignKey(), '=', new Expression($otherKey));
9798
}
9899

99100
/**
100-
* Add the constraints for a relationship count query on the same table.
101+
* Add the constraints for a relationship query on the same table.
101102
*
102103
* @param \Illuminate\Database\Eloquent\Builder $query
103104
* @param \Illuminate\Database\Eloquent\Builder $parent
105+
* @param array|mixed $columns
104106
* @return \Illuminate\Database\Eloquent\Builder
105107
*/
106-
public function getRelationCountQueryForSelfRelation(Builder $query, Builder $parent)
108+
public function getRelationQueryForSelfRelation(Builder $query, Builder $parent, $columns = ['*'])
107109
{
108-
$query->select(new Expression('count(*)'));
110+
$query->select($columns);
109111

110112
$query->from($query->getModel()->getTable().' as '.$hash = $this->getRelationCountHash());
111113

‎src/Illuminate/Database/Eloquent/Relations/BelongsToMany.php‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -299,33 +299,35 @@ public function addConstraints()
299299
}
300300

301301
/**
302-
* Add the constraints for a relationship count query.
302+
* Add the constraints for a relationship query.
303303
*
304304
* @param \Illuminate\Database\Eloquent\Builder $query
305305
* @param \Illuminate\Database\Eloquent\Builder $parent
306+
* @param array|mixed $columns
306307
* @return \Illuminate\Database\Eloquent\Builder
307308
*/
308-
public function getRelationCountQuery(Builder $query, Builder $parent)
309+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
309310
{
310311
if ($parent->getQuery()->from == $query->getQuery()->from) {
311-
return $this->getRelationCountQueryForSelfJoin($query, $parent);
312+
return $this->getRelationQueryForSelfJoin($query, $parent, $columns);
312313
}
313314

314315
$this->setJoin($query);
315316

316-
return parent::getRelationCountQuery($query, $parent);
317+
return parent::getRelationQuery($query, $parent, $columns);
317318
}
318319

319320
/**
320-
* Add the constraints for a relationship count query on the same table.
321+
* Add the constraints for a relationship query on the same table.
321322
*
322323
* @param \Illuminate\Database\Eloquent\Builder $query
323324
* @param \Illuminate\Database\Eloquent\Builder $parent
325+
* @param array|mixed $columns
324326
* @return \Illuminate\Database\Eloquent\Builder
325327
*/
326-
public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent)
328+
public function getRelationQueryForSelfJoin(Builder $query, Builder $parent, $columns = ['*'])
327329
{
328-
$query->select(new Expression('count(*)'));
330+
$query->select($columns);
329331

330332
$query->from($this->table.' as '.$hash = $this->getRelationCountHash());
331333

‎src/Illuminate/Database/Eloquent/Relations/HasManyThrough.php‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -79,19 +79,20 @@ public function addConstraints()
7979
}
8080

8181
/**
82-
* Add the constraints for a relationship count query.
82+
* Add the constraints for a relationship query.
8383
*
8484
* @param \Illuminate\Database\Eloquent\Builder $query
8585
* @param \Illuminate\Database\Eloquent\Builder $parent
86+
* @param array|mixed $columns
8687
* @return \Illuminate\Database\Eloquent\Builder
8788
*/
88-
public function getRelationCountQuery(Builder $query, Builder $parent)
89+
public function getRelationQuery(Builder $query, Builder $parent, $columns = '*')
8990
{
9091
$parentTable = $this->parent->getTable();
9192

9293
$this->setJoin($query);
9394

94-
$query->select(new Expression('count(*)'));
95+
$query->select($columns);
9596

9697
$key = $this->wrap($parentTable.'.'.$this->firstKey);
9798

‎src/Illuminate/Database/Eloquent/Relations/HasOneOrMany.php‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -55,31 +55,33 @@ public function addConstraints()
5555
}
5656

5757
/**
58-
* Add the constraints for a relationship count query.
58+
* Add the constraints for a relationship query.
5959
*
6060
* @param \Illuminate\Database\Eloquent\Builder $query
6161
* @param \Illuminate\Database\Eloquent\Builder $parent
62+
* @param array|mixed $columns
6263
* @return \Illuminate\Database\Eloquent\Builder
6364
*/
64-
public function getRelationCountQuery(Builder $query, Builder $parent)
65+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
6566
{
6667
if ($parent->getQuery()->from == $query->getQuery()->from) {
67-
return $this->getRelationCountQueryForSelfRelation($query, $parent);
68+
return $this->getRelationQueryForSelfRelation($query, $parent, $columns);
6869
}
6970

70-
return parent::getRelationCountQuery($query, $parent);
71+
return parent::getRelationQuery($query, $parent, $columns);
7172
}
7273

7374
/**
74-
* Add the constraints for a relationship count query on the same table.
75+
* Add the constraints for a relationship query on the same table.
7576
*
7677
* @param \Illuminate\Database\Eloquent\Builder $query
7778
* @param \Illuminate\Database\Eloquent\Builder $parent
79+
* @param array|mixed $columns
7880
* @return \Illuminate\Database\Eloquent\Builder
7981
*/
80-
public function getRelationCountQueryForSelfRelation(Builder $query, Builder $parent)
82+
public function getRelationQueryForSelfRelation(Builder $query, Builder $parent, $columns = ['*'])
8183
{
82-
$query->select(new Expression('count(*)'));
84+
$query->select($columns);
8385

8486
$query->from($query->getModel()->getTable().' as '.$hash = $this->getRelationCountHash());
8587

‎src/Illuminate/Database/Eloquent/Relations/MorphOneOrMany.php‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -55,15 +55,16 @@ public function addConstraints()
5555
}
5656

5757
/**
58-
* Get the relationship count query.
58+
* Get the relationship query.
5959
*
6060
* @param \Illuminate\Database\Eloquent\Builder $query
6161
* @param \Illuminate\Database\Eloquent\Builder $parent
62+
* @param array|mixed $columns
6263
* @return \Illuminate\Database\Eloquent\Builder
6364
*/
64-
public function getRelationCountQuery(Builder $query, Builder $parent)
65+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
6566
{
66-
$query = parent::getRelationCountQuery($query, $parent);
67+
$query = parent::getRelationQuery($query, $parent, $columns);
6768

6869
return $query->where($this->morphType, $this->morphClass);
6970
}

‎src/Illuminate/Database/Eloquent/Relations/MorphToMany.php‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
namespace Illuminate\Database\Eloquent\Relations;
44

5+
use Illuminate\Database\Query\Expression;
56
use Illuminate\Support\Arr;
67
use Illuminate\Database\Eloquent\Model;
78
use Illuminate\Database\Eloquent\Builder;
@@ -72,11 +73,12 @@ protected function setWhere()
7273
*
7374
* @param \Illuminate\Database\Eloquent\Builder $query
7475
* @param \Illuminate\Database\Eloquent\Builder $parent
76+
* @param array|mixed $columns
7577
* @return \Illuminate\Database\Eloquent\Builder
7678
*/
77-
public function getRelationCountQuery(Builder $query, Builder $parent)
79+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
7880
{
79-
$query = parent::getRelationCountQuery($query, $parent);
81+
$query = parent::getRelationQuery($query, $parent, $columns);
8082

8183
return $query->where($this->table.'.'.$this->morphType, $this->morphClass);
8284
}

‎src/Illuminate/Database/Eloquent/Relations/Relation.php‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -145,7 +145,20 @@ public function rawUpdate(array $attributes = [])
145145
*/
146146
public function getRelationCountQuery(Builder $query, Builder $parent)
147147
{
148-
$query->select(new Expression('count(*)'));
148+
return $this->getRelationQuery($query, $parent, new Expression('count(*)'));
149+
}
150+
151+
/**
152+
* Add the constraints for a relationship count query.
153+
*
154+
* @param \Illuminate\Database\Eloquent\Builder $query
155+
* @param \Illuminate\Database\Eloquent\Builder $parent
156+
* @param array|mixed $columns
157+
* @return \Illuminate\Database\Eloquent\Builder
158+
*/
159+
public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*'])
160+
{
161+
$query->select($columns);
149162

150163
$key = $this->wrap($this->getQualifiedParentKeyName());
151164

‎src/Illuminate/Database/Query/Builder.php‎

Lines changed: 20 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -708,20 +708,14 @@ protected function whereSub($column, $operator, Closure $callback, $boolean)
708708
*/
709709
public function whereExists(Closure $callback, $boolean = 'and', $not = false)
710710
{
711-
$type = $not ? 'NotExists' : 'Exists';
712-
713711
$query = $this->newQuery();
714712

715713
// Similar to the sub-select clause, we will create a new query instance so
716714
// the developer may cleanly specify the entire exists query and we will
717715
// compile the whole thing in the grammar and insert it into the SQL.
718716
call_user_func($callback, $query);
719717

720-
$this->wheres[] = compact('type', 'operator', 'query', 'boolean');
721-
722-
$this->addBinding($query->getBindings(), 'where');
723-
724-
return $this;
718+
return $this->whereExistsQuery($query, $boolean, $not);
725719
}
726720

727721
/**
@@ -748,6 +742,25 @@ public function whereNotExists(Closure $callback, $boolean = 'and')
748742
return $this->whereExists($callback, $boolean, true);
749743
}
750744

745+
/**
746+
* Add an exists clause to the query.
747+
*
748+
* @param \Illuminate\Database\Query\Builder $query
749+
* @param string $boolean
750+
* @param bool $not
751+
* @return $this
752+
*/
753+
public function whereExistsQuery(Builder $query, $boolean = 'and', $not = false)
754+
{
755+
$type = $not ? 'NotExists' : 'Exists';
756+
757+
$this->wheres[] = compact('type', 'operator', 'query', 'boolean');
758+
759+
$this->addBinding($query->getBindings(), 'where');
760+
761+
return $this;
762+
}
763+
751764
/**
752765
* Add a where not exists clause to the query.
753766
*

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL