Last year I proposed PR #40115 to change database expressions to be grammar-enhanced. But my PR was changing too much, so I redid the whole concept to change as least as possible to be easier to merge for the next Laravel major release.

What is the problem?

Laravel's database implementation provides us a good way of working with multiple databases while abstracting away the inner workings. We don't have to think about small syntax differences when using a query builder or how column names are escaped to not interfere with reserved keywords.

However, when we want to use more database functionality we have to fall back to raw database code and writing database-specific code to e.g. quote column names or column aliases:

DB::table('table')
    ->when(isPostgreSQL(), fn ($query) => $query->select(DB::raw('coalesce("user", "admin") AS "value"')))
    ->when(isMySQL(), fn ($query) => $query->select(DB::raw('coalesce(`user`, `admin`) AS `value`')))

How can this be solved?

Currently the Expression class returned by DB::raw is just a container to indicate that the value should not be further processed. My implementation changes that approach a little bit by declaring an expression contract DB::raw uses and custom classes can implement:

interface Expression
{
    /**
     * Get the value of the expression.
     *
     * @param  \Illuminate\Database\Grammar  $grammar
     * @return scalar
     */
    public function getValue(Grammar $grammar);
}

Following that idea, the operations normally done by raw expressions and statements can be created as reusable expression classes. The former example could be written like this, which is totally database-agnostic for the one using those classes instead of raw expressions:

DB::table('table')
    ->select(new Alias(new Coalesce(['user', new 'admin']), 'count'));


class Alias implements Expression
{
    public function __construct(
    public readonly Expression|string $expression,
    public readonly string $name,
    ) { }

    public function getValue(Grammar $grammar): string
    {
        return match ($grammar->isExpression($this->expression)) {
            true => "{$grammar->getValue($this->expression)} as {$grammar->wrap($this->name)}",
            false => $grammar->wrap("{$this->name} as {$this->name}"),
        };
    }
}

class Coalesce implements Expression
{
    public function __construct(
        public readonly array $expressions,
    ) { }

    public function getValue(Grammar $grammar): string
    {
        $expressions = array_map(function ($expression) use($grammar): string {
            return match ($grammar->isExpression($expression)) {
                true => $grammar->getValue($expression),
                false => $grammar->wrap($expression),
            };
        }, $this->expressions);
        $expressions = implode(', ', $expressions);

        return "coalesce({$expressions})";
    }
}

But isn't it much work to write those classes?

Kind of. But keep in mind these have to be written only once and you don't have to be the one. Like with every package created by the community anyone can start and write those SQL expressions for anyone to use. When this PR will be merged into 10.x I will start by writing the most common ones at tpetry/laravel-query-expressions.

But I don't like the syntax

That's ok as I don't propose any syntax, I propose a system of how this can work. You can start a package that will provide a macro-able facade to do the same with static function calls as long as those calls return expression objects:

Expr::alias(Expr::coalesce(['user', 'admin']), 'count')

An implementation even doesn't have to use this wrapping. I can imagine a parser could transform a special little syntax to create those object chains from a string:

DB::table('table')
    ->select(exprParse('[user], [admin] ~> coalesce ~> alias:count'));

As said, this PR is not about providing syntax. It is about adding a building block packages can use to build something great

Outlook

In future PRs I would like to work on more things useful for database expressions:

• Access to the database type:. Some functions are implemented differently by the databases. When the Grammar would provide methods to identify it as e.g. a MySQL grammar, the expression could use a different function for regex replacement than for e.g. PostgreSQL.
• Value bindings: Right now request-provided values can't be added to expressions as the value could contain a SQL injection. When the Grammar would provide method to quote() a value these could be safely injected into an expression.
• Database version: Some features are easily done on recent database versions as critical features have been added while a lot of boilerplate is needed for older releases. When the Grammar would provide the database type and version a grammar class could use best implementation for every database and version.