hectororm/query

Hector Query

v1.0.0-beta6 2022-09-05 09:22 UTC

This package is auto-updated.

Last update: 2023-01-05 10:13:23 UTC


README

Latest Version Software license Build Status Quality Grade Total Downloads

Hector Query is the query module of Hector ORM. Can be used independently of ORM.

Installation

Composer

You can install Hector Query with Composer, it's the recommended installation.

$ composer require hectororm/query

Dependencies

  • PHP ^8.0
  • Packages dependencies:
    • hectororm/connection

Usage

QueryBuilder

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();

Select / Insert / Update / Delete / Union

You can do a select/insert/update/delete request with specific objects:

  • Select : Hector\Query\Select class
  • Insert : Hector\Query\Insert class
  • Update : Hector\Query\Update class
  • Delete : Hector\Query\Delete class
  • Union : Hector\Query\Union class
use 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);

Conditions

Hector Query has support of having and where conditions. Methods are sames, just replace "where" by "having" in method name.

Where / Having

/** @var QueryBuilder $queryBuilder */
use Hector\Query\QueryBuilder;

$queryBuilder
    ->from('table', 'alias')
    ->where('field', '=', 'value')
    ->orWhere('field', '=', 'value2');

Shortcuts

  • 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)

Columns

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)

Group

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)

Order

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()

Limit

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().

Assignments

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)

Jointures

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().

Union

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.

Fetch results

3 methods to fetch result:

  • QueryBuilder::fetchOne(): ?array Get first row of statement results.
  • QueryBuilder::fetchAll(): Generator Get all rows of statement results, uses Generator class.
  • QueryBuilder::fetchColumn(int $column = 0): Generator Get specified column value of all rows of statement results, uses Generator class.

To known how use Generator, refers to the PHP documentation: https://www.php.net/manual/class.generator.php

Count results

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.

Exists

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.

Insert / Update / Delete

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.