MediaWiki  master
SelectQueryBuilder.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Wikimedia\Rdbms;
4 
21 
23  public const SORT_ASC = 'ASC';
24 
26  public const SORT_DESC = 'DESC';
27 
31  private $fields = [];
32 
36  private $conds = [];
37 
41  private $caller = __CLASS__;
42 
46  protected $options = [];
47 
51  private $nextAutoAlias = 1;
52 
54  protected $db;
55 
62  public function __construct( IDatabase $db ) {
63  $this->db = $db;
64  }
65 
73  public function connection( IDatabase $db ) {
74  if ( $this->db->getType() !== $db->getType() ) {
75  throw new \InvalidArgumentException( __METHOD__ .
76  ' cannot switch to a database of a different type.' );
77  }
78  $this->db = $db;
79  return $this;
80  }
81 
101  public function queryInfo( $info ) {
102  if ( isset( $info['tables'] ) ) {
103  $this->rawTables( $info['tables'] );
104  }
105  if ( isset( $info['fields'] ) ) {
106  $this->fields( $info['fields'] );
107  }
108  if ( isset( $info['conds'] ) ) {
109  $this->where( $info['conds'] );
110  }
111  if ( isset( $info['options'] ) ) {
112  $this->options( (array)$info['options'] );
113  }
114  if ( isset( $info['join_conds'] ) ) {
115  $this->joinConds( (array)$info['join_conds'] );
116  }
117  if ( isset( $info['joins'] ) ) {
118  $this->joinConds( (array)$info['joins'] );
119  }
120  return $this;
121  }
122 
135  public function rawTables( $tables ) {
136  if ( is_array( $tables ) ) {
137  $this->tables = array_merge( $this->tables, $tables );
138  } elseif ( is_string( $tables ) ) {
139  $this->tables[] = $tables;
140  } else {
141  throw new \InvalidArgumentException( __METHOD__ .
142  ': $tables must be a string or array' );
143  }
144  return $this;
145  }
146 
152  public function newSubquery() {
153  return new self( $this->db );
154  }
155 
163  public function from( $table, $alias = null ) {
164  return $this->table( $table, $alias );
165  }
166 
173  public function tables( $tables ) {
174  foreach ( $tables as $alias => $table ) {
175  if ( is_string( $alias ) ) {
176  $this->table( $table, $alias );
177  } else {
178  $this->table( $table );
179  }
180  }
181  return $this;
182  }
183 
194  public function fields( $fields ) {
195  if ( is_array( $fields ) ) {
196  $this->fields = array_merge( $this->fields, $fields );
197  } else {
198  $this->fields[] = $fields;
199  }
200  return $this;
201  }
202 
209  public function select( $fields ) {
210  return $this->fields( $fields );
211  }
212 
221  public function field( $field, $alias = null ) {
222  if ( $alias === null ) {
223  $this->fields[] = $field;
224  } else {
225  $this->fields[$alias] = $field;
226  }
227  return $this;
228  }
229 
266  public function where( $conds ) {
267  if ( is_array( $conds ) ) {
268  foreach ( $conds as $key => $cond ) {
269  if ( is_int( $key ) ) {
270  $this->conds[] = $cond;
271  } elseif ( isset( $this->conds[$key] ) ) {
272  // @phan-suppress-previous-line PhanTypeMismatchDimFetch
273  // T288882
274  $this->conds[] = $this->db->makeList(
275  [ $key => $cond ], IDatabase::LIST_AND );
276  } else {
277  $this->conds[$key] = $cond;
278  }
279  }
280  } else {
281  $this->conds[] = $conds;
282  }
283  return $this;
284  }
285 
292  public function andWhere( $conds ) {
293  return $this->where( $conds );
294  }
295 
302  public function conds( $conds ) {
303  return $this->where( $conds );
304  }
305 
314  public function joinConds( array $joinConds ) {
315  $this->joinConds = array_merge( $this->joinConds, $joinConds );
316  return $this;
317  }
318 
324  protected function getAutoAlias() {
325  return 'sqb' . ( $this->nextAutoAlias++ );
326  }
327 
334  public function newJoinGroup() {
335  return new JoinGroup( $this->getAutoAlias() );
336  }
337 
348  public function offset( $offset ) {
349  $this->options['OFFSET'] = $offset;
350  return $this;
351  }
352 
363  public function limit( $limit ) {
364  $this->options['LIMIT'] = $limit;
365  return $this;
366  }
367 
375  public function lockInShareMode() {
376  $this->options[] = 'LOCK IN SHARE MODE';
377  return $this;
378  }
379 
387  public function forUpdate() {
388  $this->options[] = 'FOR UPDATE';
389  return $this;
390  }
391 
397  public function distinct() {
398  $this->options[] = 'DISTINCT';
399  return $this;
400  }
401 
408  public function setMaxExecutionTime( int $time ) {
409  $this->options['MAX_EXECUTION_TIME'] = $time;
410  return $this;
411  }
412 
422  public function groupBy( $group ) {
423  $this->mergeOption( 'GROUP BY', $group );
424  return $this;
425  }
426 
438  public function having( $having ) {
439  $this->mergeOption( 'HAVING', $having );
440  return $this;
441  }
442 
453  public function orderBy( $fields, $direction = null ) {
454  if ( $direction === null ) {
455  $this->mergeOption( 'ORDER BY', $fields );
456  } elseif ( is_array( $fields ) ) {
457  $fieldsWithDirection = [];
458  foreach ( $fields as $field ) {
459  $fieldsWithDirection[] = "$field $direction";
460  }
461  $this->mergeOption( 'ORDER BY', $fieldsWithDirection );
462  } else {
463  $this->mergeOption( 'ORDER BY', "$fields $direction" );
464  }
465  return $this;
466  }
467 
474  private function mergeOption( $name, $newArrayOrValue ) {
475  $value = isset( $this->options[$name] )
476  ? (array)$this->options[$name] : [];
477  if ( is_array( $newArrayOrValue ) ) {
478  $value = array_merge( $value, $newArrayOrValue );
479  } else {
480  $value[] = $newArrayOrValue;
481  }
482  $this->options[$name] = $value;
483  }
484 
497  public function useIndex( $index ) {
498  $this->setIndexHint( 'USE INDEX', $index );
499  return $this;
500  }
501 
514  public function ignoreIndex( $index ) {
515  $this->setIndexHint( 'IGNORE INDEX', $index );
516  return $this;
517  }
518 
525  private function setIndexHint( $type, $value ) {
526  if ( !isset( $this->options[$type] ) ) {
527  $this->options[$type] = [];
528  } elseif ( !is_array( $this->options[$type] ) ) {
529  throw new \UnexpectedValueException(
530  __METHOD__ . ": The $type option cannot be appended to " .
531  'because it is not an array. This may have been caused by a prior ' .
532  'call to option() or options().' );
533  }
534  if ( is_array( $value ) ) {
535  $this->options[$type] = array_merge( $this->options[$type], $value );
536  } elseif ( $this->lastAlias === null ) {
537  throw new \UnexpectedValueException(
538  __METHOD__ . ': Cannot append index value since there is no' .
539  'prior table' );
540  } else {
541  $this->options[$type][$this->lastAlias] = $value;
542  }
543  }
544 
550  public function explain() {
551  $this->options['EXPLAIN'] = true;
552  return $this;
553  }
554 
560  public function straightJoinOption() {
561  $this->options[] = 'STRAIGHT_JOIN';
562  return $this;
563  }
564 
570  public function bigResult() {
571  $this->options[] = 'SQL_BIG_RESULT';
572  return $this;
573  }
574 
580  public function bufferResult() {
581  $this->options[] = 'SQL_BUFFER_RESULT';
582  return $this;
583  }
584 
590  public function smallResult() {
591  $this->options[] = 'SQL_SMALL_RESULT';
592  return $this;
593  }
594 
600  public function calcFoundRows() {
601  $this->options[] = 'SQL_CALC_FOUND_ROWS';
602  return $this;
603  }
604 
613  public function option( $name, $value = null ) {
614  if ( $value === null ) {
615  $this->options[] = $name;
616  } else {
617  $this->options[$name] = $value;
618  }
619  return $this;
620  }
621 
629  public function options( array $options ) {
630  $this->options = array_merge( $this->options, $options );
631  return $this;
632  }
633 
640  public function caller( $fname ) {
641  $this->caller = $fname;
642  return $this;
643  }
644 
650  public function fetchResultSet() {
651  return $this->db->select( $this->tables, $this->fields, $this->conds, $this->caller,
652  $this->options, $this->joinConds );
653  }
654 
662  public function fetchField() {
663  if ( count( $this->fields ) !== 1 ) {
664  throw new \UnexpectedValueException(
665  __METHOD__ . ' expects the query to have only one field' );
666  }
667  $field = reset( $this->fields );
668  return $this->db->selectField( $this->tables, $field, $this->conds, $this->caller,
669  $this->options, $this->joinConds );
670  }
671 
679  public function fetchFieldValues() {
680  if ( count( $this->fields ) !== 1 ) {
681  throw new \UnexpectedValueException(
682  __METHOD__ . ' expects the query to have only one field' );
683  }
684  $field = reset( $this->fields );
685  return $this->db->selectFieldValues( $this->tables, $field, $this->conds, $this->caller,
686  $this->options, $this->joinConds );
687  }
688 
695  public function fetchRow() {
696  return $this->db->selectRow( $this->tables, $this->fields, $this->conds, $this->caller,
697  $this->options, $this->joinConds );
698  }
699 
707  public function fetchRowCount() {
708  return $this->db->selectRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
709  $this->caller, $this->options, $this->joinConds );
710  }
711 
722  public function estimateRowCount() {
723  return $this->db->estimateRowCount( $this->tables, $this->getRowCountVar(), $this->conds,
724  $this->caller, $this->options, $this->joinConds );
725  }
726 
733  private function getRowCountVar() {
734  if ( count( $this->fields ) === 0 ) {
735  return '*';
736  } elseif ( count( $this->fields ) === 1 ) {
737  return reset( $this->fields );
738  } else {
739  throw new \UnexpectedValueException(
740  __METHOD__ . ' expects the query to have at most one field' );
741  }
742  }
743 
749  public function lockForUpdate() {
750  return $this->db->lockForUpdate( $this->tables, $this->conds, $this->caller,
751  $this->options, $this->joinConds );
752  }
753 
765  public function buildGroupConcatField( $delim ) {
766  if ( count( $this->fields ) !== 1 ) {
767  throw new \UnexpectedValueException(
768  __METHOD__ . ' expects the query to have only one field' );
769  }
770  $field = reset( $this->fields );
771  return $this->db->buildGroupConcatField( $delim, $this->tables, $field,
772  $this->conds, $this->joinConds );
773  }
774 
780  public function getSQL() {
781  return $this->db->selectSQLText( $this->tables, $this->fields, $this->conds, $this->caller,
782  $this->options, $this->joinConds );
783  }
784 
799  public function getQueryInfo( $joinsName = 'join_conds' ) {
800  $info = [
801  'tables' => $this->tables,
802  'fields' => $this->fields,
803  'conds' => $this->conds,
804  'options' => $this->options,
805  ];
806  $info[ $joinsName ] = $this->joinConds;
807  return $info;
808  }
809 }
const LIST_AND
Definition: Defines.php:43
A class for code shared between SelectQueryBuilder and JoinGroup.
table( $table, $alias=null)
Add a single table or a single parenthesized group.
An object representing a parenthesized group of tables and their join types and conditions.
Definition: JoinGroup.php:9
A query builder for SELECT queries with a fluent interface.
estimateRowCount()
Estimate the number of rows in dataset.
getQueryInfo( $joinsName='join_conds')
Get an associative array describing the query in terms of its raw parameters to Database::select().
distinct()
Enable the DISTINCT option.
straightJoinOption()
Enable the STRAIGHT_JOIN query option.
groupBy( $group)
Add a GROUP BY clause.
newSubquery()
Get an empty SelectQueryBuilder which can be used to build a subquery of this query.
lockForUpdate()
Run the SELECT query with the FOR UPDATE option.
useIndex( $index)
Set a USE INDEX option.
connection(IDatabase $db)
Change the IDatabase object the query builder is bound to.
rawTables( $tables)
Given a table or table array as might be passed to Database::select(), append it to the existing tabl...
conds( $conds)
Add conditions to the query.
limit( $limit)
Set the query limit.
andWhere( $conds)
Add conditions to the query.
fetchField()
Run the constructed SELECT query, and return a single field extracted from the first result row.
array $options
The options to be passed to IDatabase::select()
fetchFieldValues()
Run the constructed SELECT query, and extract a single field from each result row,...
calcFoundRows()
Enable the SQL_CALC_FOUND_ROWS option.
newJoinGroup()
Create a parenthesized group of joins which can be added to the object like a table.
fetchResultSet()
Run the constructed SELECT query and return all results.
lockInShareMode()
Enable the LOCK IN SHARE MODE option.
bufferResult()
Enable the SQL_BUFFER_RESULT option.
queryInfo( $info)
Set the query parameters to the given values, appending to the values which were already set.
options(array $options)
Manually set multiple options in the $options array to be passed to IDatabase::select().
forUpdate()
Enable the FOR UPDATE option.
option( $name, $value=null)
Manually set an option in the $options array to be passed to IDatabase::select()
buildGroupConcatField( $delim)
Build a GROUP_CONCAT or equivalent statement for a query.
select( $fields)
Add a field or an array of fields to the query.
caller( $fname)
Set the method name to be included in an SQL comment.
tables( $tables)
Add multiple tables.
ignoreIndex( $index)
Set the IGNORE INDEX option.
having( $having)
Add a HAVING clause.
from( $table, $alias=null)
Add a single table to the SELECT query.
setMaxExecutionTime(int $time)
Set MAX_EXECUTION_TIME for queries.
field( $field, $alias=null)
Add a single field to the query, optionally with an alias.
orderBy( $fields, $direction=null)
Set the ORDER BY clause.
smallResult()
Enable the SQL_SMALL_RESULT option.
__construct(IDatabase $db)
Only for use in subclasses.
explain()
Make the query be an EXPLAIN SELECT query instead of a SELECT query.
bigResult()
Enable the SQL_BIG_RESULT option.
fetchRowCount()
Run the SELECT query, and return the number of results.
fetchRow()
Run the constructed SELECT query, and return the first result row.
joinConds(array $joinConds)
Manually append to the $join_conds array which will be passed to IDatabase::select().
fields( $fields)
Add a field or an array of fields to the query.
getAutoAlias()
Get a table alias which is unique to this SelectQueryBuilder.
where( $conds)
Add conditions to the query.
getSQL()
Get the SQL query string which would be used by fetchResultSet().
Basic database interface for live and lazy-loaded relation database handles.
Definition: IDatabase.php:40
getType()
Get the RDBMS type of the server (e.g.