Download the PHP package foolz/sphinxql-query-builder without Composer
On this page you can find all versions of the php package foolz/sphinxql-query-builder. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download foolz/sphinxql-query-builder
More information about foolz/sphinxql-query-builder
Files in foolz/sphinxql-query-builder
Package sphinxql-query-builder
Short Description A PHP query builder for SphinxQL. Uses MySQLi to connect to the Sphinx server.
License Apache-2.0
Homepage http://www.foolz.us
Informations about the package sphinxql-query-builder
Query Builder for SphinxQL
About
This is a SphinxQL Query Builder used to work with SphinxQL, a SQL dialect used with the Sphinx search engine and it's fork Manticore. It maps most of the functions listed in the SphinxQL reference and is generally faster than the available Sphinx API.
This Query Builder has no dependencies except PHP 7.1 or later, \MySQLi
extension, PDO
, and Sphinx/Manticore.
Missing methods?
SphinxQL evolves very fast.
Most of the new functions are static one liners like SHOW PLUGINS
. We'll avoid trying to keep up with these methods, as they are easy to just call directly ((new SphinxQL($conn))->query($sql)->execute()
). You're free to submit pull requests to support these methods.
If any feature is unreachable through this library, open a new issue or send a pull request.
Code Quality
The majority of the methods in the package have been unit tested.
The only methods that have not been fully tested are the Helpers, which are mostly simple shorthands for SQL strings.
How to Contribute
Pull Requests
- Fork the SphinxQL Query Builder repository
- Create a new branch for each feature or improvement
- Submit a pull request from each branch to the master branch
It is very important to separate new features or improvements into separate feature branches, and to send a pull request for each branch. This allows me to review and pull in new features or improvements individually.
Style Guide
All pull requests must adhere to the PSR-2 standard.
Unit Testing
All pull requests must be accompanied by passing unit tests and complete code coverage. The SphinxQL Query Builder uses
phpunit
for testing.
Installation
This is a Composer package. You can install this package with the following command: composer require foolz/sphinxql-query-builder
Usage
The following examples will omit the namespace.
Drivers
We support the following database connection drivers:
- Foolz\SphinxQL\Drivers\Mysqli\Connection
- Foolz\SphinxQL\Drivers\Pdo\Connection
Connection
-
$conn = new Connection()
Create a new Connection instance to be used with the following methods or SphinxQL class.
-
$conn->setParams($params = array('host' => '127.0.0.1', 'port' => 9306))
Sets the connection parameters used to establish a connection to the server. Supported parameters: 'host', 'port', 'socket', 'options'.
-
$conn->query($query)
Performs the query on the server. Returns a
ResultSet
object containing the query results.
More methods are available in the Connection class, but usually not necessary as these are handled automatically.
SphinxQL
-
new SphinxQL($conn)
Creates a SphinxQL instance used for generating queries.
Bypass Query Escaping
Often, you would need to call and run SQL functions that shouldn't be escaped in the query. You can bypass the query escape by wrapping the query in an \Expression
.
-
SphinxQL::expr($string)
Returns the string without being escaped.
Query Escaping
There are cases when an input must be escaped in the SQL statement. The following functions are used to handle any escaping required for the query.
-
$sq->escape($value)
Returns the escaped value. This is processed with the
\MySQLi::real_escape_string()
function. -
$sq->quoteIdentifier($identifier)
Adds backtick quotes to the identifier. For array elements, use
$sq->quoteIdentifierArray($arr)
. -
$sq->quote($value)
Adds quotes to the value and escapes it. For array elements, use
$sq->quoteArr($arr)
. -
$sq->escapeMatch($value)
Escapes the string to be used in
MATCH
. -
$sq->halfEscapeMatch($value)
Escapes the string to be used in
MATCH
. The following characters are allowed:-
,|
, and"
.Refer to
$sq->match()
for more information.
SELECT
-
$sq = (new SphinxQL($conn))->select($column1, $column2, ...)->from($index1, $index2, ...)
Begins a
SELECT
query statement. If no column is specified, the statement defaults to using*
. Both$column1
and$index1
can be arrays.
INSERT, REPLACE
This will return an INT
with the number of rows affected.
-
$sq = (new SphinxQL($conn))->insert()->into($index)
Begins an
INSERT
. -
$sq = (new SphinxQL($conn))->replace()->into($index)
Begins an
REPLACE
. -
__$sq->set($associative_array)__
Inserts an associative array, with the keys as the columns and values as the value for the respective column.
-
$sq->value($column1, $value1)->value($column2, $value2)->value($column3, $value3)
Sets the value of each column individually.
-
$sq->columns($column1, $column2, $column3)->values($value1, $value2, $value3)->values($value11, $value22, $value33)
Allows the insertion of multiple arrays of values in the specified columns.
Both
$column1
and$index1
can be arrays.
UPDATE
This will return an INT
with the number of rows affected.
-
$sq = (new SphinxQL($conn))->update($index)
Begins an
UPDATE
. -
$sq->value($column1, $value1)->value($column2, $value2)
Updates the selected columns with the respective value.
-
__$sq->set($associative_array)__
Inserts the associative array, where the keys are the columns and the respective values are the column values.
DELETE
Will return an array with an INT
as first member, the number of rows deleted.
-
$sq = (new SphinxQL($conn))->delete()->from($index)->where(...)
Begins a
DELETE
.
WHERE
-
$sq->where($column, $operator, $value)
Standard WHERE, extended to work with Sphinx filters and full-text.
It should be noted that
OR
and parenthesis are not supported and implemented in the SphinxQL dialect yet.
MATCH
-
$sq->match($column, $value, $half = false)
Search in full-text fields. Can be used multiple times in the same query. Column can be an array. Value can be an Expression to bypass escaping (and use your own custom solution).
By default, all inputs are escaped. The usage of
SphinxQL::expr($value)
is required to bypass the default escaping and quoting function.The
$half
argument, if set totrue
, will not escape and allow the usage of the following characters:-
,|
,"
. If you plan to use this feature and expose it to public interfaces, it is recommended that you wrap the query in atry catch
block as the character order maythrow
a query error.
GROUP, WITHIN GROUP, ORDER, OFFSET, LIMIT, OPTION
-
$sq->groupBy($column)
GROUP BY $column
-
$sq->withinGroupOrderBy($column, $direction = null)
WITHIN GROUP ORDER BY $column [$direction]
Direction can be omitted with
null
, or beASC
orDESC
case insensitive. -
$sq->orderBy($column, $direction = null)
ORDER BY $column [$direction]
Direction can be omitted with
null
, or beASC
orDESC
case insensitive. -
$sq->offset($offset)
LIMIT $offset, 9999999999999
Set the offset. Since SphinxQL doesn't support the
OFFSET
keyword,LIMIT
has been set at an extremely high number. -
$sq->limit($limit)
LIMIT $limit
-
$sq->limit($offset, $limit)
LIMIT $offset, $limit
-
$sq->option($name, $value)
OPTION $name = $value
Set a SphinxQL option such as
max_matches
orreverse_scan
for the query.
TRANSACTION
-
(new SphinxQL($conn))->transactionBegin()
Begins a transaction.
-
(new SphinxQL($conn))->transactionCommit()
Commits a transaction.
-
(new SphinxQL($conn))->transactionRollback()
Rollbacks a transaction.
Executing and Compiling
-
$sq->execute()
Compiles, executes, and returns a
ResultSet
object containing the query results. -
$sq->executeBatch()
Compiles, executes, and returns a
MultiResultSet
object containing the multi-query results. -
$sq->compile()
Compiles the query.
-
$sq->getCompiled()
Returns the last query compiled.
-
$sq->getResult()
Returns the
MultiResultSet
object, depending on whether single or multi-query have been executed last.
Multi-Query
-
$sq->enqueue(SphinxQL $next = null)
Queues the query. If a $next is provided, $next is appended and returned, otherwise a new SphinxQL object is returned.
-
$sq->executeBatch()
Returns a
MultiResultSet
object containing the multi-query results.
$result
will contain ResultSet
object containing the results of the next enqueued query.
Query results
ResultSet
Contains the results of the query execution.
-
$result->fetchAllAssoc()
Fetches all result rows as an associative array.
-
$result->fetchAllNum()
Fetches all result rows as a numeric array.
-
$result->fetchAssoc()
Fetch a result row as an associative array.
-
$result->fetchNum()
Fetch a result row as a numeric array.
-
$result->getAffectedRows()
Returns the number of affected rows in the case of a DML query.
MultiResultSet
Contains the results of the multi-query execution.
-
$result->getNext()
Returns a
ResultSet
object containing the results of the next query.
Helper
The Helper
class contains useful methods that don't need "query building".
Remember to ->execute()
to get a result.
-
Helper::pairsToAssoc($result)
Takes the pairs from a SHOW command and returns an associative array key=>value
The following methods return a prepared SphinxQL
object. You can also use ->enqueue($next_object)
:
(new Helper($conn))->showMeta() => 'SHOW META'
(new Helper($conn))->showWarnings() => 'SHOW WARNINGS'
(new Helper($conn))->showStatus() => 'SHOW STATUS'
(new Helper($conn))->showTables() => 'SHOW TABLES'
(new Helper($conn))->showVariables() => 'SHOW VARIABLES'
(new Helper($conn))->setVariable($name, $value, $global = false)
(new Helper($conn))->callSnippets($data, $index, $query, $options = array())
(new Helper($conn))->callKeywords($text, $index, $hits = null)
(new Helper($conn))->describe($index)
(new Helper($conn))->createFunction($udf_name, $returns, $soname)
(new Helper($conn))->dropFunction($udf_name)
(new Helper($conn))->attachIndex($disk_index, $rt_index)
(new Helper($conn))->flushRtIndex($index)
(new Helper($conn))->optimizeIndex($index)
(new Helper($conn))->showIndexStatus($index)
(new Helper($conn))->flushRamchunk($index)
Percolate
The Percolate
class provides methods for the "Percolate query" feature of Manticore Search.
For more information about percolate queries refer the Percolate Query documentation.
INSERT
The Percolate class provide a dedicated helper for inserting queries in a percolate
index.
-
$pq = (new Percolate($conn))->insert($query,$noEscape)
Begins an
INSERT
. A single query is allowed to be added per insert. By default, the query string is escaped. Optional second parameter$noEscape
can be set totrue
for not applying the escape. -
$pq->into($index)
Set the percolate index for insert.
-
$pq->tags($tags)
Set a list of tags per query. Accepts array of strings or string delimited by comma
-
$pq->filter($filter)
Sets an attribute filtering string. The string must look the same as string of an WHERE attribute filters clause -
$pq->execute()
Execute the
INSERT
.
CALLPQ
Searches for stored queries that provide matching for input documents.
-
$pq = (new Percolate($conn))->callPQ()
Begins a
CALL PQ
-
$pq->from($index)
Set percolate index.
-
$pq->documents($docs)
Set the incoming documents. $docs can be:
- a single plain string (requires
Percolate::OPTION_DOCS_JSON
set to 0) - array of plain strings (requires
Percolate::OPTION_DOCS_JSON
set to 0) - a single JSON document
- an array of JSON documents
- a JSON object containing an array of JSON objects
- a single plain string (requires
-
$pq->options($options)
Set options of
CALL PQ
. Refer the Manticore docs for more information about theCALL PQ
parameters.- Percolate::OPTION_DOCS_JSON (
as docs_json
) default to 1 (docs are json objects). Needs to be set to 0 for plain string documents. Documents added as associative arrays will be converted to JSON when sending the query to Manticore. - __Percolate::OPTION_VERBOSE__ (
as verbose
) more information is printed by followingSHOW META
, default is 0 - __Percolate::OPTION_QUERY__ (
as query
) returns all stored queries fields , default is 0 - __Percolate::OPTION_DOCS__ (
as docs
) provide result set as per document matched (instead of per query), default is 0
- Percolate::OPTION_DOCS_JSON (
-
$pq->execute()
Execute the
CALL PQ
.
Laravel
Laravel's dependency injection and realtime facades brings more convenience to SphinxQL Query Builder usage.
Facade access also works with Helper
and Percolate
.