Idable queries Relational library dev release

I have been working this weekend on a "real-world" solution. The application I want to create is a CMS where the fields for the content of the pages are user driven.

I want to show off the library based on the PostgresRepository I created over the weekend.

The simple things first

public function getPages(): MapCollection|Error
{
   $query = "SELECT ~Pages:Id, ~Pages:Name, ~Pages:Url FROM ~Pages:Pages";

   return getSecondLevelMapCollection(
         buildStatement(
            $this->settings->connection,
            $query,
            namespaces: $this->settings->namespaces,
         ),
        $query,
   );
}

The ~Pages:X in the query is an enum that implements the Identifier from the core package. And the X are cases.

I wanted to show calling the buildStatement and getSecondMapCollection without the PHP 8.5 pipe operator. Because I added quite a few convenience functions most of the times only a few methods are needed.
For the people who want more control I added a runChain function in the core library for an experience similar to the pipe operator.

The buildStatement function is a wrapper for the PDO->prepare and PDOStatement->bindParam methods.
The getSecondMapCollection function takes care of the data fetching and transforms the data into a MapCollection. The latter is an array of Map instances.

The use of a Map is in another method.

$query = "SELECT ~Pages:Id, ~Pages:Name, ~Pages:Url FROM ~Pages:Pages WHERE ~Pages:Url = :Pages:Url";
$page = $this->getPage(
    $query,
    IdableParameterCollection::createWithIdableParameter(Pages::Url, $url),
);

// some code that can be ignored for this example

$pageMap = createMapFromFirstLevelResults($page, $query, namespaces: $this->settings->namespaces);
$mapCollection = $this
   ->getFieldValueMapCollectionByPageId((int) $pageMap[Pages::Id]);

The getPage method contain the buildStatement and getRow functions.
The createMapFromFirstLevelResults function creates the Map instance. And then the instance value, $pageMap[Pages::Id], is used for the next repository method.
The main goal of a Map is to avoid using the database names even after the data is fetched. It is something where ORM solutions leave it up to the developers, while they have model properties as the database name abstraction. It never sat good with me, and it is one of the reasons I started the library.
Because the identifiers are just enums they can be used in domains. The naming of the identifiers is totally up to what the application needs it to be.

The settings come from the container that is added to the Slim application. For more information about the Slim application creation you can read my previous post.

A more advanced query

SELECT fbv.~FieldBooleanValues:Value boolean, null as float, null as integer, 
null as long_text, null as short_text, pffbv.~PageFieldBooleanValues:Weight as weight
FROM ~PageFieldBooleanValues:PageFieldBooleanValues pffbv
LEFT JOIN ~FieldBooleanValues:FieldBooleanValues fbv 
ON fbv.~FieldBooleanValues:Id = pffbv.~PageFieldBooleanValues:FieldId
WHERE pffbv.~PageFieldBooleanValues:PageId = :Pages:Id

This is a part of a UNION ALL query.
I find the placeholders for the fields very readable, because it is easier to see which fields are used and which ones are just there to create the table shape.
The placeholders in the rest of the query look more like noise to me.
But the main thing again is to abstract the actual database names, and maintain the names on a single location. It will not be ideal in every situation.

I fell into a old pattern, where I changed the PageFieldBooleanValues:Weight from PageFieldBooleanValues:Order because in Postgres order is a keyword. I should have left the case Order and changed the value to weight.
It is so easy to slip in language changes because that makes the querying easier. By using the identifiers this should become a thing of the past.

A more advanced configuration of a Map

$aliasValueFilter = fn($i): bool => is_string($i);
$aliases = AliasCollection::createWithAlias('boolean', FieldBooleanValues::Value, fn($i): bool => Is_bool($i))
   ->add('float', FieldFloatValues::Value, $aliasValueFilter)
   ->add('integer', FieldIntegerValues::Value, $aliasValueFilter)
   ->add('long_text', FieldLongTextValues::Value, $aliasValueFilter)
   ->add('short_text', FieldShortTextValues::Value, $aliasValueFilter)
        ;

return buildAliasesMapCollection($data, $aliases);

In the previous Map example the createMapFromFirstLevelResults function was used. That function uses the query to transform the data. And can optionally use an AliasCollection.
With the buildAliasesMapCollection function the transformation relies fully on the alias input to create the Map instance.

The static createWithAlias method is a combination of the collection instantiation and calling the add method. The first two arguments are the alias and the identifier pair.
The query produces a lot of fields with null, these shouldn't appear in the map. And that is where the third argument comes into play. It needs to be a closure with the bool return type.
And that is how the fields will be fetched that are fully controlled by an editor.

Closing words

To install the library use composer require Xwero\idable-queries-relational or download a release from Github.
I set the stability of the package to dev. I want to stay on that stability level until the 0.5.0 version. Mainly because I want to see if there are going to be breaking changes while I'm focusing on the Redis and MongoDB packages.
I feel the core package is 80% done. But the buildAliasesMapCollection and `buildAliasesMap functions started quite a big refactor. And that is something I want to avoid as much as possible in the future.

Adding more database types will be my main focus, but my mind is already wandering into the direction of having a query builder. I wouldn't go for the actual pattern. I am more thinking about an atomic design sort of way of doing it. A very early idea is something like;

``

$query = new Query(
    new Select(
        new Fields(
            new Field(Pages::Id),
            new Field(Pages::Name),
            new Field(Pages::Url),
        ),
        new From(Pages::Pages),
        new Where(
            new EqualParameter(Pages::Id),
        )
    )
);

``

The Field and EqualParameter items are the atoms, Where and Fields are the molecules, Select is the organism and Query is the template.
The major work will be to split up all the functionality SQL has and distinguish the database dialects.
That is why I'm going to put that further in the future.

I know the library exposes the native query language again, which means not abstracting away the foreign fields and pivot tables with code. For me it was an eye opener to see what a database can do without being confined to an ORM solution.
When you try the library I hope you will have the same experience of wonder and frustration I had. Nothing good happens with a little bit of trouble.