Actions
Bug #94899
open
Exbase: Expression #1 of ORDER BY clause is not in SELECT list
Start date:
2021-08-16
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
10
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:
Description
TYPO3 10.4.19
MySQL 5.7.35
When I do an individual Query in my Repository with an order_by to a related table field, an error like:
Expression #1 of ORDER BY clause is not in SELECT list, references column 'typo3.tx_lux_domain_model_pagevisit.tstamp' which is not in SELECT list; this is incompatible with DISTINCT
came up
Example function in a Repository:
public function findSomething(): QueryResultInterface
{
$query = $this->createQuery();
$query->setOrderings(['relatedTable.tstamp' => QueryInterface::ORDER_DESCENDING]);
return $query->execute();
}
When I do a "select @@SQL_MODE;" - this is the definition:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Updated by Alex Kellner over 3 years ago
This is probably related to:
Updated by Alex Kellner over 3 years ago
My workarround for now is to build my own SQL query as string and to use it in extbase. Maybe someone else needs this workarround:
// Add field tstamp to select statement so it can be used for ordering $query = $this->createQuery(); $sql = 'select v.*, pv.tstamp'; $sql .= ' from ' . Visitor::TABLE_NAME . ' v left join ' . Pagevisit::TABLE_NAME . ' pv on v.uid=pv.visitor'; $sql .= ' where pv.page=' . (int)$pageIdentifier . ' and v.deleted=0 and pv.deleted=0'; $sql .= ' order by pv.tstamp DESC limit 3'; return $query->statement($sql)->execute();
Updated by Georg Ringer over 3 years ago
- Related to Bug #92514: SELECT DISTINCT and ORDER BY in Extbase query lead to database error when using PostgreSQL added
Actions