Hector Query is the query module of Hector ORM. Can be used independently of ORM.
MIT License
Hector Query is the query module of Hector ORM. Can be used independently of ORM.
You can install Hector Query with Composer, it's the recommended installation.
$ composer require hectororm/query
You can initialize the query builder with a Connection
object.
use Hector\Connection\Connection;
use Hector\Query\QueryBuilder;
$connection = new Connection('...');
$queryBuilder = new QueryBuilder($connection);
$result = $queryBuilder
->select('table')
->where('field1', 'foo')
->where('field2', '>=', 2)
->fetchAll();
You can do a select/insert/update/delete request with specific objects:
Hector\Query\Select
classHector\Query\Insert
classHector\Query\Update
classHector\Query\Delete
classHector\Query\Union
classuse Hector\Query\Select;
use Hector\Query\Insert;
use Hector\Query\Update;
use Hector\Query\Delete;
use Hector\Query\Union;
$select = new Select();
$insert = new Insert();
$update = new Update();
$delete = new Delete();
$union = new Union();
All this classes implements StatementInterface
interface. This interface provides one method to get statement and bindings:
StatementInterface::getStatement(BindParamList $bindParams)
Example of use:
use Hector\Connection\Connection;
use Hector\Connection\Bind\BindParamList;
use Hector\Query\Select;
$connection = new Connection('...');
$select = new Select();
$select
->from('table')
->where('field', 'value');
$binds = new BindParamList();
$statement = $select->getStatement($binds);
$result = $connection->fetchAll($statement, $binds);
Hector Query
has support of having and where conditions. Methods are sames, just replace "where" by "having" in method
name.
/** @var QueryBuilder $queryBuilder */
use Hector\Query\QueryBuilder;
$queryBuilder
->from('table', 'alias')
->where('field', '=', 'value')
->orWhere('field', '=', 'value2');
QueryBuilder::whereIn($column, array $values)
QueryBuilder::whereNotIn($column, array $values)
QueryBuilder::whereBetween($column, $value1, $value2)
QueryBuilder::whereNotBetween($column, $value1, $value2)
QueryBuilder::whereGreaterThan($column, $value)
QueryBuilder::whereGreaterThanOrEqual($column, $value)
QueryBuilder::whereLessThan($column, $value)
QueryBuilder::whereLessThanOrEqual($column, $value)
QueryBuilder::whereExists($statement)
QueryBuilder::whereNotExists($statement)
QueryBuilder::whereContains($string)
QueryBuilder::whereStartsWith($string)
QueryBuilder::whereEndsWith($string)
You can specify columns name and alias with method:
QueryBuilder::column($column, $alias)
Repeat call of this method, add a new column to the result rows ; you can reset columns with method QueryBuilder::resetColumns()
.
Or pass an array of column names:
QueryBuilder::columns(array $columnNames)
You can group results with method:
QueryBuilder::groupBy($column)
Repeat call of this method, add a new group ; you can reset groups with method QueryBuilder::resetGroups()
.
If you want set WITH ROLLUP
modifier to your statement, you can do it with method:
QueryBuilder::groupByWithRollup(bool $withRollup = true)
You can order results with method:
QueryBuilder::orderBy($column, $order)
Repeat call of this method, add a new order ; you can reset orders with method QueryBuilder::resetOrder()
.
A shortcut is available if you want to do a random order:
QueryBuilder::random()
You can limit results with methods:
QueryBuilder::limit(int $limit, int $offset = null)
QueryBuilder::offset(int $offset)
If you want reset limits, uses method QueryBuilder::resetLimit()
.
For Insert/Update statements, you need to assign values with method :
QueryBuilder::assign($column, $value)
Repeat call of this method, add a new assignment to the statement ; you can reset assignments with method QueryBuilder::resetAssignments()
.
Or pass an associative array with column names and values:
QueryBuilder::assigns(array|StatementInterface $columnValues)
Three methods are available to do jointures:
QueryBuilder::innerJoin($table, $condition, ?string $alias = null)
QueryBuilder::leftJoin($table, $condition, ?string $alias = null)
QueryBuilder::rightJoin($table, $condition, ?string $alias = null)
If you want reset jointures, uses method QueryBuilder::resetJoin()
.
An Union
class is available to make unions with select.
use Hector\Connection\Connection;
use Hector\Query\Select;
use Hector\Query\Union;
$connection = new Connection('...');
$union = new Union();
/** @var Select $select1 */
/** @var Select $select2 */
$union->addSelect($select1, $select2);
Union
class is a StatementInterface
, so refers to the related paragraph to use it.
3 methods to fetch result:
QueryBuilder::fetchOne(): ?array
QueryBuilder::fetchAll(): Generator
Generator
class.QueryBuilder::fetchColumn(int $column = 0): Generator
Generator
class.To known how use Generator, refers to the PHP documentation: https://www.php.net/manual/class.generator.php
A shortcut method is available in QueryBuilder
class to count results.
/** @var QueryBuilder $queryBuilder */
use Hector\Query\QueryBuilder;
$queryBuilder
->from('table', 'alias')
->where('field', '=', 'value')
->orWhere('field', '=', 'value2');
$count = $queryBuilder->count();
$results = $queryBuilder->fetchAll();
This method reset columns, limit and order of query ; but don't modify the query builder, so you can continue to use it to get results for example.
A shortcut method is available in QueryBuilder
class to do an exists query.
/** @var QueryBuilder $queryBuilder */
use Hector\Query\QueryBuilder;
$queryBuilder
->from('table', 'alias')
->where('field', '=', 'value')
->orWhere('field', '=', 'value2');
$exists = $queryBuilder->exists();
This method don't modify the query builder, so you can continue to use it to get results for example.
Shortcut methods are available in QueryBuilder
class to do an insert, an update or a delete.
/** @var QueryBuilder $queryBuilder */
use Hector\Query\QueryBuilder;
use Hector\Query\Select;
$queryBuilder
->from('table', 'alias')
->where('field', '=', 'value')
->orWhere('field', '=', 'value2');
$affectedRows = $queryBuilder->insert(['field' => 'value', 'field2' => 'value2']);
$affectedRows = $queryBuilder->insert((new Select())->from('table_src'));
$affectedRows = $queryBuilder->update(['field' => 'value']);
$affectedRows = $queryBuilder->delete();
These methods don't modify the query builder, so you can continue to use it to get results for example.