MediaWiki  master
SelectQueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Wikimedia\Rdbms;
4 
12 
14  public const SORT_ASC = 'ASC';
15 
17  public const SORT_DESC = 'DESC';
18 
22  private $fields = [];
23 
27  private $conds = [];
28 
32  private $caller = __CLASS__;
33 
37  protected $options = [];
38 
42  private $nextAutoAlias = 1;
43 
45  protected $db;
46 
52  public function __construct( IDatabase $db ) {
53  $this->db = $db;
54  }
55 
63  public function connection( IDatabase $db ) {
64  if ( $this->db->getType() !== $db->getType() ) {
65  throw new \InvalidArgumentException( __METHOD__ .
66  ' cannot switch to a database of a different type.' );
67  }
68  $this->db = $db;
69  return $this;
70  }
71 
91  public function queryInfo( $info ) {
92  if ( isset( $info['tables'] ) ) {
93  $this->rawTables( $info['tables'] );
94  }
95  if ( isset( $info['fields'] ) ) {
96  $this->fields( $info['fields'] );
97  }
98  if ( isset( $info['conds'] ) ) {
99  $this->where( $info['conds'] );
100  }
101  if ( isset( $info['options'] ) ) {
102  $this->options( (array)$info['options'] );
103  }
104  if ( isset( $info['join_conds'] ) ) {
105  $this->joinConds( (array)$info['join_conds'] );
106  }
107  if ( isset( $info['joins'] ) ) {
108  $this->joinConds( (array)$info['joins'] );
109  }
110  return $this;
111  }
112 
125  public function rawTables( $tables ) {
126  if ( is_array( $tables ) ) {
127  $this->tables = array_merge( $this->tables, $tables );
128  } elseif ( is_string( $tables ) ) {
129  $this->tables[] = $tables;
130  } else {
131  throw new \InvalidArgumentException( __METHOD__ .
132  ': $tables must be a string or array' );
133  }
134  return $this;
135  }
136 
142  public function newSubquery() {
143  return new self( $this->db );
144  }
145 
153  public function from( $table, $alias = null ) {
154  return $this->table( $table, $alias );
155  }
156 
163  public function tables( $tables ) {
164  foreach ( $tables as $alias => $table ) {
165  if ( is_string( $alias ) ) {
166  $this->table( $table, $alias );
167  } else {
168  $this->table( $table );
169  }
170  }
171  return $this;
172  }
173 
184  public function fields( $fields ) {
185  if ( is_array( $fields ) ) {
186  $this->fields = array_merge( $this->fields, $fields );
187  } else {
188  $this->fields[] = $fields;
189  }
190  return $this;
191  }
192 
199  public function select( $fields ) {
200  return $this->fields( $fields );
201  }
202 
211  public function field( $field, $alias = null ) {
212  if ( $alias === null ) {
213  $this->fields[] = $field;
214  } else {
215  $this->fields[$alias] = $field;
216  }
217  return $this;
218  }
219 
256  public function where( $conds ) {
257  if ( is_array( $conds ) ) {
258  foreach ( $conds as $key => $cond ) {
259  if ( is_int( $key ) ) {
260  $this->conds[] = $cond;
261  } elseif ( isset( $this->conds[$key] ) ) {
262  // T288882
263  $this->conds[] = $this->db->makeList(
264  [ $key => $cond ], IDatabase::LIST_AND );
265  } else {
266  $this->conds[$key] = $cond;
267  }
268  }
269  } else {
270  $this->conds[] = $conds;
271  }
272  return $this;
273  }
274 
281  public function andWhere( $conds ) {
282  return $this->where( $conds );
283  }
284 
291  public function conds( $conds ) {
292  return $this->where( $conds );
293  }
294 
303  public function joinConds( array $joinConds ) {
304  $this->joinConds = array_merge( $this->joinConds, $joinConds );
305  return $this;
306  }
307 
313  protected function getAutoAlias() {
314  return 'sqb' . ( $this->nextAutoAlias++ );
315  }
316 
323  public function newJoinGroup() {
324  return new JoinGroup( $this->getAutoAlias() );
325  }
326 
337  public function offset( $offset ) {
338  $this->options['OFFSET'] = $offset;
339  return $this;
340  }
341 
352  public function limit( $limit ) {
353  $this->options['LIMIT'] = $limit;
354  return $this;
355  }
356 
364  public function lockInShareMode() {
365  $this->options[] = 'LOCK IN SHARE MODE';
366  return $this;
367  }
368 
376  public function forUpdate() {
377  $this->options[] = 'FOR UPDATE';
378  return $this;
379  }
380 
386  public function distinct() {
387  $this->options[] = 'DISTINCT';
388  return $this;
389  }
390 
400  public function groupBy( $group ) {
401  $this->mergeOption( 'GROUP BY', $group );
402  return $this;
403  }
404 
416  public function having( $having ) {
417  $this->mergeOption( 'HAVING', $having );
418  return $this;
419  }
420 
431  public function orderBy( $fields, $direction = null ) {
432  if ( $direction === null ) {
433  $this->mergeOption( 'ORDER BY', $fields );
434  } elseif ( is_array( $fields ) ) {
435  $fieldsWithDirection = [];
436  foreach ( $fields as $field ) {
437  $fieldsWithDirection[] = "$field $direction";
438  }
439  $this->mergeOption( 'ORDER BY', $fieldsWithDirection );
440  } else {
441  $this->mergeOption( 'ORDER BY', "$fields $direction" );
442  }
443  return $this;
444  }
445 
452  private function mergeOption( $name, $newArrayOrValue ) {
453  $value = isset( $this->options[$name] )
454  ? (array)$this->options[$name] : [];
455  if ( is_array( $newArrayOrValue ) ) {
456  $value = array_merge( $value, $newArrayOrValue );
457  } else {
458  $value[] = $newArrayOrValue;
459  }
460  $this->options[$name] = $value;
461  }
462 
475  public function useIndex( $index ) {
476  $this->setIndexHint( 'USE INDEX', $index );
477  return $this;
478  }
479 
492  public function ignoreIndex( $index ) {
493  $this->setIndexHint( 'IGNORE INDEX', $index );
494  return $this;
495  }
496 
503  private function setIndexHint( $type, $value ) {
504  if ( !isset( $this->options[$type] ) ) {
505  $this->options[$type] = [];
506  } elseif ( !is_array( $this->options[$type] ) ) {
507  throw new \UnexpectedValueException(
508  __METHOD__ . ": The $type option cannot be appended to " .
509  'because it is not an array. This may have been caused by a prior ' .
510  'call to option() or options().' );
511  }
512  if ( is_array( $value ) ) {
513  $this->options[$type] = array_merge( $this->options[$type], $value );
514  } elseif ( $this->lastAlias === null ) {
515  throw new \UnexpectedValueException(
516  __METHOD__ . ': Cannot append index value since there is no' .
517  'prior table' );
518  } else {
519  $this->options[$type][$this->lastAlias] = $value;
520  }
521  }
522 
528  public function explain() {
529  $this->options['EXPLAIN'] = true;
530  return $this;
531  }
532 
538  public function straightJoin() {
539  $this->options[] = 'STRAIGHT_JOIN';
540  return $this;
541  }
542 
548  public function bigResult() {
549  $this->options[] = 'SQL_BIG_RESULT';
550  return $this;
551  }
552 
558  public function bufferResult() {
559  $this->options[] = 'SQL_BUFFER_RESULT';
560  return $this;
561  }
562 
568  public function smallResult() {
569  $this->options[] = 'SQL_SMALL_RESULT';
570  return $this;
571  }
572 
578  public function calcFoundRows() {
579  $this->options[] = 'SQL_CALC_FOUND_ROWS';
580  return $this;
581  }
582 
591  public function option( $name, $value = null ) {
592  if ( $value === null ) {
593  $this->options[] = $name;
594  } else {
595  $this->options[$name] = $value;
596  }
597  return $this;
598  }
599 
607  public function options( array $options ) {
608  $this->options = array_merge( $this->options, $options );
609  return $this;
610  }
611 
618  public function caller( $fname ) {
619  $this->caller = $fname;
620  return $this;
621  }
622 
628  public function fetchResultSet() {
629  return $this->db->select( $this->tables, $this->fields, $this->conds, $this->caller,
630  $this->options, $this->joinConds );
631  }
632 
640  public function fetchField() {
641  if ( count( $this->fields ) !== 1 ) {
642  throw new \UnexpectedValueException(
643  __METHOD__ . ' expects the query to have only one field' );
644  }
645  $field = reset( $this->fields );
646  return $this->db->selectField( $this->tables, $field, $this->conds, $this->caller,
647  $this->options, $this->joinConds );
648  }
649 
657  public function fetchFieldValues() {
658  if ( count( $this->fields ) !== 1 ) {
659  throw new \UnexpectedValueException(
660  __METHOD__ . ' expects the query to have only one field' );
661  }
662  $field = reset( $this->fields );
663  return $this->db->selectFieldValues( $this->tables, $field, $this->conds, $this->caller,
664  $this->options, $this->joinConds );
665  }
666 
673  public function fetchRow() {
674  return $this->db->selectRow( $this->tables, $this->fields, $this->conds, $this->caller,
675  $this->options, $this->joinConds );
676  }
677 
685  public function fetchRowCount() {
686  return $this->db->selectRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
687  $this->caller, $this->options, $this->joinConds );
688  }
689 
700  public function estimateRowCount() {
701  return $this->db->estimateRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
702  $this->caller, $this->options, $this->joinConds );
703  }
704 
711  private function getRowCountVar() {
712  if ( count( $this->fields ) === 0 ) {
713  return '*';
714  } elseif ( count( $this->fields ) === 1 ) {
715  return reset( $this->fields );
716  } else {
717  throw new \UnexpectedValueException(
718  __METHOD__ . ' expects the query to have at most one field' );
719  }
720  }
721 
727  public function lockForUpdate() {
728  return $this->db->lockForUpdate( $this->tables, $this->conds, $this->caller,
729  $this->options, $this->joinConds );
730  }
731 
743  public function buildGroupConcatField( $delim ) {
744  if ( count( $this->fields ) !== 1 ) {
745  throw new \UnexpectedValueException(
746  __METHOD__ . ' expects the query to have only one field' );
747  }
748  $field = reset( $this->fields );
749  return $this->db->buildGroupConcatField( $delim, $this->tables, $field,
750  $this->conds, $this->joinConds );
751  }
752 
758  public function getSQL() {
759  return $this->db->selectSQLText( $this->tables, $this->fields, $this->conds, $this->caller,
760  $this->options, $this->joinConds );
761  }
762 
777  public function getQueryInfo( $joinsName = 'join_conds' ) {
778  $info = [
779  'tables' => $this->tables,
780  'fields' => $this->fields,
781  'conds' => $this->conds,
782  'options' => $this->options,
783  ];
784  $info[ $joinsName ] = $this->joinConds;
785  return $info;
786  }
787 }
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:142
Wikimedia\Rdbms\SelectQueryBuilder\calcFoundRows
calcFoundRows()
Enable the SQL_CALC_FOUND_ROWS option.
Definition: SelectQueryBuilder.php:578
Wikimedia\Rdbms\SelectQueryBuilder\joinConds
joinConds(array $joinConds)
Manually append to the $join_conds array which will be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:303
Wikimedia\Rdbms\SelectQueryBuilder\$conds
array $conds
The conditions to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:27
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:452
Wikimedia\Rdbms\SelectQueryBuilder\getRowCountVar
getRowCountVar()
Private helper which extracts a field suitable for row counting from the fields array.
Definition: SelectQueryBuilder.php:711
Wikimedia\Rdbms\SelectQueryBuilder\limit
limit( $limit)
Set the query limit.
Definition: SelectQueryBuilder.php:352
Wikimedia\Rdbms\SelectQueryBuilder\options
options(array $options)
Manually set multiple options in the $options array to be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:607
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:125
Wikimedia\Rdbms\SelectQueryBuilder\useIndex
useIndex( $index)
Set a USE INDEX option.
Definition: SelectQueryBuilder.php:475
Wikimedia\Rdbms\SelectQueryBuilder\connection
connection(IDatabase $db)
Change the IDatabase object the query builder is bound to.
Definition: SelectQueryBuilder.php:63
Wikimedia\Rdbms\SelectQueryBuilder\fetchResultSet
fetchResultSet()
Run the constructed SELECT query and return all results.
Definition: SelectQueryBuilder.php:628
LIST_AND
const LIST_AND
Definition: Defines.php:43
Wikimedia\Rdbms\SelectQueryBuilder\tables
tables( $tables)
Add multiple tables.
Definition: SelectQueryBuilder.php:163
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:313
Wikimedia\Rdbms\SelectQueryBuilder\$caller
string $caller
The caller (function name) to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:32
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:558
Wikimedia\Rdbms\SelectQueryBuilder\distinct
distinct()
Enable the DISTINCT option.
Definition: SelectQueryBuilder.php:386
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:52
Wikimedia\Rdbms\SelectQueryBuilder\newJoinGroup
newJoinGroup()
Create a parenthesized group of joins which can be added to the object like a table.
Definition: SelectQueryBuilder.php:323
Wikimedia\Rdbms\SelectQueryBuilder\caller
caller( $fname)
Set the method name to be included in an SQL comment.
Definition: SelectQueryBuilder.php:618
Wikimedia\Rdbms\SelectQueryBuilder
Definition: SelectQueryBuilder.php:11
Wikimedia\Rdbms\SelectQueryBuilder\forUpdate
forUpdate()
Enable the FOR UPDATE option.
Definition: SelectQueryBuilder.php:376
Wikimedia\Rdbms\JoinGroupBase\$lastAlias
$lastAlias
Definition: JoinGroupBase.php:16
Wikimedia\Rdbms\SelectQueryBuilder\straightJoin
straightJoin()
Enable the STRAIGHT_JOIN option.
Definition: SelectQueryBuilder.php:538
Wikimedia\Rdbms\SelectQueryBuilder\fetchRowCount
fetchRowCount()
Run the SELECT query, and return the number of results.
Definition: SelectQueryBuilder.php:685
Wikimedia\Rdbms\SelectQueryBuilder\fields
fields( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:184
Wikimedia\Rdbms\SelectQueryBuilder\lockInShareMode
lockInShareMode()
Enable the LOCK IN SHARE MODE option.
Definition: SelectQueryBuilder.php:364
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:591
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:777
Wikimedia\Rdbms\SelectQueryBuilder\offset
offset( $offset)
Set the offset.
Definition: SelectQueryBuilder.php:337
Wikimedia\Rdbms\SelectQueryBuilder\$options
array $options
The options to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:37
Wikimedia\Rdbms\SelectQueryBuilder\fetchRow
fetchRow()
Run the constructed SELECT query, and return the first result row.
Definition: SelectQueryBuilder.php:673
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:91
Wikimedia\Rdbms\SelectQueryBuilder\fetchFieldValues
fetchFieldValues()
Run the constructed SELECT query, and extract a single field from each result row,...
Definition: SelectQueryBuilder.php:657
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:42
Wikimedia\Rdbms\SelectQueryBuilder\lockForUpdate
lockForUpdate()
Run the SELECT query with the FOR UPDATE option.
Definition: SelectQueryBuilder.php:727
Wikimedia\Rdbms\SelectQueryBuilder\conds
conds( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:291
Wikimedia\Rdbms\SelectQueryBuilder\andWhere
andWhere( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:281
Wikimedia\Rdbms\SelectQueryBuilder\bigResult
bigResult()
Enable the SQL_BIG_RESULT option.
Definition: SelectQueryBuilder.php:548
Wikimedia\Rdbms\SelectQueryBuilder\buildGroupConcatField
buildGroupConcatField( $delim)
Build a GROUP_CONCAT or equivalent statement for a query.
Definition: SelectQueryBuilder.php:743
Wikimedia\Rdbms\SelectQueryBuilder\explain
explain()
Make the query be an EXPLAIN SELECT query instead of a SELECT query.
Definition: SelectQueryBuilder.php:528
Wikimedia\Rdbms\SelectQueryBuilder\having
having( $having)
Add a HAVING clause.
Definition: SelectQueryBuilder.php:416
Wikimedia\Rdbms\IDatabase\getType
getType()
Get the RDBMS type of the server (e.g.
Wikimedia\Rdbms\SelectQueryBuilder\ignoreIndex
ignoreIndex( $index)
Set the IGNORE INDEX option.
Definition: SelectQueryBuilder.php:492
Wikimedia\Rdbms\SelectQueryBuilder\smallResult
smallResult()
Enable the SQL_SMALL_RESULT option.
Definition: SelectQueryBuilder.php:568
Wikimedia\Rdbms\SelectQueryBuilder\setIndexHint
setIndexHint( $type, $value)
Private helper for methods that set index hints.
Definition: SelectQueryBuilder.php:503
Wikimedia\Rdbms\SelectQueryBuilder\where
where( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:256
Wikimedia\Rdbms\SelectQueryBuilder\field
field( $field, $alias=null)
Add a single field to the query, optionally with an alias.
Definition: SelectQueryBuilder.php:211
Wikimedia\Rdbms\SelectQueryBuilder\orderBy
orderBy( $fields, $direction=null)
Set the ORDER BY clause.
Definition: SelectQueryBuilder.php:431
Wikimedia\Rdbms\SelectQueryBuilder\select
select( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:199
Wikimedia\Rdbms\SelectQueryBuilder\$db
IDatabase $db
Definition: SelectQueryBuilder.php:45
Wikimedia\Rdbms\SelectQueryBuilder\fetchField
fetchField()
Run the constructed SELECT query, and return a single field extracted from the first result row.
Definition: SelectQueryBuilder.php:640
Wikimedia\Rdbms\SelectQueryBuilder\getSQL
getSQL()
Get the SQL query string which would be used by fetchResultSet().
Definition: SelectQueryBuilder.php:758
Wikimedia\Rdbms\SelectQueryBuilder\groupBy
groupBy( $group)
Add a GROUP BY clause.
Definition: SelectQueryBuilder.php:400
Wikimedia\Rdbms\SelectQueryBuilder\$fields
array $fields
The fields to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:22
Wikimedia\Rdbms\SelectQueryBuilder\from
from( $table, $alias=null)
Add a single table to the SELECT query.
Definition: SelectQueryBuilder.php:153
Wikimedia\Rdbms\SelectQueryBuilder\estimateRowCount
estimateRowCount()
Estimate the number of rows in dataset.
Definition: SelectQueryBuilder.php:700
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