Class yii\sphinx\QueryBuilder

All Classes | Properties | Methods | Constants
Inheritanceyii\sphinx\QueryBuilder » yii\base\BaseObject
Implementsyii\base\Configurable
Available since version2.0
Source Code https://github.com/yiisoft/yii2-sphinx/blob/master/QueryBuilder.php

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

PropertyTypeDescriptionDefined By
$db yii\sphinx\Connection The Sphinx connection. yii\sphinx\QueryBuilder
$matchBuilder yii\sphinx\MatchBuilder Match builder. 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

PropertyTypeDescriptionDefined By
$conditionBuilders array Map of query condition to builder methods. yii\sphinx\QueryBuilder

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. yii\base\BaseObject
__construct() Constructor. yii\sphinx\QueryBuilder
__get() Returns the value of an object property. yii\base\BaseObject
__isset() Checks if a property is set, i.e. defined and not null. yii\base\BaseObject
__set() Sets value of an object property. yii\base\BaseObject
__unset() Sets an object property to null. yii\base\BaseObject
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
canGetProperty() Returns a value indicating whether a property can be read. yii\base\BaseObject
canSetProperty() Returns a value indicating whether a property can be set. yii\base\BaseObject
className() Returns the fully qualified name of this class. yii\base\BaseObject
delete() Creates a DELETE SQL statement. yii\sphinx\QueryBuilder
getMatchBuilder() yii\sphinx\QueryBuilder
hasMethod() Returns a value indicating whether a method is defined. yii\base\BaseObject
hasProperty() Returns a value indicating whether a property is defined. yii\base\BaseObject
init() Initializes the object. yii\base\BaseObject
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

MethodDescriptionDefined 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

ConstantValueDescriptionDefined 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
$matchBuilder public read-only property (available since version 2.0.6)

Match builder.

public yii\sphinx\MatchBuilder getMatchBuilder ( )
$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 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 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 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 query
buildOrderBy() public method

public string buildOrderBy ( $columns )
$columns array
return string

The ORDER BY clause built from 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 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 query.
buildWithin() public method

public string buildWithin ( $columns )
$columns array
return string

The ORDER BY clause built from 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