PHP Class yii\sphinx\QueryBuilder

QueryBuilder can also be used to build SQL statements such as INSERT, REPLACE, UPDATE, DELETE, from a Query object.
Since: 2.0
Author: Paul Klimov ([email protected])
Inheritance: extends yii\base\Object
Afficher le fichier Open project: yiisoft/yii2-sphinx

Méthodes publiques

Свойство Type Description
$db the Sphinx connection.
$querySeparator separator between different SQL queries. This is mainly used by QueryBuilder::build when generating a SQL statement.
$separator the separator between different fragments of a SQL statement. Defaults to an empty space. This is mainly used by QueryBuilder::build when generating a SQL statement.

Protected Properties

Свойство Type Description
$conditionBuilders map of query condition to builder methods. These methods are used by [[buildCondition]] to build SQL conditions from array syntax.

Méthodes publiques

Méthode Description
__construct ( Connection $connection, array $config = [] ) Constructor.
batchInsert ( string $index, array $columns, array $rows, array &$params ) : string Generates a batch INSERT SQL statement.
batchReplace ( string $index, array $columns, array $rows, array &$params ) : string Generates a batch REPLACE SQL statement.
build ( Query $query, array $params = [] ) : array Generates a SELECT SQL statement from a Query object.
buildAndCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Connects two or more SQL expressions with the AND or OR operator.
buildBetweenCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Creates an SQL expressions with the BETWEEN operator.
buildColumns ( string | array $columns ) : string Processes columns and properly quote them if necessary.
buildCondition ( IndexSchema[] $indexes, string | array $condition, array &$params ) : string Parses the condition specification and generates the corresponding SQL expression.
buildFrom ( array $indexes, array &$params ) : string
buildGroupBy ( array $columns, integer $limit ) : string
buildHashCondition ( IndexSchema[] $indexes, array $condition, array &$params ) : string Creates a condition based on column-value pairs.
buildHaving ( string[] $indexes, string | array $condition, array &$params ) : string
buildInCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Creates an SQL expressions with the IN operator.
buildLikeCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Creates an SQL expressions with the LIKE operator.
buildLimit ( integer $limit, integer $offset ) : string
buildMatch ( string | yii\db\Expression | MatchExpression $match, array &$params ) : string
buildNotCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Inverts an SQL expressions with NOT operator.
buildOption ( array $options, array &$params ) : string
buildOrderBy ( array $columns ) : string
buildOrderByAndLimit ( string $sql, array $orderBy, integer $limit, integer $offset ) : string Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
buildSelect ( array $columns, array &$params, boolean $distinct = false, string $selectOption = null ) : string
buildSimpleCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string Creates an SQL expressions like "column" operator value.
buildWhere ( string[] $indexes, string | array $condition, array &$params, string | yii\db\Expression | null $match = null ) : string
buildWithin ( array $columns ) : string
callKeywords ( string $index, string $text, boolean $fetchStatistic, array &$params ) : string Builds a SQL statement for returning tokenized and normalized forms of the keywords, and, optionally, keyword statistics.
callSnippets ( string $index, string | array $source, string $match, array $options, array &$params ) : string Builds a SQL statement for call snippet from provided data and query, using specified index settings.
delete ( string $index, array | string $condition, array &$params ) : string Creates a DELETE SQL statement.
getMatchBuilder ( ) : MatchBuilder
insert ( string $index, array $columns, array &$params ) : string Creates an INSERT SQL statement.
replace ( string $index, array $columns, array &$params ) : string Creates an REPLACE SQL statement.
truncateIndex ( string $index ) : string Builds a SQL statement for truncating an index.
update ( string $index, array $columns, array | string $condition, array &$params, array $options ) : string Creates an UPDATE SQL statement.

Méthodes protégées

Méthode Description
buildCompositeInCondition ( IndexSchema[] $indexes, string $operator, array $columns, array $values, array &$params ) : string
buildFacets ( array $facets, array &$params ) : string
buildShowMeta ( boolean | string | yii\db\Expression $showMeta, array &$params ) : string Builds SHOW META query.
composeColumnValue ( IndexSchema[] $indexes, string $columnName, mixed $value, array &$params ) : string Composes column value for SQL, taking in account the column type.
generateBatchInsertReplace ( string $statement, string $index, array $columns, array $rows, array &$params ) : string Generates a batch INSERT/REPLACE SQL statement.
generateInsertReplace ( string $statement, string $index, array $columns, array &$params ) : string Generates INSERT/REPLACE SQL statement.

Private Methods

Méthode Description
buildSelectFields ( array $columns, array &$params ) : string
getIndexSchemas ( array $indexes ) : IndexSchema[]

Method Details

__construct() public méthode

Constructor.
public __construct ( Connection $connection, array $config = [] )
$connection Connection the Sphinx connection.
$config array name-value pairs that will be used to initialize the object properties

batchInsert() public méthode

For example, php $sql = $queryBuilder->batchInsert('idx_user', ['id', 'name', 'age'], [ [1, 'Tom', 30], [2, 'Jane', 20], [3, 'Linda', 25], ], $params); Note that the values in each row must match the corresponding column names.
public batchInsert ( string $index, array $columns, array $rows, array &$params ) : string
$index string the index that new rows will be inserted into.
$columns array the column names
$rows array the rows to be batch inserted into the index
$params array the binding parameters that will be generated by this method. They should be bound to the Sphinx command later.
Résultat string the batch INSERT SQL statement

batchReplace() public méthode

For example, php $sql = $queryBuilder->batchReplace('idx_user', ['id', 'name', 'age'], [ [1, 'Tom', 30], [2, 'Jane', 20], [3, 'Linda', 25], ], $params); Note that the values in each row must match the corresponding column names.
public batchReplace ( string $index, array $columns, array $rows, array &$params ) : string
$index string the index that new rows will be replaced.
$columns array the column names
$rows array the rows to be batch replaced in the index
$params array the binding parameters that will be generated by this method. They should be bound to the Sphinx command later.
Résultat string the batch INSERT SQL statement

build() public méthode

Generates a SELECT SQL statement from a Query object.
public build ( Query $query, array $params = [] ) : array
$query Query the [[Query]] object from which the SQL statement will be generated
$params array the parameters to be bound to the generated SQL statement. These parameters will be included in the result with the additional parameters generated during the query building process.
Résultat array the generated SQL statement (the first array element) and the corresponding parameters to be bound to the SQL statement (the second array element). The parameters returned include those provided in `$params`.

buildAndCondition() public méthode

Connects two or more SQL expressions with the AND or OR operator.
public buildAndCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use for connecting the given operands
$operands array the SQL expressions to connect.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildBetweenCondition() public méthode

Creates an SQL expressions with the BETWEEN operator.
public buildBetweenCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use (e.g. `BETWEEN` or `NOT BETWEEN`)
$operands array the first operand is the column name. The second and third operands describe the interval that column value should be in.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildColumns() public méthode

It will join all columns into a string with comma as separators.
public buildColumns ( string | array $columns ) : string
$columns string | array the columns to be processed
Résultat string the processing result

buildCompositeInCondition() protected méthode

protected buildCompositeInCondition ( IndexSchema[] $indexes, string $operator, array $columns, array $values, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use (e.g. `IN` or `NOT IN`)
$columns array
$values array
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildCondition() public méthode

Parses the condition specification and generates the corresponding SQL expression.
public buildCondition ( IndexSchema[] $indexes, string | array $condition, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$condition string | array the condition specification. Please refer to [[Query::where()]] on how to specify a condition.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildFacets() protected méthode

protected buildFacets ( array $facets, array &$params ) : string
$facets array facet specifications
$params array the binding parameters to be populated
Résultat string the FACET clause build from [[query]]

buildFrom() public méthode

public buildFrom ( array $indexes, array &$params ) : string
$indexes array
$params array the binding parameters to be populated
Résultat string the FROM clause built from [[query]].

buildGroupBy() public méthode

public buildGroupBy ( array $columns, integer $limit ) : string
$columns array group columns
$limit integer group limit
Résultat string the GROUP BY clause

buildHashCondition() public méthode

Creates a condition based on column-value pairs.
public buildHashCondition ( IndexSchema[] $indexes, array $condition, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$condition array the condition specification.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildHaving() public méthode

public buildHaving ( string[] $indexes, string | array $condition, array &$params ) : string
$indexes string[] list of index names, which affected by query
$condition string | array
$params array the binding parameters to be populated
Résultat string the HAVING clause built from [[Query::$having]].

buildInCondition() public méthode

Creates an SQL expressions with the IN operator.
public buildInCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use (e.g. `IN` or `NOT IN`)
$operands array the first operand is the column name. If it is an array a composite IN condition will be generated. The second operand is an array of values that column value should be among. If it is an empty array the generated expression will be a `false` value if operator is `IN` and empty if operator is `NOT IN`.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildLikeCondition() public méthode

Creates an SQL expressions with the LIKE operator.
public buildLikeCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use (e.g. `LIKE`, `NOT LIKE`, `OR LIKE` or `OR NOT LIKE`)
$operands array an array of two or three operands - The first operand is the column name. - The second operand is a single value or an array of values that column value should be compared with. If it is an empty array the generated expression will be a `false` value if operator is `LIKE` or `OR LIKE`, and empty if operator is `NOT LIKE` or `OR NOT LIKE`. - An optional third operand can also be provided to specify how to escape special characters in the value(s). The operand should be an array of mappings from the special characters to their escaped counterparts. If this operand is not provided, a default escape mapping will be used. You may use `false` or an empty array to indicate the values are already escaped and no escape should be applied. Note that when using an escape mapping (or the third operand is not provided), the values will be automatically enclosed within a pair of percentage characters.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildLimit() public méthode

public buildLimit ( integer $limit, integer $offset ) : string
$limit integer
$offset integer
Résultat string the LIMIT and OFFSET clauses built from [[query]].

buildMatch() public méthode

public buildMatch ( string | yii\db\Expression | MatchExpression $match, array &$params ) : string
$match string | yii\db\Expression | MatchExpression match condition
$params array the binding parameters to be populated
Résultat string generated MATCH expression

buildNotCondition() public méthode

Inverts an SQL expressions with NOT operator.
public buildNotCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use for connecting the given operands
$operands array the SQL expressions to connect.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildOption() public méthode

public buildOption ( array $options, array &$params ) : string
$options array query options in format: optionName => optionValue
$params array the binding parameters to be populated
Résultat string the OPTION clause build from [[query]]

buildOrderBy() public méthode

public buildOrderBy ( array $columns ) : string
$columns array
Résultat string the ORDER BY clause built from [[query]].

buildOrderByAndLimit() public méthode

Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
public buildOrderByAndLimit ( string $sql, array $orderBy, integer $limit, integer $offset ) : string
$sql string the existing SQL (without ORDER BY/LIMIT/OFFSET)
$orderBy array the order by columns. See [[Query::orderBy]] for more details on how to specify this parameter.
$limit integer the limit number. See [[Query::limit]] for more details.
$offset integer the offset number. See [[Query::offset]] for more details.
Résultat string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)

buildSelect() public méthode

public buildSelect ( array $columns, array &$params, boolean $distinct = false, string $selectOption = null ) : string
$columns array
$params array the binding parameters to be populated
$distinct boolean
$selectOption string
Résultat string the SELECT clause built from [[query]].

buildShowMeta() protected méthode

Builds SHOW META query.
protected buildShowMeta ( boolean | string | yii\db\Expression $showMeta, array &$params ) : string
$showMeta boolean | string | yii\db\Expression show meta specification.
$params array the binding parameters to be populated
Résultat string SHOW META query, if it does not required - empty string.

buildSimpleCondition() public méthode

Creates an SQL expressions like "column" operator value.
public buildSimpleCondition ( IndexSchema[] $indexes, string $operator, array $operands, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$operator string the operator to use. Anything could be used e.g. `>`, `<=`, etc.
$operands array contains two column names.
$params array the binding parameters to be populated
Résultat string the generated SQL expression

buildWhere() public méthode

public buildWhere ( string[] $indexes, string | array $condition, array &$params, string | yii\db\Expression | null $match = null ) : string
$indexes string[] list of index names, which affected by query
$condition string | array
$params array the binding parameters to be populated
$match string | yii\db\Expression | null
Résultat string the WHERE clause built from [[query]].

buildWithin() public méthode

public buildWithin ( array $columns ) : string
$columns array
Résultat string the ORDER BY clause built from [[query]].

callKeywords() public méthode

Builds a SQL statement for returning tokenized and normalized forms of the keywords, and, optionally, keyword statistics.
public callKeywords ( string $index, string $text, boolean $fetchStatistic, array &$params ) : string
$index string the name of the index from which to take the text processing settings
$text string the text to break down to keywords.
$fetchStatistic boolean whether to return document and hit occurrence statistics
$params array the binding parameters that will be modified by this method so that they can be bound to the Sphinx command later.
Résultat string the SQL statement for call keywords.

callSnippets() public méthode

Builds a SQL statement for call snippet from provided data and query, using specified index settings.
public callSnippets ( string $index, string | array $source, string $match, array $options, array &$params ) : string
$index string name of the index, from which to take the text processing settings.
$source string | array is the source data to extract a snippet from. It could be either a single string or array of strings.
$match string the full-text query to build snippets for.
$options array list of options in format: optionName => optionValue
$params array the binding parameters that will be modified by this method so that they can be bound to the Sphinx command later.
Résultat string the SQL statement for call snippets.

composeColumnValue() protected méthode

Composes column value for SQL, taking in account the column type.
protected composeColumnValue ( IndexSchema[] $indexes, string $columnName, mixed $value, array &$params ) : string
$indexes IndexSchema[] list of indexes, which affected by query
$columnName string name of the column
$value mixed raw column value
$params array the binding parameters to be populated
Résultat string SQL expression, which represents column value

delete() public méthode

For example, php $sql = $queryBuilder->delete('idx_user', 'status = 0'); The method will properly escape the index and column names.
public delete ( string $index, array | string $condition, array &$params ) : string
$index string the index where the data will be deleted from.
$condition array | string the condition that will be put in the WHERE part. Please refer to [[Query::where()]] on how to specify condition.
$params array the binding parameters that will be modified by this method so that they can be bound to the Sphinx command later.
Résultat string the DELETE SQL

generateBatchInsertReplace() protected méthode

Generates a batch INSERT/REPLACE SQL statement.
protected generateBatchInsertReplace ( string $statement, string $index, array $columns, array $rows, array &$params ) : string
$statement string statement ot be generated.
$index string the affected index name.
$columns array the column data (name => value).
$rows array the rows to be batch inserted into the index
$params array the binding parameters that will be generated by this method.
Résultat string generated SQL

generateInsertReplace() protected méthode

Generates INSERT/REPLACE SQL statement.
protected generateInsertReplace ( string $statement, string $index, array $columns, array &$params ) : string
$statement string statement ot be generated.
$index string the affected index name.
$columns array the column data (name => value).
$params array the binding parameters that will be generated by this method.
Résultat string generated SQL

getMatchBuilder() public méthode

Since: 2.0.6
public getMatchBuilder ( ) : MatchBuilder
Résultat MatchBuilder match builder.

insert() public méthode

For example, php $sql = $queryBuilder->insert('idx_user', [ 'name' => 'Sam', 'age' => 30, 'id' => 10, ], $params); The method will properly escape the index and column names.
public insert ( string $index, array $columns, array &$params ) : string
$index string the index that new rows will be inserted into.
$columns array the column data (name => value) to be inserted into the index.
$params array the binding parameters that will be generated by this method. They should be bound to the Sphinx command later.
Résultat string the INSERT SQL

replace() public méthode

For example, php $sql = $queryBuilder->replace('idx_user', [ 'name' => 'Sam', 'age' => 30, 'id' => 10, ], $params); The method will properly escape the index and column names.
public replace ( string $index, array $columns, array &$params ) : string
$index string the index that new rows will be replaced.
$columns array the column data (name => value) to be replaced in the index.
$params array the binding parameters that will be generated by this method. They should be bound to the Sphinx command later.
Résultat string the INSERT SQL

truncateIndex() public méthode

Builds a SQL statement for truncating an index.
public truncateIndex ( string $index ) : string
$index string the index to be truncated. The name will be properly quoted by the method.
Résultat string the SQL statement for truncating an index.

update() public méthode

For example, php $params = []; $sql = $queryBuilder->update('idx_user', ['status' => 1], 'age > 30', $params); The method will properly escape the index and column names.
public update ( string $index, array $columns, array | string $condition, array &$params, array $options ) : string
$index string the index to be updated.
$columns array the column data (name => value) to be updated.
$condition array | string the condition that will be put in the WHERE part. Please refer to [[Query::where()]] on how to specify condition.
$params array the binding parameters that will be modified by this method so that they can be bound to the Sphinx command later.
$options array list of options in format: optionName => optionValue
Résultat string the UPDATE SQL

Property Details

$conditionBuilders protected_oe property

map of query condition to builder methods. These methods are used by [[buildCondition]] to build SQL conditions from array syntax.
protected $conditionBuilders

$db public_oe property

the Sphinx connection.
public $db

$querySeparator public_oe property

separator between different SQL queries. This is mainly used by QueryBuilder::build when generating a SQL statement.
public $querySeparator

$separator public_oe property

the separator between different fragments of a SQL statement. Defaults to an empty space. This is mainly used by QueryBuilder::build when generating a SQL statement.
public $separator