ORM query expression builders - markstory/cakephp GitHub Wiki

With namespaced functions, I think we are at a place where we could offer a more succinct and expressive Database API & ORM. We can take code like:

$hour = $query->func()->date_format([
    'Activities.created' => 'identifier',
    "'%Y-%m-%d %H:%i'" => 'literal',
]);
$query->select([
 'total' => $query->func()->count('DISTINCT process_id')
 'hour' => $hour
]);

And convert it into

use function Cake\Database\Query\countExpr;
use function Cake\Database\Query\dateFormat;
use function Cake\Database\Query\identifier;
use function Cake\Database\Query\literal;

$query->select([
    'total' => countExpr('DISTINCT process_id'),
    'hour' => dateFormat(identifier('Activities.created'), literal("'%Y-%m-%d %H:%i'")),
]);

These functions would be 'bound' to the query as part of the QueryFunctionBuilder being added to the Query. The QueryFunctionBuilder would have an interface like:

interface QueryFunctionBuilder() {
    // Build the required state for the parameters of this function builder.
    // This could be primitives or more FunctionBuilder instances.
    // Each builder would be able to define its own types.
    public function __construct(...$args) {
    }

    // Called by Database\Query when a function builder is added to the query.
    public function build(Cake\Database\Query $query): Cake\Database\Query
    {
        // Use the existing imperative pattern to modify the query.
    }
}

This interface also gives us an extensible interface that application developers can use to build their own typehinted DSL to build queries.

Functions to include

Basic builders:

• identifier($column) creates an IdentifierExpression.
• literal($value) creates a QueryExpression.

Comparison operators:

• isNull($field) proxies QueryExpression method.
• isNotNull($field) proxies QueryExpression method.
• like() proxies QueryExpression method.
• notLike() proxies QueryExpression method.
• exists() proxies QueryExpression method.
• notExists() proxies QueryExpression method.
• between() proxies QueryExpression method.

I think we should intentionally avoid providing functions that cover common keywords/operators. The risk of creating difficult to read code is too high. We could use suffix/prefixes on the functions to disambiguate the Database API functions. Many of the function on FunctionBuilder should only have global functions added if we can find a prefix or suffix.