Project

General

Profile

Actions

Bug #94899

open

Exbase: Expression #1 of ORDER BY clause is not in SELECT list

Added by Alex Kellner over 3 years ago. Updated 28 days ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
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


Related issues 1 (1 open0 closed)

Related to TYPO3 Core - Bug #92514: SELECT DISTINCT and ORDER BY in Extbase query lead to database error when using PostgreSQLNew2020-10-08

Actions
Actions #2

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();

Actions #3

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 #5

Updated by Alex Kellner 28 days ago

And again in TYPO3 13

Actions

Also available in: Atom PDF