Jump to content

Manual:SelectQueryBuilder.php

From mediawiki.org

Class for building a SQL query and fetching its results, largely intended to be extended by more specialised classes. Extends JoinGroupBase .

Public methods

[edit]

None of the methods in this class are stable to override.

  • connection( IDatabase $db )
  • queryInfo()
  • rawTables()
  • newSubquery()

Query methods

[edit]
  • from() - single table to the SELECT query. Alias for JoinGroupBase::table().
  • tables() - Add multiple tables. It's recommended to use join() and leftJoin() instead in new code.
  • field() - add a single field to the query, optionally with an alias. The field is an SQL fragment. It is unsafe to pass user input to this function.
  • fields() - add a field or an array of fields to the query. Each field is an SQL fragment. If the array key is non-numeric, the key is taken to be an alias for the field.
    • select() - alias for fields()
  • where() - add conditions to the query. The supplied conditions will be appended to the existing conditions, separated by AND.
    • andWhere() - alias.
    • conds() - another alias.
  • JOIN:
    • join() - inherited from JoinGroupBase - join (INNER JOIN) a table or group of tables. To be called after from()/table().
    • leftjoin() - inherited from JoinGroupBase - left join a table or group of tables. To be called after from().
    • straightJoin() - inherited from JoinGroupBase - straight join a table or group of tables. To be called after from(). See also straightJoinOption() below.
    • joinConds() - It's recommended to use join() and leftJoin() instead in new code.
    • newJoinGroup() - create a parenthesized group of joins which can be added to the object like a table. The group is initially empty.
  • offset() - set the offset. Skip this many rows at the start of the result set. Offset with limit() can theoretically be used for paging through a result set, but this is discouraged for performance reasons.
  • limit()
  • options:
    • distinct()
    • lockInShareMode()
    • forUpdate()
  • setMaxExecutionTime()
  • clauses:
    • groupBy() - add a GROUP BY clause. May be either an SQL fragment string naming a field or expression to group by, or an array of such SQL fragments. If there is an existing GROUP BY clause, the new one will be appended.
    • having()
    • orderBy()
  • Index options
    • useIndex()
    • ignoreIndex()
  • explain() - make the query be an EXPLAIN SELECT query instead of a SELECT query.
  • caller() - set the method name to be included in an SQL comment.
Other options
  • option() - manually set an option in the $options array to be passed to IDatabase::select().
  • options() - manually set multiple options in the $options array to be passed to IDatabase::select().
  • straightJoinOption() - enable the STRAIGHT_JOIN query option.
  • bigResult() - enable SQL_BIG_RESULT.
  • bufferResult() - enable SQL_BUFFER_RESULT
  • smallResult() - enable SQL_SMALL_RESULT
  • calcFoundRows() - SQL_CALC_FOUND_ROWS

Run, fetch and count methods

[edit]
  • fetchResultSet() - run the constructed SELECT query and return all results. Returns instance of IResultWrapper .
  • fetchField() - run the constructed SELECT query, and return a single field extracted from the first result row. This may only be called when only one field has been added to the builder.
  • fetchFieldValues() - run the constructed SELECT query, and extract a single field from each result row, returning an array containing all the values. This may only be called when only one field has been added to the builder.
  • fetchRow() - run the constructed SELECT query, and return the first result row (stdClass). If there were no results, return false.
  • fetchRowCount()
  • estimateRowCount() - estimate the number of rows in dataset that would be returned by a SELECT query, using EXPLAIN SELECT. The estimate is provided using index cardinality statistics, and is notoriously inaccurate, especially when large numbers of rows have recently been added or deleted.
  • lockForUpdate() - run the SELECT query with the FOR UPDATE option. The field list is ignored.
  • buildGroupConcatField() - combine a field for several rows into a single string. NULL values will not appear in the output, duplicated values will appear.

Other

[edit]
  • getSQL() - get the SQL query string which would be used by fetchResultSet().
  • getQueryInfo() - get an associative array describing the query in terms of its raw parameters to Database::select(). This can be used to interface with legacy code.

Examples

[edit]
$dbProvider = MediaWikiServices::getInstance()->getConnectionProvider();
$dbr = $dbProvider->getReplicaDatabase();
$res = $dbr->newSelectQueryBuilder()
	->fields( [ 'cat_title', 'cat_pages' ] )
	->table( 'category' )
	->where( $dbr->expr( 'cat_pages', '>', 0 ) )
	->orderBy( 'cat_title', SelectQueryBuilder::SORT_ASC )
	->caller( __METHOD__ )
	->fetchResultSet();

See Manual:Database access for further examples and guidance.

Extended by

[edit]

See also

[edit]