Queries

Queries usage

This page shows the working and usage of query system.

Overview

Query system is split into 3 parts or process, for handle creation and execution :

First, it starts with a query builder, implementing CommandInterface, providing methods for creating and configuring the query. This object is the public API for querying database. The query can be created by calling ConnectionInterface::make().
Then, juste before execution, a compiler is called. The compiler can be any object, but some standard interfaces and classes are available like CompilerInterface. The compiler will take as parameter the query, and will compile the query in the platform grammar (see: ConnectionInterface::platform()->grammar()).
Now the query was compiled into a value handle by the database platform : it can be executed, and the result is returned as ResultSetInterface.

Query object may cache compiled statement, and reuse it on future executions.

Query objects may implement some interfaces to standardise behaviors :

ClauseInterface Base type for query builders.
CompilableClauseInterface Add some metadata accessors on ClauseInterface used by compiler. Note: this type do not extends Compilable.
Compilable Base type for query objects that can be compiled to native statement. Those queries can be executed using ConnectionInterface::execute().
SelfExecutable Provide method execute() on query.
CommandInterface Base type for any database query builders (i.e. common type for both read and write queries). Extends CompilableClauseInterface and SelfExecutable.
ReadCommandInterface Base type for queries executing read operations. Provide methods which process ResultSetInterface values.
QueryInterface Generic query builder type. Handles both read and write operations.
SqlQueryInterface Generic query builder type for SQL databases.

ResultSetInterface

All executed queries (using SelfExecutable::execute() or ConnectionInterface::execute()) will returns an instance of ResultSetInterface. This object wrap both read and write results, to provides a more convenient and standardised way for handling database results.

Note: This object should not be reused once iterated.

// Execute a query to get a result set
$result = $connection->from('person')->execute();

// You can check type of response
$result->isRead(); // true
$result->isWrite(); // false

$result->count(); // Get number of returned results
count($result); // It implements Countable, so count() can also be used

// This object is iterable
foreach ($result as $row) {
    // By default, rows are fetch as associative array
    $row['first_name'];
    // ...
}

// You can perform a "fetchAll" instead of use it as iterator
$rows = $result->all();

// You can change fetch type using asXXX methods
foreach ($result->asObject() as $row) {
    // $row is an stdClass
    $row->first_name;
}
foreach ($result->asList() as $row) {
    // $row is a list (indexed array)
    $row[1];
}
foreach ($result->asColumn(1) as $row) {
    // $row is a single value. This is equivalent of calling asList() and using $row[$index] on each rows
}
foreach ($result->asClass(Person::class) as $row) {
    // $row is an instance of Person. Fill properties regardless of visibility.
}


// Execute a write query
$result = $connection->execute($connection->from('person')->where(['id' => 42])->values(['first_name' => 'Alan']));

$result->isRead(); // false
$result->isWrite(); // true

$result->hasWrite(); // true if at least one row has been modified by the query

// You can get number of affected rows using count
$result->count();
count($result);

Query

Query is the generic query builder object for SQL databases. It implements SqlQueryInterface, and provide APIs for perform read and write operations.

Filters

Filters can be applied by adding WHERE or HAVING clauses to the query by using where or having methods and their derivatives.

Those methods are :

where : Add an AND filter on WHERE clause
orWhere : Add an OR filter on WHERE clause
whereNull : Add an AND xxx IS NULL filter on WHERE clause
whereNotNull : Add an AND xxx IS NOT NULL filter on WHERE clause
orWhereNull : Add an OR xxx IS NULL filter on WHERE clause
orWhereNotNull : Add an OR xxx IS NOT NULL filter on WHERE clause
whereRaw : Add an AND SQL expression on WHERE clause
orWhereRaw : Add an OR SQL expression on WHERE clause
having, orHaving, havingNull, orHavingNull, havingNotNull, orHavingNotNull, havingRaw, orHavingRaw : equivalent for HAVING clause

Base methods (where, orWhere, having, orHaving) can be used with different parameters (following examples use where, but can be replaced with other methods) :

where(string $fieldName, mixed $fieldValue) : create equals filter. ex: where('foo', 42) -> WHERE foo = 42
where(string $fieldName, string $operator, mixed $fieldValue) : create filter with explicit operator. ex: where('foo', '>=', 42) -> WHERE foo >= 42
where(array<string, mixed> $filters) : add multiple filters. Array key is the field optionally with an operator. Array value is the comparison value. ex: where(['foo' => 42, 'bar >=' => 7]) -> WHERE foo = 42 AND bar >= 7
where(callable(QueryInterface $queryBuilder)) : add a nested filter expression (i.e. filters wrapped into parenthesis). Closure passed as parameter will takes current query instance to build filters. ex: where(function (QueryInterface $query) { $query->where('foo', 42)->orWhere('bar', 42); }) -> WHERE (foo = 42 OR bar = 42)

Note: You can pass an array into $fieldValue parameter to perform an "OR" filter on each array values. So equal operator will be converted to IN, not equals to NOT IN, like to "xxx LIKE xxx OR xxx LIKE xxx"...

This table shows all available values for parameter $operator :

Operator	Description	Parameter format	SQL operator
`<`, `:lt`	Lower than comparison	Any scalar value	`<`
`<=`, `:lte`	Lower than or equals comparison	Any scalar value	`<=`
`>`, `:gt`	Greater than comparison	Any scalar value	`>`
`>=`, `:gte`	Greater than or equals comparison	Any scalar value	`>=`
`~=`, `=~`, `:regex`	Regex match	Regex string or array of regex to perform a "IN regex", where all match are combined using `OR` operator.	`REGEXP`
`:like`	Simple string pattern match, using `_` as single character placeholder, and `%` as multiple characters wildcard.	Pattern string, or array of patterns to perform a "IN LIKE", where all match are combined using `OR` operator.	`LIKE`
`:notlike`, `!like`	Reverse of `:like` operator.	Pattern string, or array of patterns to perform a "NOT IN LIKE", where all match are combined using `AND` operator.	`NOT LIKE`
`in`, `:in`	Check if the field value is contained into the given values.	Array of values.	`IN` or `IS NULL` if an empty value is passed as parameter
`notin`, `:notin`, `!in`	Check if the field value is not contained into the given values.	Array of values.	`NOT IN` or `IS NOT NULL` if an empty value is passed as parameter
`between`, `:between`	Check if the field value is contained into the given interval.	List of two values. First value is the "min" boundary, and the second the "max" boundary.	`BETWEEN [min] AND [max]`
`!between`, `:notbetween`	Check if the field value is outside the given interval.	List of two values. First value is the "min" boundary, and the second the "max" boundary.	`NOT(BETWEEN [min] AND [max])`
`<>`, `!=`, `:ne`, `:not`	Check if the field value is different than given value.	Single or array of values.	`NOT IN` if an array is passed, `IS NOT NULL` if null is passed, `!=` in other cases
`=`, `:eq`	Check if the field value is equals to given value.	Single or array of values.	`IN` if an array is passed, `IS NULL` if null is passed, `=` in other cases

The parameter $fieldValue (or item value when use an array as filter) can be a complex expression instead of a simple comparison value :

An ExpressionInterface which is compiled into raw SQL expression. ex: where('published_at', '<=', new Now()) -> WHERE published_at <= CURRENT_DATE
An ExpressionTransformerInterface which provides column, operator and value. Unlike ExpressionInterface, operator is parsed by compiler, and value is bind instead of a raw concatenation. ex: where('name', (new Like('foo'))->startsWith() -> WHERE name LIKE 'foo%'
A QueryInterface to use sub-query as value. ex: where('name', 'in', Post::builder()->select('author')->where('published_at', '>=', new DateTime('2020-10-05'))) -> WHERE name IN (SELECT author FROM posts WHERE published_at >= '2020-10-05 00:00:00')

Available implementations of ExpressionInterface :

Attribute : Compare with a database field. Preprocessor is called to resolve actual column name on ORM query.
Aggregate : Aggregation functions (i.e. MIN(column), AVG(column)...). Preprocessor is called to resolve actual column name on ORM query.
Field : Use MySQL FIELD function. Preprocessor is called to resolve actual column name on ORM query.
Now : Expression for get current SQL server date time.
Raw : Wrap SQL string expression.
FullTextMatch : Perform a fulltext search on a MyISAM table. Preprocessor is called to resolve actual column name on ORM query.

Available implements of ExpressionTransformerInterface :

Like : LIKE expression builder.
Value : Force conversion of the value according the field type. Useful for search raw array value, and ensure that the operator will not be converted to IN.
RawValue : Skip type transformation of the value.

User::builder()->where('login', 'bar'); // SELECT * FROM users WHERE login = 'bar'
User::builder()->where('login', 'bar')->where('activated', true); // SELECT * FROM users WHERE login = 'bar' AND activated = 1
User::builder()->where(['login' => 'bar', 'activated' => true]); // Same as above
User::builder()->where('login', 'bar')->orWhere('login', 'baz'); // SELECT * FROM users WHERE login = 'bar' OR login = 'baz'
User::builder()->where(['login :like' => '%b%', 'activated' => true]); // SELECT * FROM users WHERE login LIKE '%b%' AND activated = 1

// SELECT * FROM users WHERE activated = 1 OR (login LIKE '%a%' AND name LIKE '%b%')
User::builder()->where('activated', true)->orWhere(function ($query) {
    $query
        ->where('login', ':like', '%a%')
        ->where('name', ':like', '%b%')
    ;
});

// SELECT country, COUNT(id) AS usersCount FROM users GROUP BY country HAVING usersCount >= 100
User::builder()
    ->select([
        'country',
        'usersCount' => Aggregate::count('id')
    ])
    ->group('country')
    ->having('usersCount', '>=', 100)
;

// SELECT * FROM users WHERE name IN(SELECT name FROM groups)
User::builder()->where('name', 'in', Group::builder()->select('name'));

Grouping

Rows can be grouped for perform aggregation or remove duplicates, by calling :

SqlQueryInterface::group() : Add a GROUP BY clauses on given columns. Replace previous groups if already defined.
SqlQueryInterface::addGroup() : Add new columns to GROUP BY clause.
SqlQueryInterface::distinct() : Add a DISTINCT flag on SELECT clause to remove duplicates rows.

// SELECT DISTINCT name, country FROM users
User::builder()->select(['name', 'country'])->distinct();

// SELECT * FROM users GROUP BY name, country
User::builder()->group('name', 'country');

Sorting

You can define the order of result entities by using methods of Orderable :

Orderable::order() : Define columns used for order the result. Replaces any previously specified orderings.
Orderable::addOrder() : Add new columns ORDER BY clause.
Orderable::getOrders() : Get all defined orders, which column as key and sort order as value.

By default, ascendant order is used. To define a custom order, you can pass as second parameter one of the constants Orderable::ORDER_*, or define the order as array value.

// SELECT * FROM users ORDER BY name, created_at
User::builder()->order(['name', 'createdAt']);

// SELECT * FROM users ORDER BY name DESC, created_at ASC
User::builder()->order(['name' => Orderable::ORDER_DESC, 'createdAt']);

Projection and query execution result

Data returned by the query can be filtered using methods of Projectionable :

project or select : Define columns or expressions to be returned by the query execution. If already defined, this method will replace previous defined columns or expressions.
addSelect : Add columns or expressions to SELECT clause.

All projections methods takes as parameter an array of column names or expressions. Array wrap is optional when only a single column or expression is used. When an array if used, an alias (i.e. AS keyword) can be defined by using a string key. The select expression can be an instance of ExpressionInterface, or QueryInterface. In case of a query, it must return a single result to be valid.

Once projection is defined, you can configure entity transformation process by calling ReadCommandInterface::post(). By default, when called from ORM context, the post process if configured to create and fill related entity object.

This method takes as first parameter the transformation closure. If the second parameter is true, the transformation closure will be applied on each row. If false, the transformation closure will takes as parameter the ResultSetInterface instance :

ReadCommandInterface::post(Closure(array $row):(object|array) $transformer) : Transformation closure is applied on each row.
ReadCommandInterface::post(Closure(ResultSetInterface $results):array $transformer, false) : Transformation closure is applied on all rows.

Once transformation process configured, you can execute query and get result by calling ReadCommandInterface::all() to get an array of entities or ReadCommandInterface::first() to get only the first result entity.

You can also configure query to return a CollectionInterface instance instead of array when calling ReadCommandInterface::all(), by calling method ReadCommandInterface::wrapAs() with wrapper class name as parameter.

You can also ignore transformation process by calling directly SelfExecutable::execute() which returns a ResultSetInterface object. Two execution helper methods are also present for selecting a single column value by calling :

ReadCommandInterface::inRow(string $column) : Get the value of the given column. This is equivalent of calling ->select($column)->execute()->asColumn()->next()
ReadCommandInterface::inRows(string $column) : Get all values of the given column. This is equivalent of calling ->select($column)->execute()->asColumn()->all()

// SELECT name FROM users
// An array of User instance will be returned with only "name" property filled
User::select('name')->all();

// SELECT name FROM users LIMIT 1
// Get a single User instance with only "name" property filled
User::select('name')->first();

// SELECT id, name FROM users LIMIT 1
// Same as above but with id and name properties filled
User::select(['id', 'name'])->first();

// Get raw database result as associative array
User::select(['id', 'name'])->execute()->all();

// Transform row as string
// So an array of string will be returned
User::select(['id', 'name'])->post(fn (array $row) => "{$row['name']} ({$row['id']})")->all();

// Custom post process on result set
// In this case "id" values will be indexed by corresponding "name" value
User::select(['id', 'name'])->post(function (ResultSetInterface $rs) {
    $values = [];
    
    foreach ($rs as $row) {
        $values[$row['name']][] = $row['id'];
    }

    return $values;
}, false)->all();

// Entities will be wrapped into an EntityCollection instance
User::wrapAs(EntityCollection::class)->all();

// Get the first "name" column value
User::inRow('name');

// Get "name" values
User::inRows('name');

// SELECT (SELECT COUNT(*) FROM users) as count, id FROM users WHERE id >= count
User::select([
        'count' => User::select(Aggregate::count('*')),
        'id'
    ])
    ->where('id', '>=', new Attribute('count'))
    ->execute()->all()
;

Join table and entity

To perform join operations, Joinable methods can be used :

join() to perform an INNER JOIN
leftJoin() to perform a LEFT JOIN
rightJoin() to perform a RIGHT JOIN

Those methods have two usages :

join(string|array|QueryInterface $table, string $localKey, string $operator, mixed|ExpressionInterface $distantKey) : Create a simple clause JOIN [table] ON [localKey] [operator] [distanceKey].
join(string|array|QueryInterface $table, Closure(JoinClause):void $configurator) : Use a builder to configure the ON clause.

The $table parameter can be :

The table name as string
An array with first value as table name and second as alias
A QueryInterface to use the given query as table. An array can be used to define an alias.

The $localKey parameter should be the first join column as string. The $operator parameter is the comparison operator. Use same operators as filters. The $distantKey parameter is the compared value. Use new Attribute() to perform a join on a column.

When use a closure as second parameter, an instance of JoinClause will be passed for configure ON constraints. Used syntax is same as filters.

Example:

// SELECT * FROM users INNER JOIN groups ON admin = id WHERE groups.name = 'foo'
User::builder()
    ->join('groups', 'admin', '=', new Attribute('id'))
    ->where('groups.name', 'foo')
;

// Using alias:
// SELECT * FROM users INNER JOIN groups AS g ON g.admin = id WHERE g.name = 'foo'
User::builder()
    ->join(['groups', 'g'], 'g.admin', '=', new Attribute('id'))
    ->where('g.name', 'foo')
;

// Using closure for configure join on multiple keys:
// SELECT * FROM user_options t0 INNER JOIN extra ON extra.userId = t0.userId AND extra.name = t0.name
UserOption::builder()
    ->join('extra', function (\Bdf\Prime\Query\JoinClause $join) {
        $join
            ->on('extra.userId', '=', new Attribute('t0.userId'))
            ->on('extra.name', '=', new Attribute('t0.name'))
        ;
    })
;

// Use embedded select query:
// SELECT * FROM users INNER JOIN (SELECT * FROM user_options WHERE value LIKE '{"%":"%"}') o ON userId = o.userId
User::builder()->join([UserOption::where('value', ':like', '{"%":"%"}'), 'o'], 'userId', '=', new Attribute('o.userId'));

When use query builder in ORM context, entity class name can be used instead of table name, to let compiler resolve table and column name. The interface EntityJoinable should be used in replacement of Joinable. It works like Joinable APIs, but takes entity class name instead of table name, and join alias is mandatory and handled by the query builder.

Example:

// SELECT * FROM users INNER JOIN groups AS g ON g.admin = id WHERE g.name = 'foo'
User::builder()
    ->entityJoin(Group::class, 'admin', '=', 'id', 'g')
    ->where('g.name', 'foo')
;

Pagination and walker

When you have to handle high number of entities, it's advisable to cut results into chunks of limited number of entities.

This operation can be performed by methods of Limitable :

limit(?int $limit, ?int $offset) : Define the LIMIT clause. Number of returned and skipped rows can be configured.
offset(?int $offset) : Define only number of skipped rows, without change limit.
limitPage(int $page, int $rowCount) : More convenient way of handling pagination / chunks by using page number instead of offset. $page parameter starts at 1. This is equivalent of calling : limit($rowCount, ($page - 1) * $rowCount).

It also provides some getters or helper methods :

getLimit() : Get the configured limit
getOffset() : Get the configured number of skipped rows
getPage() : Get the current page using configured limit and offset
isLimitQuery() : Check if at least one of the limit or offset values are defined
hasPagination() : Check if both limit and offset are defined

So, by using this interface you can perform a simple, and low level iteration by chunks of rows :

$query = User::where(...)->limit(100);

// Execute query (should return at most 100 rows)
while (($users = $query->all())) {
    // Iterate on the chunk of entities
    foreach ($users as $user) {
        // ...
    }
    
    // Move cursor to next chunk
    $query->offset($query->getOffset() + $query->getLimit());
}

Prime also provide a higher level API for handle pagination, using Paginable, and PaginatorInterface. So, you can call Paginable::paginate(int $maxRows, int $page) to execute query with given limit and automatically wrap result into a PaginatorInterface object.

This object implements CollectionInterface, but also provides some utility methods :

size() to get the total count of available entities for the current query (not the current chunk size !)
limit() or pageMaxRows() to get the configured limit
offset() to get the configured number of skipped rows
page() to get the current page number (starts at 1)

Note: calling count($paginator) or $paginator->count() will return the size of the current chunk and not the total number of rows on database.

If you simply want to iterate over rows, you can use Query (or any implementation of Paginable) as iterator. By default, rows will be loaded by chunks of 150 rows.

You can configure this behavior by calling Paginable::walk(int $maxRows, int $page). The returned paginator will be an instance of Walker. Unlike base paginator, the walker will load following entities once iterator reach end of the current chunk. Walker provide two iteration strategy :

PaginationWalkStrategy which use internally Limitable API to create chunks. This strategy works on all queries, but have two drawback :
- Its performance go worse when reach high offset number. So this strategy should not be used to iterate over millions of entities.
- Entities can be skipped when deletion are performed during iteration.
KeyWalkStrategy which use operator > or < on a key with unique value.
- Unlike previous strategy, if the key is indexed, performance remain high whatever cursor position or number of rows.
- Delete operations can be performed during iteration.
- But a unique key must be present (ideally use the primary key).

To change the strategy, simple call Walker::setStrategy().

Note: strategy can only be defined on a new walker. Once iteration is started, strategy cannot be changed anymore.

// Get 20 users, on page 3 (offset = 40)
$paginator = User::builder()->paginate(20, 3);

// Display users
echo json_encode([
    'users' => $paginator->map($serializer->formatUser(...))->all(),
    'per_page' => $paginator->limit(),
    'page' => $paginator->page(),
    'total' => $paginator->size(),
]);

// Walk users, by chunk of 150 entities
foreach (User::builder()->where('name', ':like', '%a%') as $user) {
    // ...
}

// Walk users, by chunk of 500 entities
foreach (User::builder()->where('name', ':like', '%a%')->walk(500) as $user) {
    // ...
}

// Create the key definition
class SimpleKey implements KeyInterface
{
    private string $key;
    
    public function __construct(string $key) 
    {
        $this->key = $key;
    }
    
    public function name(): string
    {
        return $this->key;
    }

    public function get($entity)
    {
        return $entity->{$this->key}();
    }
}

// Walk ordered by login and use optimised KeyWalkStrategy
// login is unique, so it can be used as cursor
foreach (User::builder()->order('login')->walk(500)->setStrategy(new KeyWalkStrategy(new SimpleKey('login'))) as $user) {
    if (xxx) {
        // KeyWalkStrategy allows to delete entities during iteration
        $user->delete();
    }
}

Aggregation

Some aggregation function can be call using methods of Aggregatable. Query will be executed directly and return the aggregation value. All clause or projection defined for execution the aggregation will be reset once executed. So it's safe to reuse a query after execution of an aggregate function.

Methods:

count() Count number of rows matching with current query. Note: if a limit is defined, the count will takes this clause in account, and count will also be limited.
avg() Get the average value of the given column.
sum() Get the result of adding all values of the given column.
min() Get the minimal value of the given column. Unlike previous aggregates, a string can be returned.
max() Get the maximal value of the given column. Unlike previous aggregates, a string can be returned.
aggregate() Execute a custom aggregation function.

$users = User::builder()->where('name', ':like ', '%a%');

// call an aggregate, and then the base query
$count = $users->count();
$user = $user->first();

echo 'Match with ' . $this->printUser($user) . ' and ' . $count . ' users';

// Get the first creation date 
// Note: type system is not used here, so a string will be returned instead of a DateTime object
User::builder()->min('createdAt');

Write operations

Query can also perform write operations :

Delete entities by filtering and then call Deletable::delete().
Update entities by filtering, define new column values using QueryInterface::set() and then call QueryInterface::update().
Insert entities by calling QueryInterface::insert() with an associative array for define column values.

// DELETE FROM users WHERE login = 'robert@example.com'
User::builder()
    ->where('login', 'robert@example.com')
    ->delete()
;

// UPDATE users SET password = 'new_password' WHERE login = 'robert@example.com'
User::builder()
    ->where('login', 'robert@example.com')
    ->set('password', 'new_password')
    ->update()
;

// INSERT INTO users (login, password, roles) VALUES ('robert@example.com', 'my_password', ',4,8,')
User::builder()->insert([
    'login' => 'robert@example.com',
    'password' => 'my_password',
    'roles' => [4, 8],
]);

Debugging methods

You can check the result of compilation of the query by using those methods :

toSql() Get the executed SQL. Note: if bindings are present (ex: if where clause is used), a placeholder (? in MySQL) will be used instead of actual value.
toRawSql() Get the SQL but placeholder replaced by actual bound value.
getBindings() Get bound values.

$query = User::builder()->where('login', 'bob')->where('enabled', true);

$query->toSql(); // SELECT * FROM users WHERE login = ? AND enabled = ?
$query->getBindings(); // ['bob', 1]
$query->toRawSql(); // SELECT * FROM users WHERE login = 'bob' AND enabled = 1

QueryRepositoryExtension

The QueryRepositoryExtension class is registered on all read queries on the ORM layer. It's used as extension of the query, declared by the repository using ReadCommandInterface::setExtension().

Provided methods :

get(array|mixed $key, ?array $columns = null) : Execute a "find by primary" request. Previous configured filters will also be applied.
- The first parameter is the primary key value. Can be an array of composed key.
- The second (optional) parameter is columns to return (i.e. SELECT clause).
- It returns the entity, or null if not exists.
getOrFail(array|mixed $key, ?array $columns = null) : Same as above, but throws an EntityNotFoundException if the entity is not found instead of returns null.
getOrNew(array|mixed $key, ?array $columns = null) : Same as above, but returns an empty entity instance if the entity is not found. Note: the returned instance will be completely empty, primary key or any other filters will not be filled automatically !
with(string|array $relations) : Define relations to load on each result entity. See Loading relations for the format. This is the recommended way for loading relations because it fixes N+1 issue.
without(string|array $relations) : Ignore loading of eager relations. See Loading relations for more information.
by(string $field, bool $combine = false) : Indexing returned entities by given field. If true is passed as second parameter, entities with same field value will be stacked into a list instead of be replaced.

// Get the user with id 42, which is in group 5
User::builder()->where('groups.id', 5)->get(42);

// Same as above, but throw an exception if not found
User::builder()->where('groups.id', 5)->getOrFail(42);

// Index users by their login
$users = User::builder()->where('groups.id', 5)->by('login')->all();

// Index users by their name, but stack duplicated names.
// Also loads relation groups on each user.
// The type of $users is : `array<string, list<User>>`.
$users = User::with('groups.admin')->by('name', true)->all();

KeyValueQuery

KeyValueQuery is a query type that allows only "AND equals" filters. This simplification permit to optimise query compilation, and can reuse a prepared statement to improve performance.

Filters are set by calling where(string $column, mixed $value). Unlike Query, $value cannot be null or an ExpressionInterface, and operator cannot be defined. Also, orWhere(), whereNull(), etc... are not available.

This query implements Limitable and Paginable, so pagination is available, but KeyWalkStrategy is not working, due to lack of operator. So on large table, Paginable::walk() is not optimised on this implementation.

// SELECT * FROM users WHERE login = 'foo'
$query = User::repository()->make(KeyValueQuery::class)->where('login', 'foo');
$query->first();

// Execute same query as above (prepared statement is kept), only change bound value
$query->where('login', 'bar')->first();

// SELECT * FROM users WHERE login = 'foo' AND enabled = 1
$query->where('enabled', true)->first();

// Aggregation functions are also supported
$query->count();

This query can also be used to perform update or delete operations :

$query = User::repository()->make(KeyValueQuery::class)->where('login', 'foo');

// DELETE FROM users WHERE login = 'foo'
$query->delete();

// UPDATE users SET password = 'new_password', 'updated_at' = '2022-11-22 10:45:21' WHERE login = 'foo'
$query->values(['password' => 'new_password', 'updatedAt' => new DateTime()])->update();

BulkInsertQuery

BulkInsertQuery is a query dedicated to INSERT operations. Like KeyValueQuery, prepared statement is kept to be reused, to optimise insertion of multiple rows.

This query works in two mode : simple insert and bulk insert. Bulk mode allows to insert multiple entities in one query. This mode is enabled by calling BulkInsertQuery::bulk(). Once enabled, all calls of BulkInsertQuery::values() will be stacked.

Insert columns can be explicitly defined by calling BulkInsertQuery::columns() which takes array of columns names, or an associative array with the column name as key and the column type as value.

$query = User::repository()->make(BulkInsertQuery::class)->columns(['login', 'password', 'roles', 'name']);

// Execute INSERT INTO users (login, password, roles, name) VALUES ('bob', 'secret', ',4,7,', 'Robert')
$query->values(['login' => 'bob', 'password' => 'secret', 'roles' => [4, 7], 'name' => 'Robert'])->execute();

// Reuse prepared query
// Note: 'password' field is missing, so `NULL` will be passed
$query->values(['login' => 'mike', 'roles' => [3], 'name' => 'James'])->execute();

// Enable bulk mode
$query->bulk();
$count = 0;

foreach ($rows as $row) {
    // Insert by chunk of 100 rows
    if ($count >= 100) {
        $count = 0;
        $query->execute();
        // Set true as second parameter (replace) to reset values
        $query->values($row, true);
    } else {
        ++$count;
        // Stack row to insert
        $query->values($row);
    }
}

// Commit remaining rows
if ($count) {
    $query->execute();
}

Navigation Menu

Uh oh!

Queries

Queries usage

Overview

ResultSetInterface

Query

Filters

Grouping

Sorting

Projection and query execution result

Join table and entity

Pagination and walker

Aggregation

Write operations

Debugging methods

QueryRepositoryExtension

KeyValueQuery

BulkInsertQuery

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally