Open dreamsbond opened 6 years ago
You have a few options for building your query.
/** @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();
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,
]);
is there any agreed location where we put the custom query helper function?
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?
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.
> 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?
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 ~~!!
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 AND
s and OR
s with any deepness.
Try it out and feel free to ask if you need some help with it.
tried a bit of the new ModelQueryBuilder really great having flexible injection adding complex filters
in mean time, i am still facing an issue.
the addSelect does not reflect in the relationship route as well as the include.
scenario:
<?php namespace App\Data\Types;
use CrEOF\Spatial\DBAL\Types\Geometry\PointType; use Doctrine\DBAL\Platforms\AbstractPlatform;
/**
@package App\Data\Types */ class JsonApiPointType extends PointType { /**
/**
/**
- override the Api class:
```php
/**
@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)
could you help?
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.
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
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
how to make it generic to detect the type of geometry object
take types from Model::getAttributeTypes()
, take type by column name then compare.
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?
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 ...;
});
}
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
I don't know what kind of problem with relationships, you might have. Let me know in case of any issues.
the scheme (jsonapi) returns value was not beings "ASTEXT"
call in relationship route and include queryparameter.
let me try to figure it out
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...