MediaWiki  master
SelectQueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Wikimedia\Rdbms;
4 
6 
8  public const SORT_ASC = 'ASC';
9 
11  public const SORT_DESC = 'DESC';
12 
16  private $fields = [];
17 
21  private $conds = [];
22 
26  private $caller = __CLASS__;
27 
31  protected $options = [];
32 
36  private $nextAutoAlias = 1;
37 
39  protected $db;
40 
46  public function __construct( IDatabase $db ) {
47  $this->db = $db;
48  }
49 
57  public function connection( IDatabase $db ) {
58  if ( $this->db->getType() !== $db->getType() ) {
59  throw new \InvalidArgumentException( __METHOD__ .
60  ' cannot switch to a database of a different type.' );
61  }
62  $this->db = $db;
63  return $this;
64  }
65 
85  public function queryInfo( $info ) {
86  if ( isset( $info['tables'] ) ) {
87  $this->rawTables( $info['tables'] );
88  }
89  if ( isset( $info['fields'] ) ) {
90  $this->fields( $info['fields'] );
91  }
92  if ( isset( $info['conds'] ) ) {
93  $this->where( $info['conds'] );
94  }
95  if ( isset( $info['options'] ) ) {
96  $this->options( (array)$info['options'] );
97  }
98  if ( isset( $info['join_conds'] ) ) {
99  $this->joinConds( (array)$info['join_conds'] );
100  }
101  if ( isset( $info['joins'] ) ) {
102  $this->joinConds( (array)$info['joins'] );
103  }
104  return $this;
105  }
106 
119  public function rawTables( $tables ) {
120  if ( is_array( $tables ) ) {
121  $this->tables = array_merge( $this->tables, $tables );
122  } elseif ( is_string( $tables ) ) {
123  $this->tables[] = $tables;
124  } else {
125  throw new \InvalidArgumentException( __METHOD__ .
126  ': $tables must be a string or array' );
127  }
128  return $this;
129  }
130 
136  public function newSubquery() {
137  return new self( $this->db );
138  }
139 
147  public function from( $table, $alias = null ) {
148  return $this->table( $table, $alias );
149  }
150 
157  public function tables( $tables ) {
158  foreach ( $tables as $alias => $table ) {
159  if ( is_string( $alias ) ) {
160  $this->table( $table, $alias );
161  } else {
162  $this->table( $table );
163  }
164  }
165  return $this;
166  }
167 
178  public function fields( $fields ) {
179  if ( is_array( $fields ) ) {
180  $this->fields = array_merge( $this->fields, $fields );
181  } else {
182  $this->fields[] = $fields;
183  }
184  return $this;
185  }
186 
193  public function select( $fields ) {
194  return $this->fields( $fields );
195  }
196 
205  public function field( $field, $alias = null ) {
206  if ( $alias === null ) {
207  $this->fields[] = $field;
208  } else {
209  $this->fields[$alias] = $field;
210  }
211  return $this;
212  }
213 
250  public function where( $conds ) {
251  if ( is_array( $conds ) ) {
252  $this->conds = array_merge( $this->conds, $conds );
253  } else {
254  $this->conds[] = $conds;
255  }
256  return $this;
257  }
258 
265  public function andWhere( $conds ) {
266  return $this->where( $conds );
267  }
268 
275  public function conds( $conds ) {
276  return $this->where( $conds );
277  }
278 
287  public function joinConds( array $joinConds ) {
288  $this->joinConds = array_merge( $this->joinConds, $joinConds );
289  return $this;
290  }
291 
297  protected function getAutoAlias() {
298  return 'sqb' . ( $this->nextAutoAlias++ );
299  }
300 
307  public function newJoinGroup() {
308  return new JoinGroup( $this->getAutoAlias() );
309  }
310 
321  public function offset( $offset ) {
322  $this->options['OFFSET'] = $offset;
323  return $this;
324  }
325 
336  public function limit( $limit ) {
337  $this->options['LIMIT'] = $limit;
338  return $this;
339  }
340 
348  public function lockInShareMode() {
349  $this->options[] = 'LOCK IN SHARE MODE';
350  return $this;
351  }
352 
360  public function forUpdate() {
361  $this->options[] = 'FOR UPDATE';
362  return $this;
363  }
364 
370  public function distinct() {
371  $this->options[] = 'DISTINCT';
372  return $this;
373  }
374 
384  public function groupBy( $group ) {
385  $this->mergeOption( 'GROUP BY', $group );
386  return $this;
387  }
388 
400  public function having( $having ) {
401  $this->mergeOption( 'HAVING', $having );
402  return $this;
403  }
404 
415  public function orderBy( $fields, $direction = null ) {
416  if ( $direction === null ) {
417  $this->mergeOption( 'ORDER BY', $fields );
418  } elseif ( is_array( $fields ) ) {
419  $fieldsWithDirection = [];
420  foreach ( $fields as $field ) {
421  $fieldsWithDirection[] = "$field $direction";
422  }
423  $this->mergeOption( 'ORDER BY', $fieldsWithDirection );
424  } else {
425  $this->mergeOption( 'ORDER BY', "$fields $direction" );
426  }
427  return $this;
428  }
429 
436  private function mergeOption( $name, $newArrayOrValue ) {
437  $value = isset( $this->options[$name] )
438  ? (array)$this->options[$name] : [];
439  if ( is_array( $newArrayOrValue ) ) {
440  $value = array_merge( $value, $newArrayOrValue );
441  } else {
442  $value[] = $newArrayOrValue;
443  }
444  $this->options[$name] = $value;
445  }
446 
459  public function useIndex( $index ) {
460  $this->setIndexHint( 'USE INDEX', $index );
461  return $this;
462  }
463 
476  public function ignoreIndex( $index ) {
477  $this->setIndexHint( 'IGNORE INDEX', $index );
478  return $this;
479  }
480 
487  private function setIndexHint( $type, $value ) {
488  if ( !isset( $this->options[$type] ) ) {
489  $this->options[$type] = [];
490  } elseif ( !is_array( $this->options[$type] ) ) {
491  throw new \UnexpectedValueException(
492  __METHOD__ . ": The $type option cannot be appended to " .
493  'because it is not an array. This may have been caused by a prior ' .
494  'call to option() or options().' );
495  }
496  if ( is_array( $value ) ) {
497  $this->options[$type] = array_merge( $this->options[$type], $value );
498  } elseif ( $this->lastAlias === null ) {
499  throw new \UnexpectedValueException(
500  __METHOD__ . ': Cannot append index value since there is no' .
501  'prior table' );
502  } else {
503  $this->options[$type][$this->lastAlias] = $value;
504  }
505  }
506 
512  public function explain() {
513  $this->options['EXPLAIN'] = true;
514  return $this;
515  }
516 
522  public function straightJoin() {
523  $this->options[] = 'STRAIGHT_JOIN';
524  return $this;
525  }
526 
532  public function bigResult() {
533  $this->options[] = 'SQL_BIG_RESULT';
534  return $this;
535  }
536 
542  public function bufferResult() {
543  $this->options[] = 'SQL_BUFFER_RESULT';
544  return $this;
545  }
546 
552  public function smallResult() {
553  $this->options[] = 'SQL_SMALL_RESULT';
554  return $this;
555  }
556 
562  public function calcFoundRows() {
563  $this->options[] = 'SQL_CALC_FOUND_ROWS';
564  return $this;
565  }
566 
575  public function option( $name, $value = null ) {
576  if ( $value === null ) {
577  $this->options[] = $name;
578  } else {
579  $this->options[$name] = $value;
580  }
581  return $this;
582  }
583 
591  public function options( array $options ) {
592  $this->options = array_merge( $this->options, $options );
593  return $this;
594  }
595 
602  public function caller( $fname ) {
603  $this->caller = $fname;
604  return $this;
605  }
606 
612  public function fetchResultSet() {
613  return $this->db->select( $this->tables, $this->fields, $this->conds, $this->caller,
614  $this->options, $this->joinConds );
615  }
616 
624  public function fetchField() {
625  if ( count( $this->fields ) !== 1 ) {
626  throw new \UnexpectedValueException(
627  __METHOD__ . ' expects the query to have only one field' );
628  }
629  $field = reset( $this->fields );
630  return $this->db->selectField( $this->tables, $field, $this->conds, $this->caller,
631  $this->options, $this->joinConds );
632  }
633 
641  public function fetchFieldValues() {
642  if ( count( $this->fields ) !== 1 ) {
643  throw new \UnexpectedValueException(
644  __METHOD__ . ' expects the query to have only one field' );
645  }
646  $field = reset( $this->fields );
647  return $this->db->selectFieldValues( $this->tables, $field, $this->conds, $this->caller,
648  $this->options, $this->joinConds );
649  }
650 
657  public function fetchRow() {
658  return $this->db->selectRow( $this->tables, $this->fields, $this->conds, $this->caller,
659  $this->options, $this->joinConds );
660  }
661 
669  public function fetchRowCount() {
670  return $this->db->selectRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
671  $this->caller, $this->options, $this->joinConds );
672  }
673 
684  public function estimateRowCount() {
685  return $this->db->estimateRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
686  $this->caller, $this->options, $this->joinConds );
687  }
688 
695  private function getRowCountVar() {
696  if ( count( $this->fields ) === 0 ) {
697  return '*';
698  } elseif ( count( $this->fields ) === 1 ) {
699  return reset( $this->fields );
700  } else {
701  throw new \UnexpectedValueException(
702  __METHOD__ . ' expects the query to have at most one field' );
703  }
704  }
705 
711  public function lockForUpdate() {
712  return $this->db->lockForUpdate( $this->tables, $this->conds, $this->caller,
713  $this->options, $this->joinConds );
714  }
715 
727  public function buildGroupConcatField( $delim ) {
728  if ( count( $this->fields ) !== 1 ) {
729  throw new \UnexpectedValueException(
730  __METHOD__ . ' expects the query to have only one field' );
731  }
732  $field = reset( $this->fields );
733  return $this->db->buildGroupConcatField( $delim, $this->tables, $field,
734  $this->conds, $this->joinConds );
735  }
736 
742  public function getSQL() {
743  return $this->db->selectSQLText( $this->tables, $this->fields, $this->conds, $this->caller,
744  $this->options, $this->joinConds );
745  }
746 
761  public function getQueryInfo( $joinsName = 'join_conds' ) {
762  $info = [
763  'tables' => $this->tables,
764  'fields' => $this->fields,
765  'conds' => $this->conds,
766  'options' => $this->options,
767  ];
768  $info[ $joinsName ] = $this->joinConds;
769  return $info;
770  }
771 }
Wikimedia\Rdbms\JoinGroupBase\$joinConds
array $joinConds
Definition: JoinGroupBase.php:14
Wikimedia\Rdbms\SelectQueryBuilder\newSubquery
newSubquery()
Get an empty SelectQueryBuilder which can be used to build a subquery of this query.
Definition: SelectQueryBuilder.php:136
Wikimedia\Rdbms\SelectQueryBuilder\calcFoundRows
calcFoundRows()
Enable the SQL_CALC_FOUND_ROWS option.
Definition: SelectQueryBuilder.php:562
Wikimedia\Rdbms\SelectQueryBuilder\joinConds
joinConds(array $joinConds)
Manually append to the $join_conds array which will be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:287
Wikimedia\Rdbms\SelectQueryBuilder\$conds
array $conds
The conditions to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:21
Wikimedia\Rdbms\SelectQueryBuilder\mergeOption
mergeOption( $name, $newArrayOrValue)
Add a value to an option which may be not set or a string or array.
Definition: SelectQueryBuilder.php:436
Wikimedia\Rdbms\SelectQueryBuilder\getRowCountVar
getRowCountVar()
Private helper which extracts a field suitable for row counting from the fields array.
Definition: SelectQueryBuilder.php:695
Wikimedia\Rdbms\SelectQueryBuilder\limit
limit( $limit)
Set the query limit.
Definition: SelectQueryBuilder.php:336
Wikimedia\Rdbms\SelectQueryBuilder\options
options(array $options)
Manually set multiple options in the $options array to be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:591
Wikimedia\Rdbms\SelectQueryBuilder\rawTables
rawTables( $tables)
Given a table or table array as might be passed to Database::select(), append it to the existing tabl...
Definition: SelectQueryBuilder.php:119
Wikimedia\Rdbms\SelectQueryBuilder\useIndex
useIndex( $index)
Set a USE INDEX option.
Definition: SelectQueryBuilder.php:459
Wikimedia\Rdbms\SelectQueryBuilder\connection
connection(IDatabase $db)
Change the IDatabase object the query builder is bound to.
Definition: SelectQueryBuilder.php:57
Wikimedia\Rdbms\SelectQueryBuilder\fetchResultSet
fetchResultSet()
Run the constructed SELECT query and return all results.
Definition: SelectQueryBuilder.php:612
Wikimedia\Rdbms\SelectQueryBuilder\tables
tables( $tables)
Add multiple tables.
Definition: SelectQueryBuilder.php:157
Wikimedia\Rdbms\JoinGroup
An object representing a parenthesized group of tables and their join types and conditions.
Definition: JoinGroup.php:9
Wikimedia\Rdbms
Definition: ChronologyProtector.php:24
Wikimedia\Rdbms\SelectQueryBuilder\getAutoAlias
getAutoAlias()
Get a table alias which is unique to this SelectQueryBuilder.
Definition: SelectQueryBuilder.php:297
Wikimedia\Rdbms\SelectQueryBuilder\$caller
string $caller
The caller (function name) to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:26
Wikimedia\Rdbms\IDatabase
Basic database interface for live and lazy-loaded relation database handles.
Definition: IDatabase.php:38
Wikimedia\Rdbms\SelectQueryBuilder\bufferResult
bufferResult()
Enable the SQL_BUFFER_RESULT option.
Definition: SelectQueryBuilder.php:542
Wikimedia\Rdbms\SelectQueryBuilder\distinct
distinct()
Enable the DISTINCT option.
Definition: SelectQueryBuilder.php:370
Wikimedia\Rdbms\JoinGroupBase
A class for code shared between SelectQueryBuilder and JoinGroup.
Definition: JoinGroupBase.php:9
Wikimedia\Rdbms\SelectQueryBuilder\__construct
__construct(IDatabase $db)
Definition: SelectQueryBuilder.php:46
Wikimedia\Rdbms\SelectQueryBuilder\newJoinGroup
newJoinGroup()
Create a parenthesized group of joins which can be added to the object like a table.
Definition: SelectQueryBuilder.php:307
Wikimedia\Rdbms\SelectQueryBuilder\caller
caller( $fname)
Set the method name to be included in an SQL comment.
Definition: SelectQueryBuilder.php:602
Wikimedia\Rdbms\SelectQueryBuilder
Definition: SelectQueryBuilder.php:5
Wikimedia\Rdbms\SelectQueryBuilder\forUpdate
forUpdate()
Enable the FOR UPDATE option.
Definition: SelectQueryBuilder.php:360
Wikimedia\Rdbms\JoinGroupBase\$lastAlias
$lastAlias
Definition: JoinGroupBase.php:16
Wikimedia\Rdbms\SelectQueryBuilder\straightJoin
straightJoin()
Enable the STRAIGHT_JOIN option.
Definition: SelectQueryBuilder.php:522
Wikimedia\Rdbms\SelectQueryBuilder\fetchRowCount
fetchRowCount()
Run the SELECT query, and return the number of results.
Definition: SelectQueryBuilder.php:669
Wikimedia\Rdbms\SelectQueryBuilder\fields
fields( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:178
Wikimedia\Rdbms\SelectQueryBuilder\lockInShareMode
lockInShareMode()
Enable the LOCK IN SHARE MODE option.
Definition: SelectQueryBuilder.php:348
Wikimedia\Rdbms\SelectQueryBuilder\option
option( $name, $value=null)
Manually set an option in the $options array to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:575
Wikimedia\Rdbms\SelectQueryBuilder\getQueryInfo
getQueryInfo( $joinsName='join_conds')
Get an associative array describing the query in terms of its raw parameters to Database::select().
Definition: SelectQueryBuilder.php:761
Wikimedia\Rdbms\SelectQueryBuilder\offset
offset( $offset)
Set the offset.
Definition: SelectQueryBuilder.php:321
Wikimedia\Rdbms\SelectQueryBuilder\$options
array $options
The options to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:31
Wikimedia\Rdbms\SelectQueryBuilder\fetchRow
fetchRow()
Run the constructed SELECT query, and return the first result row.
Definition: SelectQueryBuilder.php:657
Wikimedia\Rdbms\SelectQueryBuilder\queryInfo
queryInfo( $info)
Set the query parameters to the given values, appending to the values which were already set.
Definition: SelectQueryBuilder.php:85
Wikimedia\Rdbms\SelectQueryBuilder\fetchFieldValues
fetchFieldValues()
Run the constructed SELECT query, and extract a single field from each result row,...
Definition: SelectQueryBuilder.php:641
Wikimedia\Rdbms\JoinGroupBase\$tables
array $tables
Definition: JoinGroupBase.php:11
Wikimedia\Rdbms\SelectQueryBuilder\$nextAutoAlias
int $nextAutoAlias
An integer used to assign automatic aliases to tables and groups.
Definition: SelectQueryBuilder.php:36
Wikimedia\Rdbms\SelectQueryBuilder\lockForUpdate
lockForUpdate()
Run the SELECT query with the FOR UPDATE option.
Definition: SelectQueryBuilder.php:711
Wikimedia\Rdbms\SelectQueryBuilder\conds
conds( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:275
Wikimedia\Rdbms\SelectQueryBuilder\andWhere
andWhere( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:265
Wikimedia\Rdbms\SelectQueryBuilder\bigResult
bigResult()
Enable the SQL_BIG_RESULT option.
Definition: SelectQueryBuilder.php:532
Wikimedia\Rdbms\SelectQueryBuilder\buildGroupConcatField
buildGroupConcatField( $delim)
Build a GROUP_CONCAT or equivalent statement for a query.
Definition: SelectQueryBuilder.php:727
Wikimedia\Rdbms\SelectQueryBuilder\explain
explain()
Make the query be an EXPLAIN SELECT query instead of a SELECT query.
Definition: SelectQueryBuilder.php:512
Wikimedia\Rdbms\SelectQueryBuilder\having
having( $having)
Add a HAVING clause.
Definition: SelectQueryBuilder.php:400
Wikimedia\Rdbms\IDatabase\getType
getType()
Get the type of the DBMS (e.g.
Wikimedia\Rdbms\SelectQueryBuilder\ignoreIndex
ignoreIndex( $index)
Set the IGNORE INDEX option.
Definition: SelectQueryBuilder.php:476
Wikimedia\Rdbms\SelectQueryBuilder\smallResult
smallResult()
Enable the SQL_SMALL_RESULT option.
Definition: SelectQueryBuilder.php:552
Wikimedia\Rdbms\SelectQueryBuilder\setIndexHint
setIndexHint( $type, $value)
Private helper for methods that set index hints.
Definition: SelectQueryBuilder.php:487
Wikimedia\Rdbms\SelectQueryBuilder\where
where( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:250
Wikimedia\Rdbms\SelectQueryBuilder\field
field( $field, $alias=null)
Add a single field to the query, optionally with an alias.
Definition: SelectQueryBuilder.php:205
Wikimedia\Rdbms\SelectQueryBuilder\orderBy
orderBy( $fields, $direction=null)
Set the ORDER BY clause.
Definition: SelectQueryBuilder.php:415
Wikimedia\Rdbms\SelectQueryBuilder\select
select( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:193
Wikimedia\Rdbms\SelectQueryBuilder\$db
IDatabase $db
Definition: SelectQueryBuilder.php:39
Wikimedia\Rdbms\SelectQueryBuilder\fetchField
fetchField()
Run the constructed SELECT query, and return a single field extracted from the first result row.
Definition: SelectQueryBuilder.php:624
Wikimedia\Rdbms\SelectQueryBuilder\getSQL
getSQL()
Get the SQL query string which would be used by fetchResultSet().
Definition: SelectQueryBuilder.php:742
Wikimedia\Rdbms\SelectQueryBuilder\groupBy
groupBy( $group)
Add a GROUP BY clause.
Definition: SelectQueryBuilder.php:384
Wikimedia\Rdbms\SelectQueryBuilder\$fields
array $fields
The fields to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:16
Wikimedia\Rdbms\SelectQueryBuilder\from
from( $table, $alias=null)
Add a single table to the SELECT query.
Definition: SelectQueryBuilder.php:147
Wikimedia\Rdbms\SelectQueryBuilder\estimateRowCount
estimateRowCount()
Estimate the number of rows in dataset.
Definition: SelectQueryBuilder.php:684
Wikimedia\Rdbms\JoinGroupBase\table
table( $table, $alias=null)
Add a single table or a single parenthesized group.
Definition: JoinGroupBase.php:28
$type
$type
Definition: testCompression.php:52