README

Define reusable snippets in plain SQL with bound values. Use snippets recursively and compose complex or simple queries.

composer require packagefactory/sql-syntax-snippet

Simple Queries

We can define simple queries with their parameters:

$personQuery = Query::create(
    <<<SQL
    SELECT id FROM person WHERE givenName = :name
    SQL,
    ['name' => Value::string('Marc Henry')]
);

Here you can extract the SQL for MySQL and MariaDB with flattened placeholders to ? as names are not supported by the driver. Also IN (:myIds) will be flattened to IN (?, ?, ?) depending on how many entries myIds had.

This functionality was copied directly from doctrine/dbal but this package itself is fully independent.

The final SQL query can be used with any framework or native PHP PDO.

Subqueries

We can use queries within other queries as subquery. Using the above $personQuery would look like

$query = Query::create(
    <<<SQL
    SELECT bio FROM author WHERE author IN (:author)
    SQL,
    ['author' => $personQuery]
);

If we expand the SQL and parameters we get a flat list of values and a single SQL string:

SELECT bio FROM author WHERE author IN (SELECT id FROM person WHERE givenName = ?)

With parameters

["Marc Henry"]

Snippets

Any part that should be encapsulated but which does not form a semantic query should be defined as snippet. Snippets have their most value when being encapsulated into objects which provide snippets.

Let's write a PropertyEquals snippet which takes a property name and value and checks a JSON column:

class PropertyEqualsConstraint implements SnippetProvider
{
    public function __construct(
        public PropertyName $propertyName,
        public string $value,
    ) {
    }

    public function toSnippet(): Snippet
    {
        return Snippet::create(
            <<<SQL
            JSON_VALUE(properties, :propertyName) = :propertyValue
            SQL,
            [
                'propertyName'=> Value::string("$.{$this->propertyName->toEscapedString()}.value"),
                'propertyValue'=> Value::string($this->value),
            ]
        );
    }
}

We now can instantiate this constraint and use it in WHERE conditions

$myCompositeQuery = Query::create(
    <<<SQL
    SELECT id FROM entries WHERE
        :propertyCriteria
    SQL,
    ['propertyCriteria' => new PropertyEqualsConstraint(PropertyName::fromString('myProperty'), 'myValue')]
);

SELECT id FROM entries WHERE JSON_VALUE(properties, ?) = ?

As above with the subquery, this will resolve all parameters to a flat list without polluting the names of parameters across snippets.

Nested Snippets

The PropertyName above could be a nested snippet itself if it provides a snippet.

It could look like:

final readonly class PropertyName implements SnippetProvider
{
    private function __construct(
        public string $value
    ) {
    }

    public static function fromString(string $value): self
    {
        return new self($value);
    }

    public function toEscapedString(): string
    {
        try {
            return addslashes(json_encode($this->value, JSON_THROW_ON_ERROR));
        } catch (\JsonException $e) {
            throw new \RuntimeException(sprintf('Failed to escape property name: %s', $e->getMessage()), 1679394579, $e);
        }
    }

    public function toSnippet(): Snippet
    {
        return Snippet::create(
            <<<SQL
            JSON_VALUE(properties, :propertyName)
            SQL,
            [
                'propertyName' => Value::string("$.{$this->toEscapedString()}.value")
            ]
        );
    }
}

which can then simplify our above snippet to

return Snippet::create(
    <<<SQL
    :propertyName = :propertyValue
    SQL,
    [
        'propertyName'=> $this->propertyName,
        'propertyValue'=> Value::string($this->value),
    ]
);

And as its so simple we are ready to be able to implement more property comparisons ;)

Real world usage

This framework should allow to write all queries for the content graph in Neos efficiently without compromising readability. From complex subqueries to recursive CTEs. As everything is just plain SQL there is no limit in features.

A proof of concept can be seen here https://codeberg.org/PackageFactory/PackageFactory.Neos.MySqlContentGraphQueries

packagefactory / sql-syntax-snippet

Package info

Security

Statistics