Class yii\sphinx\QueryBuilder

All Classes | Properties | Methods | Constants
Inheritanceyii\sphinx\QueryBuilder » yii\base\BaseObject
Available since extension's version2.0
Source Code https://github.com/yiisoft/yii2-sphinx/blob/master/src/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

Property Type Description Defined By
$conditionBuilders array Map of query condition to builder methods. yii\sphinx\QueryBuilder
$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

Property Type Description Defined By

Public Methods

Hide inherited methods

Method Description Defined By
__construct() Constructor. yii\sphinx\QueryBuilder
addColumn() Builds a SQL statement for adding a new index column. 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
createTable() Builds a SQL statement for creating a new index table. yii\sphinx\QueryBuilder
delete() Creates a DELETE SQL statement. yii\sphinx\QueryBuilder
dropColumn() Builds a SQL statement for dropping a index column. yii\sphinx\QueryBuilder
dropTable() Builds a SQL statement for dropping a index. 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

Hide inherited properties

$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 property

Match builder.

public yii\sphinx\MatchBuilder $matchBuilder = 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

Hide inherited methods

__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

 

                public function __construct($connection, $config = [])
{
    $this->db = $connection;
    parent::__construct($config);
}
addColumn() public method (available since version 2.0.14)

Builds a SQL statement for adding a new index column.

public string addColumn ( $table, $column, $type )
$table string

The index that the new column will be added to. The index name will be properly quoted by the method.
$column string

The name of the new column. The name will be properly quoted by the method.
$type string

The column type.
return string

The SQL statement for adding a new column.

 

                public function addColumn($table, $column, $type)
{
    return 'ALTER TABLE ' . $this->db->quoteTableName($table)
        . ' ADD COLUMN ' . $this->db->quoteColumnName($column) . ' '
        . $type;
}
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

 

                public function batchInsert($index, $columns, $rows, &$params)
{
    return $this->generateBatchInsertReplace('INSERT', $index, $columns, $rows, $params);
}
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

 

                public function batchReplace($index, $columns, $rows, &$params)
{
    return $this->generateBatchInsertReplace('REPLACE', $index, $columns, $rows, $params);
}
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.

 

                public function build($query, $params = [])
{
    $query = $query->prepare($this);
    if (!empty($query->join)) {
        throw new NotSupportedException('Build of "' . get_class($query) . '::join" is not supported.');
    }
    $params = empty($params) ? $query->params : array_merge($params, $query->params);
    $from = $query->from;
    if ($from === null && $query instanceof ActiveQuery) {
        /* @var $modelClass ActiveRecord */
        $modelClass = $query->modelClass;
        $from = [$modelClass::indexName()];
    }
    $clauses = [
        $this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
        $this->buildFrom($from, $params),
        $this->buildWhere($from, $query->where, $params, $query->match),
        $this->buildGroupBy($query->groupBy, $query->groupLimit),
        $this->buildWithin($query->within),
        $this->buildHaving($query->from, $query->having, $params),
        $this->buildOrderBy($query->orderBy),
        $this->buildLimit($query->limit, $query->offset),
        $this->buildOption($query->options, $params),
        $this->buildFacets($query->facets, $params),
    ];
    $sql = implode($this->separator, array_filter($clauses));
    $showMetaSql = $this->buildShowMeta($query->showMeta, $params);
    if (!empty($showMetaSql)) {
        $sql .= $this->querySeparator . $showMetaSql;
    }
    return [$sql, $params];
}
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

 

                public function buildAndCondition($indexes, $operator, $operands, &$params)
{
    $parts = [];
    foreach ($operands as $operand) {
        if (is_array($operand) || $operand instanceof Expression) {
            $operand = $this->buildCondition($indexes, $operand, $params);
        }
        if ($operand !== '') {
            $parts[] = $operand;
        }
    }
    if (!empty($parts)) {
        return '(' . implode(") $operator (", $parts) . ')';
    }
    return '';
}
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.

 

                public function buildBetweenCondition($indexes, $operator, $operands, &$params)
{
    if (!isset($operands[0], $operands[1], $operands[2])) {
        throw new Exception("Operator '$operator' requires three operands.");
    }
    list($column, $value1, $value2) = $operands;
    if (strpos($column, '(') === false) {
        $quotedColumn = $this->db->quoteColumnName($column);
    } else {
        $quotedColumn = $column;
    }
    $phName1 = $this->composeColumnValue($indexes, $column, $value1, $params);
    $phName2 = $this->composeColumnValue($indexes, $column, $value2, $params);
    return "$quotedColumn $operator $phName1 AND $phName2";
}
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

 

                public function buildColumns($columns)
{
    if (!is_array($columns)) {
        if (strpos($columns, '(') !== false) {
            return $columns;
        } else {
            $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
        }
    }
    foreach ($columns as $i => $column) {
        if ($column instanceof Expression) {
            $columns[$i] = $column->expression;
        } elseif (strpos($column, '(') === false) {
            $columns[$i] = $this->db->quoteColumnName($column);
        }
    }
    return is_array($columns) ? implode(', ', $columns) : $columns;
}
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

 

                protected function buildCompositeInCondition($indexes, $operator, $columns, $values, &$params)
{
    $vss = [];
    foreach ($values as $value) {
        $vs = [];
        foreach ($columns as $column) {
            if (isset($value[$column])) {
                $vs[] = $this->composeColumnValue($indexes, $column, $value[$column], $params);
            } else {
                $vs[] = 'NULL';
            }
        }
        $vss[] = '(' . implode(', ', $vs) . ')';
    }
    $sqlColumns = [];
    foreach ($columns as $i => $column) {
        if (strpos($column, '(') === false) {
            $sqlColumns[$i] = $this->db->quoteColumnName($column);
        }
    }
    return '(' . implode(', ', $sqlColumns) . ") $operator (" . implode(', ', $vss) . ')';
}
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 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

 

                public function buildCondition($indexes, $condition, &$params)
{
    if ($condition instanceof Expression) {
        foreach ($condition->params as $n => $v) {
            $params[$n] = $v;
        }
        return $condition->expression;
    } elseif (!is_array($condition)) {
        return (string) $condition;
    } elseif (empty($condition)) {
        return '';
    }
    if (isset($condition[0])) {
        // operator format: operator, operand 1, operand 2, ...
        $operator = strtoupper($condition[0]);
        if (isset($this->conditionBuilders[$operator])) {
            $method = $this->conditionBuilders[$operator];
        } else {
            $method = 'buildSimpleCondition';
        }
        array_shift($condition);
        return $this->$method($indexes, $operator, $condition, $params);
    }
    // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
    return $this->buildHashCondition($indexes, $condition, $params);
}
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.

 

                protected function buildFacets($facets, &$params)
{
    if (empty($facets)) {
        return '';
    }
    $sqlParts = [];
    foreach ($facets as $key => $value) {
        if (is_numeric($key)) {
            $facet = [
                'select' => $value
            ];
        } else {
            if (is_array($value)) {
                $facet = $value;
                if (!array_key_exists('select', $facet)) {
                    $facet['select'] = $key;
                }
            } else {
                throw new InvalidConfigException('Facet specification must be an array, "' . gettype($value) . '" given.');
            }
        }
        if (!array_key_exists('limit', $facet)) {
            $facet['limit'] = null;
        }
        if (!array_key_exists('offset', $facet)) {
            $facet['offset'] = null;
        }
        $facetSql = 'FACET ' . $this->buildSelectFields((array)$facet['select'], $params);
        if (!empty($facet['order'])) {
            $facetSql .= ' ' . $this->buildOrderBy($facet['order']);
        }
        $facetSql .= ' ' . $this->buildLimit($facet['limit'], $facet['offset']);
        $sqlParts[] = $facetSql;
    }
    return implode($this->separator, $sqlParts);
}
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.

 

                public function buildFrom($indexes, &$params)
{
    if (empty($indexes)) {
        return '';
    }
    foreach ($indexes as $i => $index) {
        if ($index instanceof Query) {
            list($sql, $params) = $this->build($index, $params);
            $indexes[$i] = "($sql) " . $this->db->quoteIndexName($i);
        } elseif (is_string($i)) {
            if (strpos($index, '(') === false) {
                $index = $this->db->quoteIndexName($index);
            }
            $indexes[$i] = "$index " . $this->db->quoteIndexName($i);
        } elseif (strpos($index, '(') === false) {
            if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $index, $matches)) { // with alias
                $indexes[$i] = $this->db->quoteIndexName($matches[1]) . ' ' . $this->db->quoteIndexName($matches[2]);
            } else {
                $indexes[$i] = $this->db->quoteIndexName($index);
            }
        }
    }
    if (is_array($indexes)) {
        $indexes = implode(', ', $indexes);
    }
    return 'FROM ' . $indexes;
}
buildGroupBy() public method

public string buildGroupBy ( $columns, $limit )
$columns array

Group columns
$limit integer

Group limit
return string

The GROUP BY clause

 

                public function buildGroupBy($columns, $limit)
{
    if (empty($columns)) {
        return '';
    }
    if (is_string($limit) && ctype_digit($limit) || is_int($limit) && $limit >= 0) {
        $limitSql = ' ' . $limit;
    } else {
        $limitSql = '';
    }
    return 'GROUP' . $limitSql . ' BY ' . $this->buildColumns($columns);
}
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

 

                public function buildHashCondition($indexes, $condition, &$params)
{
    $parts = [];
    foreach ($condition as $column => $value) {
        if (is_array($value) || $value instanceof \Traversable || $value instanceof Query) {
            // IN condition
            $parts[] = $this->buildInCondition($indexes, 'IN', [$column, $value], $params);
        } else {
            if (strpos($column, '(') === false) {
                $quotedColumn = $this->db->quoteColumnName($column);
            } else {
                $quotedColumn = $column;
            }
            if ($value === null) {
                $parts[] = "$quotedColumn IS NULL";
            } else {
                $parts[] = $quotedColumn . '=' . $this->composeColumnValue($indexes, $column, $value, $params);
            }
        }
    }
    return count($parts) === 1 ? $parts[0] : '(' . implode(') AND (', $parts) . ')';
}
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 Query::$having.

 

                public function buildHaving($indexes, $condition, &$params)
{
    if (empty($condition)) {
        return '';
    }
    $indexSchemas = $this->getIndexSchemas($indexes);
    $having = $this->buildCondition($indexSchemas, $condition, $params);
    return $having === '' ? '' : 'HAVING ' . $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.

 

                public function buildInCondition($indexes, $operator, $operands, &$params)
{
    if (!isset($operands[0], $operands[1])) {
        throw new Exception("Operator '$operator' requires two operands.");
    }
    list($column, $values) = $operands;
    if ($column === []) {
        return '';
    }
    if ($values instanceof Query) {
        // sub-query
        list($sql, $params) = $this->build($values, $params);
        $column = (array) $column;
        if (is_array($column)) {
            foreach ($column as $i => $col) {
                if (strpos($col, '(') === false) {
                    $column[$i] = $this->db->quoteColumnName($col);
                }
            }
            return '(' . implode(', ', $column) . ") $operator ($sql)";
        } else {
            if (strpos($column, '(') === false) {
                $column = $this->db->quoteColumnName($column);
            }
            return "$column $operator ($sql)";
        }
    }
    if (!is_array($values) && !$values instanceof \Traversable) {
        // ensure values is an array
        $values = (array) $values;
    }
    if ($column instanceof \Traversable || ((is_array($column) || $column instanceof \Countable) && count($column) > 1)) {
        return $this->buildCompositeInCondition($indexes, $operator, $column, $values, $params);
    } elseif (is_array($column)) {
        $column = reset($column);
    }
    $sqlValues = [];
    foreach ($values as $i => $value) {
        if (is_array($value)) {
            $value = isset($value[$column]) ? $value[$column] : null;
        }
        $sqlValues[$i] = $this->composeColumnValue($indexes, $column, $value, $params);
    }
    if (strpos($column, '(') === false) {
        $column = $this->db->quoteColumnName($column);
    }
    if ($sqlValues === []) {
        if ($operator === 'IN') {
            if (empty($column)) {
                throw new Exception("Operator '$operator' requires column being specified.");
            }
            $column = $this->db->quoteColumnName($column);
            return "({$column} = 0 AND {$column} = 1)";
        }
        return '';
    }
    if (count($sqlValues) > 1) {
        return "$column $operator (" . implode(', ', $sqlValues) . ')';
    }
    $operator = $operator === 'IN' ? '=' : '<>';
    return $column . $operator . reset($sqlValues);
}
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.

 

                public function buildLikeCondition($indexes, $operator, $operands, &$params)
{
    if (!isset($operands[0], $operands[1])) {
        throw new InvalidParamException("Operator '$operator' requires two operands.");
    }
    $escape = isset($operands[2]) ? $operands[2] : ['%'=>'\%', '_'=>'\_', '\\'=>'\\\\'];
    unset($operands[2]);
    list($column, $values) = $operands;
    if (!is_array($values)) {
        $values = [$values];
    }
    if (empty($values)) {
        return $operator === 'LIKE' || $operator === 'OR LIKE' ? '0=1' : '';
    }
    if ($operator === 'LIKE' || $operator === 'NOT LIKE') {
        $andor = ' AND ';
    } else {
        $andor = ' OR ';
        $operator = $operator === 'OR LIKE' ? 'LIKE' : 'NOT LIKE';
    }
    if (strpos($column, '(') === false) {
        $column = $this->db->quoteColumnName($column);
    }
    $parts = [];
    foreach ($values as $value) {
        if ($value instanceof Expression) {
            foreach ($value->params as $n => $v) {
                $params[$n] = $v;
            }
            $phName = $value->expression;
        } else {
            $phName = self::PARAM_PREFIX . count($params);
            $params[$phName] = empty($escape) ? $value : ('%' . strtr($value, $escape) . '%');
        }
        $parts[] = "$column $operator $phName";
    }
    return implode($andor, $parts);
}
buildLimit() public method

public string buildLimit ( $limit, $offset )
$limit integer
$offset integer
return string

The LIMIT and OFFSET clauses built from query.

 

                public function buildLimit($limit, $offset)
{
    $sql = '';
    if (is_int($offset) && $offset > 0 || is_string($offset) && ctype_digit($offset) && $offset !== '0') {
        $sql = 'LIMIT ' . $offset;
    }
    if (is_string($limit) && ctype_digit($limit) || is_int($limit) && $limit >= 0) {
        $sql = $sql === '' ? "LIMIT $limit" : "$sql,$limit";
    } elseif ($sql !== '') {
        $sql .= ',1000';  // this is the default limit by sphinx
    }
    return $sql;
}
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

 

                public function buildMatch($match, &$params)
{
    if ($match instanceof Expression) {
        $params = array_merge($params, $match->params);
        return $match->expression;
    }
    if ($match instanceof MatchExpression) {
        $phName = self::PARAM_PREFIX . count($params);
        $params[$phName] = $this->getMatchBuilder()->build($match);
        return $phName;
    }
    $phName = self::PARAM_PREFIX . count($params);
    $params[$phName] = $this->db->escapeMatchValue($match);
    return $phName;
}
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.

 

                public function buildNotCondition($indexes, $operator, $operands, &$params)
{
    if (count($operands) != 1) {
        throw new InvalidParamException("Operator '$operator' requires exactly one operand.");
    }
    $operand = reset($operands);
    if (is_array($operand) || $operand instanceof Expression) {
        $operand = $this->buildCondition($indexes, $operand, $params);
    }
    if ($operand === '') {
        return '';
    }
    return "$operator ($operand)";
}
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

 

                public function buildOption($options, &$params)
{
    if (empty($options)) {
        return '';
    }
    $optionLines = [];
    foreach ($options as $name => $value) {
        if ($value instanceof Expression) {
            $actualValue = $value->expression;
        } else {
            if (is_array($value)) {
                $actualValueParts = [];
                foreach ($value as $key => $valuePart) {
                    if (is_numeric($key)) {
                        $actualValuePart = '';
                    } else {
                        $actualValuePart = $key . ' = ';
                    }
                    if ($valuePart instanceof Expression) {
                        $actualValuePart .= $valuePart->expression;
                    } else {
                        $phName = self::PARAM_PREFIX . count($params);
                        $params[$phName] = $valuePart;
                        $actualValuePart .= $phName;
                    }
                    $actualValueParts[] = $actualValuePart;
                }
                $actualValue = '(' . implode(', ', $actualValueParts) . ')';
            } else {
                $actualValue = self::PARAM_PREFIX . count($params);
                $params[$actualValue] = $value;
            }
        }
        $optionLines[] = $name . ' = ' . $actualValue;
    }
    return 'OPTION ' . implode(', ', $optionLines);
}
buildOrderBy() public method

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

The ORDER BY clause built from query.

 

                public function buildOrderBy($columns)
{
    if (empty($columns)) {
        return '';
    }
    $orders = [];
    foreach ($columns as $name => $direction) {
        if ($direction instanceof Expression) {
            $orders[] = $direction->expression;
        } else {
            $orders[] = $this->db->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : ' ASC');
        }
    }
    return 'ORDER BY ' . implode(', ', $orders);
}
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 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.
return string

The SQL completed with ORDER BY/LIMIT/OFFSET (if any)

 

                public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
    $orderBy = $this->buildOrderBy($orderBy);
    if ($orderBy !== '') {
        $sql .= $this->separator . $orderBy;
    }
    $limit = $this->buildLimit($limit, $offset);
    if ($limit !== '') {
        $sql .= $this->separator . $limit;
    }
    return $sql;
}
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.

 

                public function buildSelect($columns, &$params, $distinct = false, $selectOption = null)
{
    $select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
    if ($selectOption !== null) {
        $select .= ' ' . $selectOption;
    }
    return $select . ' ' . $this->buildSelectFields($columns, $params);
}
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.

 

                protected function buildShowMeta($showMeta, &$params)
{
    if (empty($showMeta)) {
        return '';
    }
    $sql = 'SHOW META';
    if (is_bool($showMeta)) {
        return $sql;
    }
    if ($showMeta instanceof Expression) {
        foreach ($showMeta->params as $n => $v) {
            $params[$n] = $v;
        }
        $phName = $showMeta->expression;
    } else {
        $phName = self::PARAM_PREFIX . count($params);
        $escape = ['%'=>'\%', '_'=>'\_', '\\'=>'\\\\'];
        $params[$phName] = '%' . strtr($showMeta, $escape) . '%';
    }
    $sql .= " LIKE {$phName}";
    return $sql;
}
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

 

                public function buildSimpleCondition($indexes, $operator, $operands, &$params)
{
    if (count($operands) !== 2) {
        throw new InvalidParamException("Operator '$operator' requires two operands.");
    }
    list($column, $value) = $operands;
    $value = $this->composeColumnValue($indexes, $column, $value, $params);
    if (strpos($column, '(') === false) {
        $column = $this->db->quoteColumnName($column);
    }
    return "$column $operator $value";
}
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.

 

                public function buildWhere($indexes, $condition, &$params, $match = null)
{
    if ($match !== null) {
        $matchWhere = 'MATCH(' . $this->buildMatch($match, $params) . ')';
        if ($condition === null) {
            $condition = $matchWhere;
        } else {
            $condition = ['and', $matchWhere, $condition];
        }
    }
    if (empty($condition)) {
        return '';
    }
    $indexSchemas = $this->getIndexSchemas($indexes);
    $where = $this->buildCondition($indexSchemas, $condition, $params);
    return $where === '' ? '' : 'WHERE ' . $where;
}
buildWithin() public method

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

The ORDER BY clause built from query.

 

                public function buildWithin($columns)
{
    if (empty($columns)) {
        return '';
    }
    $orders = [];
    foreach ($columns as $name => $direction) {
        if ($direction instanceof Expression) {
            $orders[] = $direction->expression;
        } else {
            $orders[] = $this->db->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : ' ASC');
        }
    }
    return 'WITHIN GROUP ORDER BY ' . implode(', ', $orders);
}
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.

 

                public function callKeywords($index, $text, $fetchStatistic, &$params)
{
    $indexParamName = self::PARAM_PREFIX . count($params);
    $params[$indexParamName] = $index;
    $textParamName = self::PARAM_PREFIX . count($params);
    $params[$textParamName] = $text;
    return 'CALL KEYWORDS(' . $textParamName . ', ' . $indexParamName . ($fetchStatistic ? ', 1' : '') . ')';
}
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.

 

                public function callSnippets($index, $source, $match, $options, &$params)
{
    if (is_array($source)) {
        $dataSqlParts = [];
        foreach ($source as $sourceRow) {
            $phName = self::PARAM_PREFIX . count($params);
            $params[$phName] = (string)$sourceRow;
            $dataSqlParts[] = $phName;
        }
        $dataSql = '(' . implode(',', $dataSqlParts) . ')';
    } else {
        $phName = self::PARAM_PREFIX . count($params);
        $params[$phName] = $source;
        $dataSql = $phName;
    }
    $indexParamName = self::PARAM_PREFIX . count($params);
    $params[$indexParamName] = $index;
    $matchSql = $this->buildMatch($match, $params);
    if (!empty($options)) {
        $optionParts = [];
        foreach ($options as $name => $value) {
            if ($value instanceof Expression) {
                $actualValue = $value->expression;
            } else {
                $actualValue = self::PARAM_PREFIX . count($params);
                $params[$actualValue] = $value;
            }
            $optionParts[] = $actualValue . ' AS ' . $name;
        }
        $optionSql = ', ' . implode(', ', $optionParts);
    } else {
        $optionSql = '';
    }
    return 'CALL SNIPPETS(' . $dataSql. ', ' . $indexParamName . ', ' . $matchSql . $optionSql. ')';
}
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

 

                protected function composeColumnValue($indexes, $columnName, $value, &$params)
{
    if ($value === null) {
        return 'NULL';
    } elseif ($value instanceof Expression) {
        $params = array_merge($params, $value->params);
        return $value->expression;
    }
    foreach ($indexes as $index) {
        $columnSchema = $index->getColumn($columnName);
        if ($columnSchema !== null) {
            break;
        }
    }
    if (is_array($value)) {
        // MVA :
        $lineParts = [];
        foreach ($value as $subValue) {
            if ($subValue instanceof Expression) {
                $params = array_merge($params, $subValue->params);
                $lineParts[] = $subValue->expression;
            } else {
                $phName = self::PARAM_PREFIX . count($params);
                $lineParts[] = $phName;
                $params[$phName] = (isset($columnSchema)) ? $columnSchema->dbTypecast($subValue) : $subValue;
            }
        }
        return '(' . implode(',', $lineParts) . ')';
    } else {
        $phName = self::PARAM_PREFIX . count($params);
        $params[$phName] = (isset($columnSchema)) ? $columnSchema->dbTypecast($value) : $value;
        return $phName;
    }
}
createTable() public method (available since version 2.0.14)

Builds a SQL statement for creating a new index table.

The columns in the new index should be specified as name-definition pairs (e.g. 'name' => 'string'), where name stands for a column name which will be properly quoted by the method, and definition stands for the column type which can contain an abstract DB type.

For example,

$sql = $queryBuilder->createTable('user', [
 'id' => 'pk',
 'name' => 'string',
 'age' => 'integer',
]);
public string createTable ( $table, $columns, $options = null )
$table string

The name of the index to be created. The name will be properly quoted by the method.
$columns array

The columns (name => definition) in the new index.
$options string

Additional SQL fragment that will be appended to the generated SQL.
return string

The SQL statement for creating a new index.

 

                public function createTable($table, $columns, $options = null)
{
    $cols = [];
    foreach ($columns as $name => $type) {
        if (is_string($name)) {
            $cols[] = "\t" . $this->db->quoteColumnName($name) . ' ' . $type;
        } else {
            $cols[] = "\t" . $type;
        }
    }
    $sql = 'CREATE TABLE ' . $this->db->quoteTableName($table) . " (\n" . implode(",\n", $cols) . "\n)";
    return $options === null ? $sql : $sql . ' ' . $options;
}
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 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

 

                public function delete($index, $condition, &$params)
{
    $sql = 'DELETE FROM ' . $this->db->quoteIndexName($index);
    $where = $this->buildWhere([$index], $condition, $params);
    return $where === '' ? $sql : $sql . ' ' . $where;
}
dropColumn() public method (available since version 2.0.14)

Builds a SQL statement for dropping a index column.

public string dropColumn ( $table, $column )
$table string

The index whose column is to be dropped. The name will be properly quoted by the method.
$column string

The name of the column to be dropped. The name will be properly quoted by the method.
return string

The SQL statement for dropping a index column.

 

                public function dropColumn($table, $column)
{
    return 'ALTER TABLE ' . $this->db->quoteTableName($table)
        . ' DROP COLUMN ' . $this->db->quoteColumnName($column);
}
dropTable() public method (available since version 2.0.14)

Builds a SQL statement for dropping a index.

public string dropTable ( $table )
$table string

The table to be dropped. The name will be properly quoted by the method.
return string

The SQL statement for dropping a index.

 

                public function dropTable($table)
{
    return 'DROP TABLE ' . $this->db->quoteTableName($table);
}
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

 

                protected function generateBatchInsertReplace($statement, $index, $columns, $rows, &$params)
{
    if (($indexSchema = $this->db->getIndexSchema($index)) !== null) {
        $indexSchemas = [$indexSchema];
    } else {
        $indexSchemas = [];
    }
    $notNullColumns = [];
    $values = [];
    foreach ($rows as $row) {
        $vs = [];
        foreach ($row as $i => $value) {
            if ($value === null) {
                continue;
            } elseif (!in_array($columns[$i], $notNullColumns)) {
                $notNullColumns[] = $columns[$i];
            }
            $vs[] = $this->composeColumnValue($indexSchemas, $columns[$i], $value, $params);
        }
        $values[] = '(' . implode(', ', $vs) . ')';
    }
    foreach ($notNullColumns as $i => $name) {
        $notNullColumns[$i] = $this->db->quoteColumnName($name);
    }
    return $statement . ' INTO ' . $this->db->quoteIndexName($index)
        . ' (' . implode(', ', $notNullColumns) . ') VALUES ' . implode(', ', $values);
}
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

 

                protected function generateInsertReplace($statement, $index, $columns, &$params)
{
    if (($indexSchema = $this->db->getIndexSchema($index)) !== null) {
        $indexSchemas = [$indexSchema];
    } else {
        $indexSchemas = [];
    }
    $names = [];
    $placeholders = [];
    foreach ($columns as $name => $value) {
        if ($value === null) {
            // Sphinx does not allows inserting `null`, column should be skipped instead
            continue;
        }
        $names[] = $this->db->quoteColumnName($name);
        $placeholders[] = $this->composeColumnValue($indexSchemas, $name, $value, $params);
    }
    return $statement . ' INTO ' . $this->db->quoteIndexName($index)
        . ' (' . implode(', ', $names) . ') VALUES ('
        . implode(', ', $placeholders) . ')';
}
getMatchBuilder() public method (available since version 2.0.6)

public yii\sphinx\MatchBuilder getMatchBuilder ( )
return yii\sphinx\MatchBuilder

Match builder.

 

                public function getMatchBuilder()
{
    if ($this->_matchBuilder === null) {
        $this->_matchBuilder = new MatchBuilder($this->db);
    }
    return $this->_matchBuilder;
}
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

 

                public function insert($index, $columns, &$params)
{
    return $this->generateInsertReplace('INSERT', $index, $columns, $params);
}
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

 

                public function replace($index, $columns, &$params)
{
    return $this->generateInsertReplace('REPLACE', $index, $columns, $params);
}
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.

 

                public function truncateIndex($index)
{
    return 'TRUNCATE RTINDEX ' . $this->db->quoteIndexName($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 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

 

                public function update($index, $columns, $condition, &$params, $options)
{
    if (($indexSchema = $this->db->getIndexSchema($index)) !== null) {
        $indexSchemas = [$indexSchema];
    } else {
        $indexSchemas = [];
    }
    $lines = [];
    foreach ($columns as $name => $value) {
        $lines[] = $this->db->quoteColumnName($name) . '=' . $this->composeColumnValue($indexSchemas, $name, $value, $params);
    }
    $sql = 'UPDATE ' . $this->db->quoteIndexName($index) . ' SET ' . implode(', ', $lines);
    $where = $this->buildWhere([$index], $condition, $params);
    if ($where !== '') {
        $sql = $sql . ' ' . $where;
    }
    $option = $this->buildOption($options, $params);
    if ($option !== '') {
        $sql = $sql . ' ' . $option;
    }
    return $sql;
}