README

sad_spirit/el2sql: ExpressionLanguage to SQL Translator

One of the first examples given on Symfony's ExpressionLanguage page is

# Promote article to the homepage when
article.commentCount > 100 and article.category not in ["misc"]

Assuming articles are kept in the database, we may eventually want to fetch all articles that satisfy the above constraint. The naïve way is to fetch everything and filter the result using $expressionLanguage->evaluate(), a nicer approach is to translate the expression to SQL and make the database do the job.

sad_spirit/el2sql allows exactly this: given an ExpressionLanguage expression it generates a WHERE clause part in Postgres dialect of SQL.

Let's use the package to translate the above example. We assume the following database schema for articles and their related comments:

create table example.articles (
    article_id integer not null generated by default as identity,
    article_title text not null,
    article_text text not null,
    article_category text not null default 'misc',
    article_state text not null default 'draft',

    constraint articles_pkey primary key (article_id),
    constraint articles_state_valid check ( article_state in ('draft', 'rejected', 'approval', 'published') )
);

create table example.comments (
    comment_id integer not null generated by default as identity,
    article_id integer not null,
    comment_text text not null,
    comment_hidden bool not null default false,

    constraint comments_pkey primary key (comment_id),
    constraint comments_articles_fkey foreign key (article_id)
        references example.articles (article_id)
        on delete cascade on update restrict
);

-- This may also be a partial index
create index comments_article_hidden_idx on example.comments (article_id, comment_hidden);

Rows from example.articles will be mapped to the following entity which also contains a lazy-loaded magic commentCount property:

namespace sad_spirit\el2sql\example;

use sad_spirit\el2sql\attributes\{
    CustomLookup,
    MagicProperty,
    PropertyToColumnTransformation,
    Table
};

#[Table('example', 'articles')]
#[PropertyToColumnTransformation(addPrefix: 'article_')]
#[MagicProperty('commentCount', new CustomLookup(CommentCountTranslator::class))]
final class Article
{
    private ?int $commentCount = null;

    public function __construct(
        public readonly int $id,
        public readonly string $title,
        public readonly string $text,
        public readonly string $state,
        public readonly string $category,
        private readonly callable $commentCountLoader
    ) {
    }

    public function __get(string $name): mixed
    {
        if ('commentCount' === $name) {
            return $this->commentCount ??= (int)($this->commentCountLoader)($this->id);
        }
        throw new \BadMethodCallException("Wrong property '$name'");
    }
}

Note the attributes applied to the class. The first two establish a default mapping of object's properties to columns of example.articles table. The third one specifies a custom translator that should be used for magic commentCount property:

namespace sad_spirit\el2sql\example;

use sad_spirit\el2sql\{
   ExtraFragmentsProvider,
   PropertyLookupTranslator
};
use sad_spirit\el2sql\translators\properties\CustomTranslator;
use sad_spirit\pg_builder\{
    Select,
    Statement,
    enums\ConstantName
};
use sad_spirit\pg_builder\nodes\{
    ColumnReference,
    ScalarExpression,
    expressions\KeywordConstant
};
use sad_spirit\pg_gateway\TableGateway;
use sad_spirit\pg_gateway\fragments\{
    CustomSelectFragment,
    join_strategies\JoinTargetFinder
};

final class CommentCountTranslator extends CustomTranslator implements ExtraFragmentsProvider, PropertyLookupTranslator
{
    public function translate(): ScalarExpression
    {
        return new ColumnReference('ac', 'comment_count');
    }

    public function getExtraFragments(): iterable
    {
        yield new class ('join-to-comment-count') extends CustomSelectFragment {
            use JoinTargetFinder;

            public function applyTo(Statement $statement, bool $isCount = false): void
            {
                /** @var Select $statement */
                $this->findNodeForJoin($statement->from, TableGateway::ALIAS_SELF)
                    ->leftJoin(
                        <<<QRY
                        lateral (
                            select count(comment_id) as comment_count
                            from example.comments as c
                            where not comment_hidden and
                                  c.article_id = self.article_id
                        ) as ac
                        QRY
                    )
                    ->on = new KeywordConstant(ConstantName::TRUE);
            }
        };
    }
}

An instance of Article can obviously be used as a variable in the original expression

use Symfony\Component\ExpressionLanguage\ExpressionLanguage;
use sad_spirit\el2sql\example\Article;

$result = (new ExpressionLanguage())->evaluate(
    'article.commentCount > 100 and article.category not in ["misc"]',
    ['article' => new Article(/* ... */)]
);

Now let's translate that expression and fetch all articles satisfying the constraint

namespace sad_spirit\el2sql\example;

use sad_spirit\el2sql\{
    TranslatableExpressionLanguage,
    fragments\ResetAliasFragment,
    translators\DefaultPropertyTranslatorFactory
};
use sad_spirit\pg_gateway\{
    FragmentList,
    TableLocator,
    builders\FluentBuilder
};
use sad_spirit\pg_wrapper\Connection;

$connection  = new Connection('...');
$locator     = new TableLocator($connection);
$language    = new TranslatableExpressionLanguage(null, [], [new DefaultPropertyTranslatorFactory($locator)]);
// This builds a mapping from class properties to their representations in SQL, used later in translation 
$language->getVariableMapper()
    ->map('article', Article::class);

$translation = $language->translate(
    'article.commentCount > 100 and article.category not in ["misc"]',
    ['article']
);

$query = $locator->select('example.articles', fn (FluentBuilder $builder) => $builder
    // No problem in combining the translated expression with standard builder methods
    ->equal('article_state', 'published')
    ->orderBy('article_id desc')
    ->add(FragmentList::normalize($translation->getExtraFragments()))
    // Conditions may be combined via and / or / not, so they are returned separately from "extra fragments"
    ->add($translation->getCondition())
    // The translated Condition will use variable name (i.e. 'article') as a table alias,
    // need to change this to default 'self' used by pg_gateway
    ->add(new ResetAliasFragment('article')));

echo $query->createSelectStatement()->getSql();

Running the example outputs the following generated query

select self.*
from example.articles as self left join lateral (
        select count(comment_id) as comment_count
        from example."comments" as c
        where not comment_hidden
            and c.article_id = self.article_id
    ) as ac on true
where self.article_state = $1::"text"
    and ac.comment_count > 100
    and self.article_category <> all(array['misc'])
order by article_id desc

How Does This Work?

ExpressionLanguage has a parse() method that builds an AST for the expression. This is then transformed into AST for Postgres dialect of SQL, which is represented using classes from pg_builder package.

Transforming ASTs is actually an easier part, the harder part is mapping of object properties to database columns and translating custom functions.

Features of ExpressionLanguage That are Currently Supported

Warning

Using features depending on PHP type juggling may lead to errors and / or incorrect results. In particular, avoid

non-strict comparisons == and !=;
ternary operator shorthands foo ?: bar and foo ? bar.

Literals

The only omission is hashes: these will currently be translated to Postgres arrays with their keys discarded.

Accessing Public Properties

These are translated using implementations of PropertyLookupTranslator and PropertyReferenceTranslator. The former is used when there is a need to look up database rows, the latter when there is an object available representing a specific database row.

As you can see in the above example, the simplest lookup translation is from a property name to a database column.

The simplest reference translation is a query parameter with its value coming from an object's property.

Null-Coalescing Operator

Translated to COALESCE() SQL construct, a.b ?? 42 becomes COALESCE(a.b, 42).

Working with Functions

Function invocations are translated by implementations of FunctionTranslator interface. These can be passed alongside other function properties when registering functions:

TranslatableExpressionLanguage::register() accepts an optional fourth argument type-hinted as FunctionTranslator.
There is a TranslatableExpressionFunction class extending ExpressionFunction, its constructor accepts an instance of FunctionTranslator. When an instance of TranslatableExpressionFunction is passed to TranslatableExpressionLanguage::addFunction() (either directly or via an implementation of ExpressionFunctionProviderInterface) the translator is registered and will be used for that function invocations.

Builtin Functions

min() function is translated to LEAST() (SQL MIN() function is aggregate).
max() function is translated to GREATEST().

Additionally, is_null() PHP function is registered and its invocation is translated to IS NULL SQL construct.

Operators

Arithmetic Operators

+, -, *, /, % operators are translated to the same ones in Postgres.

** is translated to ^, which is left-associative in Postgres, so parentheses may be added:

2 ** 3 ** 3

becomes

2 ^ (3 ^ 3)

Note

Postgres division operator / behaves like PHP's intdiv() function when its operands are integer:

el2sql=# select 5 / 2, 5. / 2;
?column?  |      ?column?
----------+--------------------
       2  | 2.5000000000000000

Bitwise Operators

&, |, ~, <<, >> operators are translated to the same ones in Postgres, ^ (bitwise xor) is translated to #.

All Postgres bitwise operators have the same precedence, so parentheses may be added.

Comparison Operators

<, >, <=, >= are translated to the same operators.
=== and !== are translated to IS NOT DISTINCT FROM and IS DISTINCT FROM, respectively. This is done so that comparison with a NULL value will yield TRUE or FALSE rather than NULL.
== and != are translated to comparison of text representations, e.g. a.b == c.d yields
```
a.b::text is not distinct from c.d::text
```
This is done to allow some comparison of different types, but obviously PHP's type juggling rules are not followed here. It is highly recommended to use only strict comparisons.
contains: 'foobarbaz' contains 'bar' is translated to 0 != strpos('foobarbaz', 'bar').
starts with: 'foobarbaz' starts with 'foo' is translated to starts_with('foobarbaz', 'foo').
matches and ends with are currently not supported.

Logical Operators

not and ! are translated to not;
and and && are translated to and;
or and || are translated to or;
xor is not supported as there is no logical xor in Postgres. However, it is usually possible to replace it with an inequality operator: a !== b where a and b are boolean will evaluate to true if
- a is true and b is false or
- a is false and b is true.

String Operators

~ is translated to ||.

Array Operators

in: foo in bar is translated to foo = any(bar). Here bar may be either an array or a subquery, which is quite useful when mapping properties to fields of related tables.
not in: foo not in bar is translated to foo <> all(bar). As above, bar may also be a subquery.

Numeric Operators

Range operator .. maps to the range() function on PHP side. The closest Postgres equivalent to that is generate_series(), but that returns multiple rows rather than an array. Thus, currently .. is translated to an array built using array_positions() / array_fill() so that the range may be used in scalar contexts.

Ternary Operators

foo ? bar : baz is translated to case foo when true then bar else baz end. As there is no type juggling on SQL side, foo should evaluate to boolean, types of bar and baz should be compatible.

Features of ExpressionLanguage that will not be Supported

"Plain" Variables

All variables used in expressions should be objects. Additionally, these objects / their classes should be mapped to variable names before translating the expression, as shown in the above example

$language->getVariableMapper()
    ->map('document', Document::class);

If there is a need to use some data that does not map to database objects, create a holder class (e.g. Environment) for that and add custom translators for its properties.

Calling Methods

These do not have a suitable counterpart in Postgres. You'll have to use (virtual) public properties instead of getters or plain functions if there is a need to pass additional arguments.

Null-Safe Operator

It does not make sense in the context of this package: if a variable is missing then a lookup query should be built for the corresponding table.

Array Offsets on Variables

Using array offsets on variables implementing ArrayAccess is unlikely to be supported as there is no corresponding syntax feature on Postgres side.

Possible Enhancements

Mapping ExpressionLanguage hashes to JSON fields;
Array offsets on properties;
matches and ends with operators.

Expression with Multiple Variables

Let's consider a more complex business rule that may appear in some document approval scenario:

document.author in employee.subordinates

Two types of queries are possible based on that

Fetch all documents that are authored by subordinates of the given employee;
Fetch all managers for the author of the given document.

Additionally, while document.author will probably map to a column of the documents table, employee.subordinates will probably not be that simple: subordination is often represented using a department tree with employees linked to the branches of that.

TODO: add code

sad_spirit / el2sql

Package info

Security

Statistics