SphinxQL Query Builder

Creating a Query Builder Instance

You can create an instance by using the following code and passing a configured Connection class.

<?php

use Foolz\SphinxQL\Drivers\Mysqli\Connection;
use Foolz\SphinxQL\SphinxQL;

$conn = new Connection();
$queryBuilder = SphinxQL::create($conn);

Building a Query

The Foolz\SphinxQL\SphinxQL class supports building the following queries: SELECT, INSERT, UPDATE, and DELETE. Which sort of query being generated depends on the methods called.

For SELECT queries, you would start by invoking the select() method:

$queryBuilder
  ->select('id', 'name')
  ->from('index');

For INSERT, REPLACE, UPDATE and DELETE queries, you can pass the index as a parameter into the following methods:

$queryBuilder
  ->insert('index');

$queryBuilder
  ->replace('index');

$queryBuilder
  ->update('index');

$queryBuilder
  ->delete('index');

Note

You can convert the query builder into its compiled SphinxQL dialect string representation by calling $queryBuilder->compile()->getCompiled().

Security: Bypass Query Escaping

SphinxQL::expr($string)

Security: Query Escaping

$queryBuilder
  ->escape($value);

$queryBuilder
  ->quoteIdentifier($value);

$queryBuilder
  ->quote($value);

$queryBuilder
  ->escapeMatch($value);

$queryBuilder
  ->halfEscapeMatch($value);

WHERE Clause

The SELECT, UPDATE and DELETE statements supports the WHERE clause with the following API methods:

// WHERE `$column` = '$value'
$queryBuilder
  ->where($column, $value);

// WHERE `$column` = '$value'
$queryBuilder
  ->where($column, '=', $value);

// WHERE `$column` >= '$value'
$queryBuilder
  ->where($column, '>=', $value)

// WHERE `$column` IN ('$value1', '$value2', '$value3')
$queryBuilder
  ->where($column, 'IN', array($value1, $value2, $value3));

// WHERE `$column` NOT IN ('$value1', '$value2', '$value3')
$queryBuilder
  ->where($column, 'NOT IN', array($value1, $value2, $value3));

// WHERE `$column` BETWEEN '$value1' AND '$value2'
$queryBuilder
  ->where($column, 'BETWEEN', array($value1, $value2))

Warning

Currently, the SphinxQL dialect does not support the OR operator and grouping with parenthesis.

MATCH Clause

MATCH extends the WHERE clause and allows for full-text search capabilities.

$queryBuilder
  ->match($column, $value, $halfEscape = false);

By default, all inputs are automatically escaped by the query builder. The usage of SphinxQL::expr($value) can be used to bypass the default query escaping and quoting functions in place during query compilation. The $column argument accepts a string or an array. The $halfEscape argument, if set to true, will not escape and allow the usage of the following special characters: -, |, and “.

SET Clause

$queryBuilder
  ->set($associativeArray);

$queryBuilder
  ->value($column1, $value1)
  ->value($colume2, $value2);

$queryBuilder
  ->columns($column1, $column2, $column3)
  ->values($value1_1, $value2_1, $value3_1)
  ->values($value1_2, $value2_2, $value3_2);

GROUP BY Clause

The GROUP BY supports grouping by multiple columns or computed expressions.

// GROUP BY $column
$queryBuilder
  ->groupBy($column);

WITHIN GROUP ORDER BY

The WITHIN GROUP ORDER BY clause allows you to control how the best row within a group will be selected.

// WITHIN GROUP ORDER BY $column [$direction]
$queryBuilder
  ->withinGroupOrderBy($column, $direction = null);

ORDER BY Clause

Unlike in regular SQL, only column names (not expressions) are allowed.

// ORDER BY $column [$direction]
$queryBuilder
  ->orderBy($column, $direction = null);

OFFSET and LIMIT Clause

// LIMIT $offset, $limit
$queryBuilder
  ->limit($offset, $limit);

// LIMIT $limit
$queryBuilder
  ->limit($limit);

OPTION Clause

The OPTION clause allows you to control a number of per-query options.

// OPTION $name = $value
$queryBuilder
  ->option($name, $value);

COMPILE

You can have the query builder compile the generated query for debugging with the following method:

$queryBuilder
  ->compile();

This can be used for debugging purposes and obtaining the resulting query generated.

EXECUTE

In order to run the query, you must invoke the execute() method so that the query builder can compile the query for execution and then return the results of the query.

$queryBuilder
  ->execute();