Custom sets of querybuilder

[dreamsbond]

I am going to apply some spatial query by doctrine to the database.
is it possible to build a custom sets of query builder.
something like those 'withFilter' etc...
where should i start with and where is the standardized location for putting those custom query builder.

for example. ST_WITHIN... etc...

[neomerx]

You have a few options for building your query.

Pure Doctrine

        /** @var Connection $connection */
        $connection  = $this->getContainer()->get(Connection::class);
        $query       = $connection->createQueryBuilder();
        $query
            ->select(...)
            ->from(Model::TABLE_NAME)
            ->where(Model::FIELD_ID . '=' . $query->createPositionalParameter(...));
        $statement = $query->execute();

Customize CRUD

In API class you can reuse query building functionality from CRUD as

        /** @var \Doctrine\DBAL\Query\QueryBuilder $builder */
        $builder = $this
            ->withFilters([
                Comment::FIELD_ID_POST => [
                    FilterParameterInterface::OPERATION_EQUALS => [$index],
                ],
            ])
            ->withPaging($offset, $pageLimit)
            ->createIndexBuilder([
                Comment::FIELD_ID_POST,
                Comment::FIELD_TEXT,
                Comment::FIELD_CREATED_AT,
            ]);

[dreamsbond]

is there any agreed location where we put the custom query helper function?

[neomerx]

I think best place would be API class such as this

[dreamsbond]

followed by putting:

    /**
     * @inheritDoc
     */
    protected function createIndexModelBuilder(iterable $columns = null): ModelQueryBuilder
    {
        $builder = parent::createIndexModelBuilder($columns);
        $addSelect = 'ASTEXT(' . $builder->getQuotedMainAliasColumn(Model::FIELD_GEOMETRY) . ') AS ' . Model::FIELD_GEOMETRY;

        return $builder->addSelect($addSelect);
    }

into the api

i can make the geometry column returns textual string (WKT), e.g.: POINT(105.665049 13.390277) from mysql binary

i found it was fine at first.

but later i would have to put this kind of declaration in every api class where geometry presents

is there any generic way to make it in BaseApi class?

besides,

i have another problems handling geometry column with "AsText" for relationship model.

i do not have idea where to get the relationship builder and hack it with "AsText"

could you help?

[neomerx]

I'm currently in the process of refactoring ModelQueryBuilder to make adding conditions to both fields and relationships super flexible. Hopefully, it will be ready tomorrow so we can get back to this issue.

[dreamsbond]

> You have a few options for building your query.
> 
> Pure Doctrine
>         /** @var Connection $connection */
>         $connection  = $this->getContainer()->get(Connection::class);
>         $query       = $connection->createQueryBuilder();
>         $query
>             ->select(...)
>             ->from(Model::TABLE_NAME)
>             ->where(Model::FIELD_ID . '=' . $query->createPositionalParameter(...));
>         $statement = $query->execute();
> Customize CRUD
> In API class you can reuse query building functionality from CRUD as
> 
>         /** @var \Doctrine\DBAL\Query\QueryBuilder $builder */
>         $builder = $this
>             ->withFilters([
>                 Comment::FIELD_ID_POST => [
>                     FilterParameterInterface::OPERATION_EQUALS => [$index],
>                 ],
>             ])
>             ->withPaging($offset, $pageLimit)
>             ->createIndexBuilder([
>                 Comment::FIELD_ID_POST,
>                 Comment::FIELD_TEXT,
>                 Comment::FIELD_CREATED_AT,
>             ]);

as i will implement much more spatial field filtering on the application.
is it possible the extend the FilterParameter class and put some virtual filter, kind of spatial filter, say 'within', 'contains' functions?

[dreamsbond]

I'm currently in the process of refactoring ModelQueryBuilder to make adding conditions to both fields and relationships super flexible. Hopefully, it will be ready tomorrow so we can get back to this issue.

look forward to new ModelQueryBuilder ~~!!

[neomerx]

Hi, I just published 0.8.5 which adds some very useful methods to ModelQueryBuilder and now you can build any conditions in relationships limited only by Doctrine abilities.

The most important method is createRelationshipAlias(string): string which accepts relationship name and returns table alias for a table in that relationship. Internally it does SQL JOIN for the corresponding table (1 or 2 if necessary). You can use this alias to build literally unlimited by complexity filters for that relationship. The method is smart enough to work with belongsTo, hasMany and belongsToMany relationships. This method alone justifies the existence of ModelQueryBuilder however it has a few more useful ones.

For example, you have a builder for comments and you need to filter them by some properties in author relationship

$alias = $builder->createRelationshipAlias(Model::REL_AUTHOR);

Now you can use $alias with all Doctrine's where possibilites

That's really all you need. Just an alias.

However, ModelQueryBuilder brings a few more helpful methods that make adding filters easier

this method

$column1 = $builder->buildColumnName($alias, OtherModel::FIELD_XXX);

builds a full column name such as

`table_name`.`column_name`

and now you can build very complex conditions such as this

$exp = $builder->expr();
$builder->andWhere($exp->andX(
    $exp->orX($exp->isNull($column1), $exp->eq($column1, $value1)),
    $exp->orX($exp->isNull($column2), $exp->eq($column2, $builder->createSingleValueNamedParameter($value2)))
));

method createSingleValueNamedParameter similar to QueryBuilder::createNamedParameter howerver it also adds information about parameter's type.

Filters and sorts can now work with aliases as well via methods applyFilters and applySorts

The first parameter CompositeExpression can be plain $builder->expr()->andX() or as complex as shown above. It is possible to build multi-level conditions with ANDs and ORs with any deepness.

Try it out and feel free to ask if you need some help with it.

[dreamsbond]

tried a bit of the new ModelQueryBuilder
really great having flexible injection adding complex filters

[dreamsbond]

in mean time,
i am still facing an issue.

the addSelect does not reflect in the relationship route as well as the include.

scenario:

• i have an geometry column which is a POINT type in MySQL;
• the geometry was something in the binary format, e.g.: "0000000001010000002CD32F116F7A5C40CC9717601F693640"
• what i am going to do, creating a custom class extending the doctrine type from creof/doctrine2-spatial package:

<?php namespace App\Data\Types;

use CrEOF\Spatial\DBAL\Types\Geometry\PointType;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * Class JsonApiPointType
 *
 * @package App\Data\Types
 */
class JsonApiPointType extends PointType
{
    /**
     * Type name
     */
    const NAME = 'limoncelloPoint';

    /**
     * @inheritDoc
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'POINT';
    }

    /**
     * @inheritDoc
     */
    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return (string)parent::convertToPHPValue($value, $platform); // TODO: Change the autogenerated stub
    }
}

• override the Api class:

    /**
     * @inheritDoc
     */
    protected function createIndexModelBuilder(iterable $columns = null): ModelQueryBuilder
    {
        $builder = parent::createIndexModelBuilder($columns);
        $addSelect = 'ASTEXT(' . $builder->getQuotedMainAliasColumn(Model::FIELD_GEOMETRY) . ') AS ' . Model::FIELD_GEOMETRY;

        return $builder->addSelect($addSelect);
    }

• it is good for the model itself, returning a proper WKT string, e.g.: POINT(113.913029 22.410635)
  instead of POINT(7.291123237071E-304 9.9914408331082E+228)

• but this does not apply to model having has-many relationship, or even belongsToMany relationship.

could you help?

[neomerx]

Sure. However, I need to understand what are you trying to achieve.

I think I understand first 3 bullets: you use one the spatial data types and have corresponding PHP/Doctrine type.

What I don't understand is next 3 bullets. You override createIndexModelBuilder and what you do there is adding a new column wrapped with SQL function AsText like

...,  ASTEXT(`table_name`.`geometry`) AS geometry

And from this point, it looks odd. Firstly, a builder should add the column to SELECT list itself but not manually as it's done above. Secondly, you can tell ModelQueryBuilder to wrap any column in any function in SELECT.

Thus, you have to add your spatial fields to Model's getAttributeTypes method. It will look something like

    public static function getAttributeTypes(): array
    {
        return [
            self::FIELD_ID       => Type::INTEGER,

            ...

            self::FIELD_GEOMETRY => JsonApiPointType::NAME,
        ];
    }

It should solve 'adding the column to SELECT list problem'.

Next. How to wrap that column in AsText? Api/Crud has withColumnMapper which should be used to wrap the column with AsText. Something like

$crudApi = ...;

$data = $crudApi
    ->withColumnMapper(function (string $columnName, QueryModelBuilder $builder): string {
        return $columnName === Model::FIELD_GEOMETRY ? 'ASTEXT(' . $builder->getQuotedMainAliasColumn(Model::FIELD_GEOMETRY) . ') AS ' . Model::FIELD_GEOMETRY : $columnName;
    })
    ->index();

Of course, you're likely to override API/CRUD constructor and add call to withColumnMapper there.

I think you should try this approach and, as I understand it, it will solve your issues with relationships.

[neomerx]

Mapper should be a bit different. Default mapper is set here. As you can see it just adds alias name and wraps both alias and column as

`alias`.`column`

Thus your implementation should do the same but geometry column should be wrapped with AsText function as well.

[dreamsbond]

Thus, you have to add your spatial fields to Model's getAttributeTypes method. It will look something like

public static function getAttributeTypes(): array
{
    return [
        self::FIELD_ID       => Type::INTEGER,

        ...

        self::FIELD_GEOMETRY => JsonApiPointType::NAME,
    ];
}

It should solve 'adding the column to SELECT list problem'.

yes, i did the part above you mentioned

[dreamsbond]

thanks for indicating this method, i did not think of. really thanks. i will try it out.

Next. How to wrap that column in AsText? Api/Crud has withColumnMapper which should be used to wrap the column with AsText. Something like

$crudApi = ...;

$data = $crudApi
->withColumnMapper(function (string $columnName, QueryModelBuilder $builder): string {
return $columnName === Model::FIELD_GEOMETRY ? 'ASTEXT(' . $builder->getQuotedMainAliasColumn(Model::FIELD_GEOMETRY) . ') AS ' . Model::FIELD_GEOMETRY : $columnName;
})
->index();
Of cource, you're likely to override API/CRUD consturcot and add call to withColumnMapper there.

I think you should try this approach and, as I understand it, it will solve your issues with relationships.

meanwhile, i am struggling on how to make it generic to detect the type of geometry object, says it was 'limoncelloPoint', check against

$this->getModelSchemes()->getAttributeTypes($modelClass);

i am thinking and try overriding those "fetch" prefixed method, say "fetchResources", but at last i found some private method

[neomerx]

how to make it generic to detect the type of geometry object

take types from Model::getAttributeTypes(), take type by column name then compare.

[dreamsbond]

Next. How to wrap that column in AsText? Api/Crud has withColumnMapper which should be used to wrap the column with AsText. Something like

$crudApi = ...;

$data = $crudApi
->withColumnMapper(function (string $columnName, QueryModelBuilder $builder): string {
return $columnName === Model::FIELD_GEOMETRY ? 'ASTEXT(' . $builder->getQuotedMainAliasColumn(Model::FIELD_GEOMETRY) . ') AS ' . Model::FIELD_GEOMETRY : $columnName;
})
->index();
Of course, you're likely to override API/CRUD constructor and add call to withColumnMapper there.

I think you should try this approach and, as I understand it, it will solve your issues with relationships.

overriding the individual api class constructor returning "Call to a member function withColumnMapper() on null"

any hints to resolve?

[neomerx]

That's likely something on your side.

For example \App\Api\PostsApi, both notations work as expected

    public function __construct(ContainerInterface $container)
    {
        parent::__construct($container, Model::class);

        // option 1
        $this->withColumnMapper(function (string $name): string {
            return ...;
        });

        // option 2
        parent::withColumnMapper(function (string $name): string {
            return ...;
        });
    }

[dreamsbond]

after removing vendor folder and composer update again.
the error gone.

thanks.
is the columnMapper covers relationship as well?
for example, both includes and relationship

[neomerx]

I don't know what kind of problem with relationships, you might have. Let me know in case of any issues.

[dreamsbond]

the scheme (jsonapi) returns value was not beings "ASTEXT"

call in relationship route and include queryparameter.

let me try to figure it out