Error on sorting observations by date
When a user has one of the special roles (SpeciesSpecialist, RegionalLead, CountryLead) the repository adds distinct clause to the queryBuilder. However, if the ORDER BY clause contains a field that is not in select list (in this case aggregate.dateTimeFrom), database will throw error because when using DISTINCT, all fields in ORDER BY must be specified in SELECT.
However, distinct clause was there to avoid duplicates that may occur in joins, particularly if the user is counter AND some of the special roles (ie. he is also land coordinator for the same transect).
I checked again and there is no danger of getting duplicate results, even if we remove distinct clause, because serialization is done based on ID of the entity and will return unique entities.
I removed distinct clauses from repositories and wrote tests to make sure the error is not there and check uniqueness of the returned results.
Request:
query {
observations (page: 1, itemsPerPage: 10, , aggregate_dateTimeFrom_order: "asc" )
{
collection {
_id
id
abundance
outOfMethod
comment
sex
isDead
species
createdAt
imageRefs {
collection {
url
thumbnailRefs {
collection {
url
}
}
}
}
aggregate {
dateTimeFrom
hasImages
qsStatus
isQsFinal
}
sectionEvent {
inspection {
_id
id
}
section {
name
label
transect {
createdAt
name
}
}
}
developmentalStage {
_id
id
label
}
}
paginationInfo {
itemsPerPage
lastPage
totalCount
}
}
}Error:
{"errors":[{"debugMessage":"An exception occurred while executing \u0027SELECT DISTINCT o0_.id AS id_0, o0_.abundance AS abundance_1, o0_.out_of_method AS out_of_method_2, o0_.comment AS comment_3, o0_.sex AS sex_4, o0_.is_dead AS is_dead_5, o0_.species AS species_6, o0_.row_id AS row_id_7, o0_.created_at AS created_at_8, o0_.modified_at AS modified_at_9, d1_.id AS id_10, d1_.label AS label_11, o0_.developmental_stage_id AS developmental_stage_id_12, o0_.section_event_id AS section_event_id_13, o0_.created_by_id AS created_by_id_14, o0_.last_modified_by_id AS last_modified_by_id_15 FROM \u0022observation\u0022 o0_ LEFT JOIN \u0022section_event\u0022 s2_ ON o0_.section_event_id = s2_.id LEFT JOIN \u0022section\u0022 s3_ ON s2_.section_id = s3_.id LEFT JOIN \u0022transect\u0022 t4_ ON s3_.transect_id = t4_.id LEFT JOIN land_coordinator l5_ ON (t4_.icc2 = l5_.icc2) LEFT JOIN \u0022regional_coordinator\u0022 r6_ ON t4_.id = r6_.transect_id LEFT JOIN \u0022view_observation_aggregates\u0022 v7_ ON o0_.id = v7_.observation_id INNER JOIN \u0022developmental_stage\u0022 d1_ ON o0_.developmental_stage_id = d1_.id WHERE o0_.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY v7_.date_time_from ASC, o0_.id ASC\u0027 with params [450, 451, 452, 517, 518, 519, 520, 521, 522, 523]:\n\nSQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list\nLINE 1: ...$1, $2, $3, $4, $5, $6, $7, $8, $9, $10) ORDER BY v7_.date_t...\n ^","message":"Internal server error","extensions":{"category":"internal"},"locations":[{"line":2,"column":13}],"path":["observations"],"trace":[{"file":"\/www-data\/vendor\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":182,"call":"Doctrine\\DBAL\\Driver\\AbstractPostgreSQLDriver::convertException()"},{"file":"\/www-data\/vendor\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":159,"call":"Doctrine\\DBAL\\DBALException::wrapException()"},{"file":"\/www-data\/vendor\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php","line":2121,"call":"Doctrine\\DBAL\\DBALException::driverExceptionDuringQuery()"},{"file":"\/www-data\/vendor\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php","line":1264,"call":"Doctrine\\DBAL\\Connection::handleExceptionDuringQuery()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/Query\/Exec\/SingleSelectExecutor.php","line":44,"call":"Doctrine\\DBAL\\Connection::executeQuery()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/Query.php","line":325,"call":"Doctrine\\ORM\\Query\\Exec\\SingleSelectExecutor::execute()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/AbstractQuery.php","line":1073,"call":"Doctrine\\ORM\\Query::_doExecute()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/AbstractQuery.php","line":1027,"call":"Doctrine\\ORM\\AbstractQuery::executeIgnoreQueryCache()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/AbstractQuery.php","line":791,"call":"Doctrine\\ORM\\AbstractQuery::execute()"},{"file":"\/www-data\/vendor\/doctrine\/orm\/lib\/Doctrine\/ORM\/Tools\/Pagination\/Paginator.php","line":171,"call":"Doctrine\\ORM\\AbstractQuery::getResult()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/Bridge\/Doctrine\/Orm\/AbstractPaginator.php","line":69,"call":"Doctrine\\ORM\\Tools\\Pagination\\Paginator::getIterator()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/GraphQl\/Resolver\/Stage\/SerializeStage.php","line":189,"call":"ApiPlatform\\Core\\Bridge\\Doctrine\\Orm\\AbstractPaginator::getIterator()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/GraphQl\/Resolver\/Stage\/SerializeStage.php","line":100,"call":"ApiPlatform\\Core\\GraphQl\\Resolver\\Stage\\SerializeStage::serializePageBasedPaginatedCollection()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/GraphQl\/Resolver\/Factory\/CollectionResolverFactory.php","line":102,"call":"ApiPlatform\\Core\\GraphQl\\Resolver\\Stage\\SerializeStage::__invoke()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/ReferenceExecutor.php","line":632,"call":"ApiPlatform\\Core\\GraphQl\\Resolver\\Factory\\CollectionResolverFactory::ApiPlatform\\Core\\GraphQl\\Resolver\\Factory\\{closure}()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/ReferenceExecutor.php","line":555,"call":"GraphQL\\Executor\\ReferenceExecutor::resolveOrError()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/ReferenceExecutor.php","line":1247,"call":"GraphQL\\Executor\\ReferenceExecutor::resolveField()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/ReferenceExecutor.php","line":257,"call":"GraphQL\\Executor\\ReferenceExecutor::executeFields()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/ReferenceExecutor.php","line":208,"call":"GraphQL\\Executor\\ReferenceExecutor::executeOperation()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/Executor\/Executor.php","line":155,"call":"GraphQL\\Executor\\ReferenceExecutor::doExecute()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/GraphQL.php","line":158,"call":"GraphQL\\Executor\\Executor::promiseToExecute()"},{"file":"\/www-data\/vendor\/webonyx\/graphql-php\/src\/GraphQL.php","line":90,"call":"GraphQL\\GraphQL::promiseToExecute()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/GraphQl\/Executor.php","line":34,"call":"GraphQL\\GraphQL::executeQuery()"},{"file":"\/www-data\/vendor\/api-platform\/core\/src\/GraphQl\/Action\/EntrypointAction.php","line":86,"call":"ApiPlatform\\Core\\GraphQl\\Executor::executeQuery()"},{"file":"\/www-data\/vendor\/symfony\/http-kernel\/HttpKernel.php","line":157,"call":"ApiPlatform\\Core\\GraphQl\\Action\\EntrypointAction::__invoke()"},{"file":"\/www-data\/vendor\/symfony\/http-kernel\/HttpKernel.php","line":79,"call":"Symfony\\Component\\HttpKernel\\HttpKernel::handleRaw()"},{"file":"\/www-data\/vendor\/symfony\/http-kernel\/Kernel.php","line":195,"call":"Symfony\\Component\\HttpKernel\\HttpKernel::handle()"},{"file":"\/www-data\/web\/index.php","line":22,"call":"Symfony\\Component\\HttpKernel\\Kernel::handle()"}]}],"data":{"observations":null}}Edited by Michael Voigt