Class yii\sphinx\QueryBuilder

All Classes | Properties | Methods | Constants

Inheritance	yii\sphinx\QueryBuilder » yii\base\BaseObject
Available since version	2.0

QueryBuilder builds a SELECT SQL statement based on the specification given as a yii\sphinx\Query object.

QueryBuilder can also be used to build SQL statements such as INSERT, REPLACE, UPDATE, DELETE, from a yii\sphinx\Query object.

Public Properties

Hide inherited properties

Property	Type	Description	Defined By
$db	yii\sphinx\Connection	The Sphinx connection.	yii\sphinx\QueryBuilder
$querySeparator	string	Separator between different SQL queries.	yii\sphinx\QueryBuilder
$separator	string	The separator between different fragments of a SQL statement.	yii\sphinx\QueryBuilder

Protected Properties

Hide inherited properties

Property	Type	Description	Defined By
$conditionBuilders	array	Map of query condition to builder methods.	yii\sphinx\QueryBuilder

Public Methods

Hide inherited methods

Method	Description	Defined By
__construct()	Constructor.	yii\sphinx\QueryBuilder
batchInsert()	Generates a batch INSERT SQL statement.	yii\sphinx\QueryBuilder
batchReplace()	Generates a batch REPLACE SQL statement.	yii\sphinx\QueryBuilder
build()	Generates a SELECT SQL statement from a yii\sphinx\Query object.	yii\sphinx\QueryBuilder
buildAndCondition()	Connects two or more SQL expressions with the `AND` or `OR` operator.	yii\sphinx\QueryBuilder
buildBetweenCondition()	Creates an SQL expressions with the `BETWEEN` operator.	yii\sphinx\QueryBuilder
buildColumns()	Processes columns and properly quote them if necessary.	yii\sphinx\QueryBuilder
buildCondition()	Parses the condition specification and generates the corresponding SQL expression.	yii\sphinx\QueryBuilder
buildFrom()		yii\sphinx\QueryBuilder
buildGroupBy()		yii\sphinx\QueryBuilder
buildHashCondition()	Creates a condition based on column-value pairs.	yii\sphinx\QueryBuilder
buildHaving()		yii\sphinx\QueryBuilder
buildInCondition()	Creates an SQL expressions with the `IN` operator.	yii\sphinx\QueryBuilder
buildLikeCondition()	Creates an SQL expressions with the `LIKE` operator.	yii\sphinx\QueryBuilder
buildLimit()		yii\sphinx\QueryBuilder
buildMatch()		yii\sphinx\QueryBuilder
buildNotCondition()	Inverts an SQL expressions with `NOT` operator.	yii\sphinx\QueryBuilder
buildOption()		yii\sphinx\QueryBuilder
buildOrderBy()		yii\sphinx\QueryBuilder
buildOrderByAndLimit()	Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.	yii\sphinx\QueryBuilder
buildSelect()		yii\sphinx\QueryBuilder
buildSimpleCondition()	Creates an SQL expressions like `"column" operator value`.	yii\sphinx\QueryBuilder
buildWhere()		yii\sphinx\QueryBuilder
buildWithin()		yii\sphinx\QueryBuilder
callKeywords()	Builds a SQL statement for returning tokenized and normalized forms of the keywords, and, optionally, keyword statistics.	yii\sphinx\QueryBuilder
callSnippets()	Builds a SQL statement for call snippet from provided data and query, using specified index settings.	yii\sphinx\QueryBuilder
delete()	Creates a DELETE SQL statement.	yii\sphinx\QueryBuilder
getMatchBuilder()		yii\sphinx\QueryBuilder
insert()	Creates an INSERT SQL statement.	yii\sphinx\QueryBuilder
replace()	Creates an REPLACE SQL statement.	yii\sphinx\QueryBuilder
truncateIndex()	Builds a SQL statement for truncating an index.	yii\sphinx\QueryBuilder
update()	Creates an UPDATE SQL statement.	yii\sphinx\QueryBuilder

Protected Methods

Hide inherited methods

Method	Description	Defined By
buildCompositeInCondition()		yii\sphinx\QueryBuilder
buildFacets()		yii\sphinx\QueryBuilder
buildShowMeta()	Builds SHOW META query.	yii\sphinx\QueryBuilder
composeColumnValue()	Composes column value for SQL, taking in account the column type.	yii\sphinx\QueryBuilder
generateBatchInsertReplace()	Generates a batch INSERT/REPLACE SQL statement.	yii\sphinx\QueryBuilder
generateInsertReplace()	Generates INSERT/REPLACE SQL statement.	yii\sphinx\QueryBuilder

Constants

Hide inherited constants

Constant	Value	Description	Defined By
PARAM_PREFIX	':qp'	The prefix for automatically generated query binding parameters.	yii\sphinx\QueryBuilder

Property Details

$conditionBuilders protected property

Map of query condition to builder methods. These methods are used by buildCondition() to build SQL conditions from array syntax.

protected array $conditionBuilders = ['AND' => 'buildAndCondition', 'OR' => 'buildAndCondition', 'BETWEEN' => 'buildBetweenCondition', 'NOT BETWEEN' => 'buildBetweenCondition', 'IN' => 'buildInCondition', 'NOT IN' => 'buildInCondition', 'LIKE' => 'buildLikeCondition', 'NOT LIKE' => 'buildLikeCondition', 'OR LIKE' => 'buildLikeCondition', 'OR NOT LIKE' => 'buildLikeCondition', 'NOT' => 'buildNotCondition']

$db public property

The Sphinx connection.

public yii\sphinx\Connection $db = null

$querySeparator public property

Separator between different SQL queries. This is mainly used by build() when generating a SQL statement.

public string $querySeparator = "; "

$separator public property

The separator between different fragments of a SQL statement. Defaults to an empty space. This is mainly used by build() when generating a SQL statement.

public string $separator = " "

Method Details

__construct() public method

Constructor.

public void __construct ( $connection, $config = [] )
$connection	yii\sphinx\Connection	The Sphinx connection.
$config	array	Name-value pairs that will be used to initialize the object properties

batchInsert() public method

Generates a batch INSERT SQL statement.

For example,

$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 string batchInsert ( $index, $columns, $rows, &$params )
$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.
return	string	The batch INSERT SQL statement

batchReplace() public method

Generates a batch REPLACE SQL statement.

For example,

$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 string batchReplace ( $index, $columns, $rows, &$params )
$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.
return	string	The batch INSERT SQL statement

build() public method

Generates a SELECT SQL statement from a yii\sphinx\Query object.

public array build ( $query, $params = [] )
$query	yii\sphinx\Query	The yii\sphinx\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.
return	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`.
throws	\yii\base\NotSupportedException	if query contains 'join' option.

buildAndCondition() public method

Connects two or more SQL expressions with the AND or OR operator.

public string buildAndCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression

buildBetweenCondition() public method

Creates an SQL expressions with the BETWEEN operator.

public string buildBetweenCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression
throws	\yii\db\Exception	if wrong number of operands have been given.

buildColumns() public method

Processes columns and properly quote them if necessary.

It will join all columns into a string with comma as separators.

public string buildColumns ( $columns )
$columns	string\|array	The columns to be processed
return	string	The processing result

buildCompositeInCondition() protected method

protected string buildCompositeInCondition ( $indexes, $operator, $columns, $values, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression

buildCondition() public method

Parses the condition specification and generates the corresponding SQL expression.

public string buildCondition ( $indexes, $condition, &$params )
$indexes	yii\sphinx\IndexSchema[]	List of indexes, which affected by query
$condition	string\|array	The condition specification. Please refer to yii\sphinx\Query::where() on how to specify a condition.
$params	array	The binding parameters to be populated
return	string	The generated SQL expression
throws	\yii\db\Exception	if the condition is in bad format

buildFacets() protected method

protected string buildFacets ( $facets, &$params )
$facets	array	Facet specifications
$params	array	The binding parameters to be populated
return	string	The FACET clause build from \yii\sphinx\query
throws	\yii\base\InvalidConfigException	on invalid facet specification.

buildFrom() public method

public string buildFrom ( $indexes, &$params )
$indexes	array
$params	array	The binding parameters to be populated
return	string	The FROM clause built from \yii\sphinx\query.

buildGroupBy() public method

public string buildGroupBy ( $columns, $limit )
$columns	array	Group columns
$limit	integer	Group limit
return	string	The GROUP BY clause

buildHashCondition() public method

Creates a condition based on column-value pairs.

public string buildHashCondition ( $indexes, $condition, &$params )
$indexes	yii\sphinx\IndexSchema[]	List of indexes, which affected by query
$condition	array	The condition specification.
$params	array	The binding parameters to be populated
return	string	The generated SQL expression

buildHaving() public method

public string buildHaving ( $indexes, $condition, &$params )
$indexes	string[]	List of index names, which affected by query
$condition	string\|array
$params	array	The binding parameters to be populated
return	string	The HAVING clause built from yii\sphinx\Query::$having.

buildInCondition() public method

Creates an SQL expressions with the IN operator.

public string buildInCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression
throws	\yii\db\Exception	if wrong number of operands have been given.

buildLikeCondition() public method

Creates an SQL expressions with the LIKE operator.

public string buildLikeCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression
throws	\yii\base\InvalidParamException	if wrong number of operands have been given.

buildLimit() public method

public string buildLimit ( $limit, $offset )
$limit	integer
$offset	integer
return	string	The LIMIT and OFFSET clauses built from \yii\sphinx\query.

buildMatch() public method

public string buildMatch ( $match, &$params )
$match	string\|\yii\db\Expression\|yii\sphinx\MatchExpression	Match condition
$params	array	The binding parameters to be populated
return	string	Generated MATCH expression

buildNotCondition() public method

Inverts an SQL expressions with NOT operator.

public string buildNotCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression
throws	\yii\base\InvalidParamException	if wrong number of operands have been given.

buildOption() public method

public string buildOption ( $options, &$params )
$options	array	Query options in format: optionName => optionValue
$params	array	The binding parameters to be populated
return	string	The OPTION clause build from \yii\sphinx\query

buildOrderBy() public method

public string buildOrderBy ( $columns )
$columns	array
return	string	The ORDER BY clause built from \yii\sphinx\query.

buildOrderByAndLimit() public method

Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.

public string buildOrderByAndLimit ( $sql, $orderBy, $limit, $offset )
$sql	string	The existing SQL (without ORDER BY/LIMIT/OFFSET)
$orderBy	array	The order by columns. See yii\sphinx\Query::orderBy for more details on how to specify this parameter.
$limit	integer	The limit number. See yii\sphinx\Query::limit for more details.
$offset	integer	The offset number. See yii\sphinx\Query::offset for more details.
return	string	The SQL completed with ORDER BY/LIMIT/OFFSET (if any)

buildSelect() public method

public string buildSelect ( $columns, &$params, $distinct = false, $selectOption = null )
$columns	array
$params	array	The binding parameters to be populated
$distinct	boolean
$selectOption	string
return	string	The SELECT clause built from \yii\sphinx\query.

buildShowMeta() protected method

Builds SHOW META query.

protected string buildShowMeta ( $showMeta, &$params )
$showMeta	boolean\|string\|\yii\db\Expression	Show meta specification.
$params	array	The binding parameters to be populated
return	string	SHOW META query, if it does not required - empty string.

buildSimpleCondition() public method

Creates an SQL expressions like "column" operator value.

public string buildSimpleCondition ( $indexes, $operator, $operands, &$params )
$indexes	yii\sphinx\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
return	string	The generated SQL expression
throws	\yii\base\InvalidParamException	if count($operands) is not 2

buildWhere() public method

public string buildWhere ( $indexes, $condition, &$params, $match = null )
$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
return	string	The WHERE clause built from \yii\sphinx\query.

buildWithin() public method

public string buildWithin ( $columns )
$columns	array
return	string	The ORDER BY clause built from \yii\sphinx\query.

callKeywords() public method

Builds a SQL statement for returning tokenized and normalized forms of the keywords, and, optionally, keyword statistics.

public string callKeywords ( $index, $text, $fetchStatistic, &$params )
$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.
return	string	The SQL statement for call keywords.

callSnippets() public method

Builds a SQL statement for call snippet from provided data and query, using specified index settings.

public string callSnippets ( $index, $source, $match, $options, &$params )
$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.
return	string	The SQL statement for call snippets.

composeColumnValue() protected method

Composes column value for SQL, taking in account the column type.

protected string composeColumnValue ( $indexes, $columnName, $value, &$params )
$indexes	yii\sphinx\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
return	string	SQL expression, which represents column value

delete() public method

Creates a DELETE SQL statement.

For example,

$sql = $queryBuilder->delete('idx_user', 'status = 0');

The method will properly escape the index and column names.

public string delete ( $index, $condition, &$params )
$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 yii\sphinx\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.
return	string	The DELETE SQL

generateBatchInsertReplace() protected method

Generates a batch INSERT/REPLACE SQL statement.

protected string generateBatchInsertReplace ( $statement, $index, $columns, $rows, &$params )
$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.
return	string	Generated SQL

generateInsertReplace() protected method

Generates INSERT/REPLACE SQL statement.

protected string generateInsertReplace ( $statement, $index, $columns, &$params )
$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.
return	string	Generated SQL

getMatchBuilder() public method (available since version 2.0.6)

public yii\sphinx\MatchBuilder getMatchBuilder ( )
return	yii\sphinx\MatchBuilder	Match builder.

insert() public method

Creates an INSERT SQL statement.

For example,

$sql = $queryBuilder->insert('idx_user', [
    'name' => 'Sam',
    'age' => 30,
    'id' => 10,
], $params);

The method will properly escape the index and column names.

public string insert ( $index, $columns, &$params )
$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.
return	string	The INSERT SQL

replace() public method

Creates an REPLACE SQL statement.

For example,

$sql = $queryBuilder->replace('idx_user', [
    'name' => 'Sam',
    'age' => 30,
    'id' => 10,
], $params);

The method will properly escape the index and column names.

public string replace ( $index, $columns, &$params )
$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.
return	string	The INSERT SQL

truncateIndex() public method

Builds a SQL statement for truncating an index.

public string truncateIndex ( $index )
$index	string	The index to be truncated. The name will be properly quoted by the method.
return	string	The SQL statement for truncating an index.

update() public method

Creates an UPDATE SQL statement.

For example,

$params = [];
$sql = $queryBuilder->update('idx_user', ['status' => 1], 'age > 30', $params);

The method will properly escape the index and column names.

public string update ( $index, $columns, $condition, &$params, $options )
$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 yii\sphinx\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
return	string	The UPDATE SQL