-
Notifications
You must be signed in to change notification settings - Fork 7
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Custom sets of querybuilder #39
Comments
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 CRUDIn 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? |
I think best place would be API class such as this |
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 |
> 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. |
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 For example, you have a builder for $alias = $builder->createRelationshipAlias(Model::REL_AUTHOR); Now you can use That's really all you need. Just an alias. However, 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 Filters and sorts can now work with aliases as well via methods applyFilters and applySorts The first parameter Try it out and feel free to ask if you need some help with it. |
tried a bit of the new ModelQueryBuilder |
in mean time, 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;
/**
* 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
}
}
/**
* @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);
}
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 ..., ASTEXT(`table_name`.`geometry`) AS geometry And from this point, it looks odd. Firstly, a builder should add the column to Thus, you have to add your spatial fields to Model's 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 $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 I think you should try this approach and, as I understand it, it will solve your issues with relationships. |
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.
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 |
take types from |
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 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. thanks. |
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...
The text was updated successfully, but these errors were encountered: