MediaWiki  master
SelectQueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Wikimedia\Rdbms;
4 
9  private $fields = [];
10 
14  private $conds = [];
15 
19  private $caller = __CLASS__;
20 
24  private $options = [];
25 
29  private $nextAutoAlias = 1;
30 
32  private $db;
33 
39  public function __construct( IDatabase $db ) {
40  $this->db = $db;
41  }
42 
50  public function connection( IDatabase $db ) {
51  if ( $this->db->getType() !== $db->getType() ) {
52  throw new \InvalidArgumentException( __METHOD__ .
53  ' cannot switch to a database of a different type.' );
54  }
55  $this->db = $db;
56  return $this;
57  }
58 
78  public function queryInfo( $info ) {
79  if ( isset( $info['tables'] ) ) {
80  $this->rawTables( $info['tables'] );
81  }
82  if ( isset( $info['fields'] ) ) {
83  $this->fields( $info['fields'] );
84  }
85  if ( isset( $info['conds'] ) ) {
86  $this->where( $info['conds'] );
87  }
88  if ( isset( $info['options'] ) ) {
89  $this->options( (array)$info['options'] );
90  }
91  if ( isset( $info['join_conds'] ) ) {
92  $this->joinConds( (array)$info['join_conds'] );
93  }
94  if ( isset( $info['joins'] ) ) {
95  $this->joinConds( (array)$info['joins'] );
96  }
97  return $this;
98  }
99 
112  public function rawTables( $tables ) {
113  if ( is_array( $tables ) ) {
114  $this->tables = array_merge( $this->tables, $tables );
115  } elseif ( is_string( $tables ) ) {
116  $this->tables[] = $tables;
117  } else {
118  throw new \InvalidArgumentException( __METHOD__ .
119  ': $tables must be a string or array' );
120  }
121  return $this;
122  }
123 
129  public function newSubquery() {
130  return new self( $this->db );
131  }
132 
140  public function from( $table, $alias = null ) {
141  return $this->table( $table, $alias );
142  }
143 
150  public function tables( $tables ) {
151  foreach ( $tables as $alias => $table ) {
152  if ( is_string( $alias ) ) {
153  $this->table( $table, $alias );
154  } else {
155  $this->table( $table );
156  }
157  }
158  return $this;
159  }
160 
171  public function fields( $fields ) {
172  if ( is_array( $fields ) ) {
173  $this->fields = array_merge( $this->fields, $fields );
174  } else {
175  $this->fields[] = $fields;
176  }
177  return $this;
178  }
179 
186  public function select( $fields ) {
187  return $this->fields( $fields );
188  }
189 
198  public function field( $field, $alias = null ) {
199  if ( $alias === null ) {
200  $this->fields[] = $field;
201  } else {
202  $this->fields[$alias] = $field;
203  }
204  return $this;
205  }
206 
243  public function where( $conds ) {
244  if ( is_array( $conds ) ) {
245  $this->conds = array_merge( $this->conds, $conds );
246  } else {
247  $this->conds[] = $conds;
248  }
249  return $this;
250  }
251 
258  public function andWhere( $conds ) {
259  return $this->where( $conds );
260  }
261 
268  public function conds( $conds ) {
269  return $this->where( $conds );
270  }
271 
280  public function joinConds( array $joinConds ) {
281  $this->joinConds = array_merge( $this->joinConds, $joinConds );
282  return $this;
283  }
284 
290  protected function getAutoAlias() {
291  return 'sqb' . ( $this->nextAutoAlias++ );
292  }
293 
300  public function newJoinGroup() {
301  return new JoinGroup( $this->getAutoAlias() );
302  }
303 
314  public function offset( $offset ) {
315  $this->options['OFFSET'] = $offset;
316  return $this;
317  }
318 
329  public function limit( $limit ) {
330  $this->options['LIMIT'] = $limit;
331  return $this;
332  }
333 
341  public function lockInShareMode() {
342  $this->options[] = 'LOCK IN SHARE MODE';
343  return $this;
344  }
345 
353  public function forUpdate() {
354  $this->options[] = 'FOR UPDATE';
355  return $this;
356  }
357 
363  public function distinct() {
364  $this->options[] = 'DISTINCT';
365  return $this;
366  }
367 
377  public function groupBy( $group ) {
378  $this->mergeOption( 'GROUP BY', $group );
379  return $this;
380  }
381 
393  public function having( $having ) {
394  $this->mergeOption( 'HAVING', $having );
395  return $this;
396  }
397 
407  public function orderBy( $fields, $direction = null ) {
408  if ( $direction === null ) {
409  $this->mergeOption( 'ORDER BY', $fields );
410  } elseif ( is_array( $fields ) ) {
411  $fieldsWithDirection = [];
412  foreach ( $fields as $field ) {
413  $fieldsWithDirection[] = "$field $direction";
414  }
415  $this->mergeOption( 'ORDER BY', $fieldsWithDirection );
416  } else {
417  $this->mergeOption( 'ORDER BY', "$fields $direction" );
418  }
419  return $this;
420  }
421 
428  private function mergeOption( $name, $newArrayOrValue ) {
429  $value = isset( $this->options[$name] )
430  ? (array)$this->options[$name] : [];
431  if ( is_array( $newArrayOrValue ) ) {
432  $value = array_merge( $value, $newArrayOrValue );
433  } else {
434  $value[] = $newArrayOrValue;
435  }
436  $this->options[$name] = $value;
437  }
438 
451  public function useIndex( $index ) {
452  $this->setIndexHint( 'USE INDEX', $index );
453  return $this;
454  }
455 
468  public function ignoreIndex( $index ) {
469  $this->setIndexHint( 'IGNORE INDEX', $index );
470  return $this;
471  }
472 
479  private function setIndexHint( $type, $value ) {
480  if ( !isset( $this->options[$type] ) ) {
481  $this->options[$type] = [];
482  } elseif ( !is_array( $this->options[$type] ) ) {
483  throw new \UnexpectedValueException(
484  __METHOD__ . ": The $type option cannot be appended to " .
485  'because it is not an array. This may have been caused by a prior ' .
486  'call to option() or options().' );
487  }
488  if ( is_array( $value ) ) {
489  $this->options[$type] = array_merge( $this->options[$type], $value );
490  } elseif ( $this->lastAlias === null ) {
491  throw new \UnexpectedValueException(
492  __METHOD__ . ': Cannot append index value since there is no' .
493  'prior table' );
494  } else {
495  $this->options[$type][$this->lastAlias] = $value;
496  }
497  }
498 
504  public function explain() {
505  $this->options['EXPLAIN'] = true;
506  return $this;
507  }
508 
514  public function straightJoin() {
515  $this->options[] = 'STRAIGHT_JOIN';
516  return $this;
517  }
518 
524  public function bigResult() {
525  $this->options[] = 'SQL_BIG_RESULT';
526  return $this;
527  }
528 
534  public function bufferResult() {
535  $this->options[] = 'SQL_BUFFER_RESULT';
536  return $this;
537  }
538 
544  public function smallResult() {
545  $this->options[] = 'SQL_SMALL_RESULT';
546  return $this;
547  }
548 
554  public function calcFoundRows() {
555  $this->options[] = 'SQL_CALC_FOUND_ROWS';
556  return $this;
557  }
558 
567  public function option( $name, $value = null ) {
568  if ( $value === null ) {
569  $this->options[] = $name;
570  } else {
571  $this->options[$name] = $value;
572  }
573  return $this;
574  }
575 
583  public function options( array $options ) {
584  $this->options = array_merge( $this->options, $options );
585  return $this;
586  }
587 
594  public function caller( $fname ) {
595  $this->caller = $fname;
596  return $this;
597  }
598 
604  public function fetchResultSet() {
605  return $this->db->select( $this->tables, $this->fields, $this->conds, $this->caller,
606  $this->options, $this->joinConds );
607  }
608 
616  public function fetchField() {
617  if ( count( $this->fields ) !== 1 ) {
618  throw new \UnexpectedValueException(
619  __METHOD__ . ' expects the query to have only one field' );
620  }
621  $field = reset( $this->fields );
622  return $this->db->selectField( $this->tables, $field, $this->conds, $this->caller,
623  $this->options, $this->joinConds );
624  }
625 
633  public function fetchFieldValues() {
634  if ( count( $this->fields ) !== 1 ) {
635  throw new \UnexpectedValueException(
636  __METHOD__ . ' expects the query to have only one field' );
637  }
638  $field = reset( $this->fields );
639  return $this->db->selectFieldValues( $this->tables, $field, $this->conds, $this->caller,
640  $this->options, $this->joinConds );
641  }
642 
649  public function fetchRow() {
650  return $this->db->selectRow( $this->tables, $this->fields, $this->conds, $this->caller,
651  $this->options, $this->joinConds );
652  }
653 
661  public function fetchRowCount() {
662  return $this->db->selectRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
663  $this->caller, $this->options, $this->joinConds );
664  }
665 
676  public function estimateRowCount() {
677  return $this->db->estimateRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
678  $this->caller, $this->options, $this->joinConds );
679  }
680 
687  private function getRowCountVar() {
688  if ( count( $this->fields ) === 0 ) {
689  return '*';
690  } elseif ( count( $this->fields ) === 1 ) {
691  return reset( $this->fields );
692  } else {
693  throw new \UnexpectedValueException(
694  __METHOD__ . ' expects the query to have at most one field' );
695  }
696  }
697 
703  public function lockForUpdate() {
704  return $this->db->lockForUpdate( $this->tables, $this->conds, $this->caller,
705  $this->options, $this->joinConds );
706  }
707 
719  public function buildGroupConcatField( $delim ) {
720  if ( count( $this->fields ) !== 1 ) {
721  throw new \UnexpectedValueException(
722  __METHOD__ . ' expects the query to have only one field' );
723  }
724  $field = reset( $this->fields );
725  return $this->db->buildGroupConcatField( $delim, $this->tables, $field,
726  $this->conds, $this->joinConds );
727  }
728 
734  public function getSQL() {
735  return $this->db->selectSQLText( $this->tables, $this->fields, $this->conds, $this->caller,
736  $this->options, $this->joinConds );
737  }
738 
753  public function getQueryInfo( $joinsName = 'join_conds' ) {
754  $info = [
755  'tables' => $this->tables,
756  'fields' => $this->fields,
757  'conds' => $this->conds,
758  'options' => $this->options,
759  ];
760  $info[ $joinsName ] = $this->joinConds;
761  return $info;
762  }
763 }
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:129
Wikimedia\Rdbms\SelectQueryBuilder\calcFoundRows
calcFoundRows()
Enable the SQL_CALC_FOUND_ROWS option.
Definition: SelectQueryBuilder.php:554
Wikimedia\Rdbms\SelectQueryBuilder\joinConds
joinConds(array $joinConds)
Manually append to the $join_conds array which will be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:280
Wikimedia\Rdbms\SelectQueryBuilder\$conds
array $conds
The conditions to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:14
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:428
Wikimedia\Rdbms\SelectQueryBuilder\getRowCountVar
getRowCountVar()
Private helper which extracts a field suitable for row counting from the fields array.
Definition: SelectQueryBuilder.php:687
Wikimedia\Rdbms\SelectQueryBuilder\limit
limit( $limit)
Set the query limit.
Definition: SelectQueryBuilder.php:329
Wikimedia\Rdbms\SelectQueryBuilder\options
options(array $options)
Manually set multiple options in the $options array to be passed to IDatabase::select().
Definition: SelectQueryBuilder.php:583
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:112
Wikimedia\Rdbms\SelectQueryBuilder\useIndex
useIndex( $index)
Set a USE INDEX option.
Definition: SelectQueryBuilder.php:451
Wikimedia\Rdbms\SelectQueryBuilder\connection
connection(IDatabase $db)
Change the IDatabase object the query builder is bound to.
Definition: SelectQueryBuilder.php:50
Wikimedia\Rdbms\SelectQueryBuilder\fetchResultSet
fetchResultSet()
Run the constructed SELECT query and return all results.
Definition: SelectQueryBuilder.php:604
Wikimedia\Rdbms\SelectQueryBuilder\tables
tables( $tables)
Add multiple tables.
Definition: SelectQueryBuilder.php:150
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:290
Wikimedia\Rdbms\SelectQueryBuilder\$caller
string $caller
The caller (function name) to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:19
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:534
Wikimedia\Rdbms\SelectQueryBuilder\distinct
distinct()
Enable the DISTINCT option.
Definition: SelectQueryBuilder.php:363
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:39
Wikimedia\Rdbms\SelectQueryBuilder\newJoinGroup
newJoinGroup()
Create a parenthesized group of joins which can be added to the object like a table.
Definition: SelectQueryBuilder.php:300
Wikimedia\Rdbms\SelectQueryBuilder\caller
caller( $fname)
Set the method name to be included in an SQL comment.
Definition: SelectQueryBuilder.php:594
Wikimedia\Rdbms\SelectQueryBuilder
Definition: SelectQueryBuilder.php:5
Wikimedia\Rdbms\SelectQueryBuilder\forUpdate
forUpdate()
Enable the FOR UPDATE option.
Definition: SelectQueryBuilder.php:353
Wikimedia\Rdbms\JoinGroupBase\$lastAlias
$lastAlias
Definition: JoinGroupBase.php:16
Wikimedia\Rdbms\SelectQueryBuilder\straightJoin
straightJoin()
Enable the STRAIGHT_JOIN option.
Definition: SelectQueryBuilder.php:514
Wikimedia\Rdbms\SelectQueryBuilder\fetchRowCount
fetchRowCount()
Run the SELECT query, and return the number of results.
Definition: SelectQueryBuilder.php:661
Wikimedia\Rdbms\SelectQueryBuilder\fields
fields( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:171
Wikimedia\Rdbms\SelectQueryBuilder\lockInShareMode
lockInShareMode()
Enable the LOCK IN SHARE MODE option.
Definition: SelectQueryBuilder.php:341
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:567
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:753
Wikimedia\Rdbms\SelectQueryBuilder\offset
offset( $offset)
Set the offset.
Definition: SelectQueryBuilder.php:314
Wikimedia\Rdbms\SelectQueryBuilder\$options
array $options
The options to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:24
Wikimedia\Rdbms\SelectQueryBuilder\fetchRow
fetchRow()
Run the constructed SELECT query, and return the first result row.
Definition: SelectQueryBuilder.php:649
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:78
Wikimedia\Rdbms\SelectQueryBuilder\fetchFieldValues
fetchFieldValues()
Run the constructed SELECT query, and extract a single field from each result row,...
Definition: SelectQueryBuilder.php:633
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:29
Wikimedia\Rdbms\SelectQueryBuilder\lockForUpdate
lockForUpdate()
Run the SELECT query with the FOR UPDATE option.
Definition: SelectQueryBuilder.php:703
Wikimedia\Rdbms\SelectQueryBuilder\conds
conds( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:268
Wikimedia\Rdbms\SelectQueryBuilder\andWhere
andWhere( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:258
Wikimedia\Rdbms\SelectQueryBuilder\bigResult
bigResult()
Enable the SQL_BIG_RESULT option.
Definition: SelectQueryBuilder.php:524
Wikimedia\Rdbms\SelectQueryBuilder\buildGroupConcatField
buildGroupConcatField( $delim)
Build a GROUP_CONCAT or equivalent statement for a query.
Definition: SelectQueryBuilder.php:719
Wikimedia\Rdbms\SelectQueryBuilder\explain
explain()
Make the query be an EXPLAIN SELECT query instead of a SELECT query.
Definition: SelectQueryBuilder.php:504
Wikimedia\Rdbms\SelectQueryBuilder\having
having( $having)
Add a HAVING clause.
Definition: SelectQueryBuilder.php:393
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:468
Wikimedia\Rdbms\SelectQueryBuilder\smallResult
smallResult()
Enable the SQL_SMALL_RESULT option.
Definition: SelectQueryBuilder.php:544
Wikimedia\Rdbms\SelectQueryBuilder\setIndexHint
setIndexHint( $type, $value)
Private helper for methods that set index hints.
Definition: SelectQueryBuilder.php:479
Wikimedia\Rdbms\SelectQueryBuilder\where
where( $conds)
Add conditions to the query.
Definition: SelectQueryBuilder.php:243
Wikimedia\Rdbms\SelectQueryBuilder\field
field( $field, $alias=null)
Add a single field to the query, optionally with an alias.
Definition: SelectQueryBuilder.php:198
Wikimedia\Rdbms\SelectQueryBuilder\orderBy
orderBy( $fields, $direction=null)
Set the ORDER BY clause.
Definition: SelectQueryBuilder.php:407
Wikimedia\Rdbms\SelectQueryBuilder\select
select( $fields)
Add a field or an array of fields to the query.
Definition: SelectQueryBuilder.php:186
Wikimedia\Rdbms\SelectQueryBuilder\$db
IDatabase $db
Definition: SelectQueryBuilder.php:32
Wikimedia\Rdbms\SelectQueryBuilder\fetchField
fetchField()
Run the constructed SELECT query, and return a single field extracted from the first result row.
Definition: SelectQueryBuilder.php:616
Wikimedia\Rdbms\SelectQueryBuilder\getSQL
getSQL()
Get the SQL query string which would be used by fetchResultSet().
Definition: SelectQueryBuilder.php:734
Wikimedia\Rdbms\SelectQueryBuilder\groupBy
groupBy( $group)
Add a GROUP BY clause.
Definition: SelectQueryBuilder.php:377
Wikimedia\Rdbms\SelectQueryBuilder\$fields
array $fields
The fields to be passed to IDatabase::select()
Definition: SelectQueryBuilder.php:9
Wikimedia\Rdbms\SelectQueryBuilder\from
from( $table, $alias=null)
Add a single table to the SELECT query.
Definition: SelectQueryBuilder.php:140
Wikimedia\Rdbms\SelectQueryBuilder\estimateRowCount
estimateRowCount()
Estimate the number of rows in dataset.
Definition: SelectQueryBuilder.php:676
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