Issue Bug Closed

#105418QueryBuilder: no longer possible to build advanced orderings with orderBy

Michael Stopp 10/23/24, 16:58 updated 11/08/24, 10:07

Description

I’m basically repeating myself from 6 years ago (#84814): with the current QueryBuilder it seems impossible to do orderings that go beyond simple field list, i.e. something like in these real-life examples:

ORDER BY FIELD(eventtype, 0, 4, 1, 2, 3), sorting

or

ORDER BY CAST(SUBSTRING_INDEX(c.IP, '.', -1) AS UNSIGNED)

Before moving to Doctrine DBAL version 4 it was possible to achieve this with something like:

$queryBuilder->add('orderBy', 'FIELD(eventtype, 0, 4, 1, 2, 3), sorting')

How can we do this now, after the add() method was dropped in DBAL v4?

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 4

  1. Stefan Bürk commented and changed status New Needs Feedback assigned to Stefan Bürk <span data-processor="relativetime" data-value="2024-10-28T13:35:29+00:00" > 10/28/24, 13:35 </span>

    First of all, add() was a internal method of Doctrine DBAL and has been
    removed by Doctrine DBAL. We decided to not restore that and following the
    line Doctrine DBAL has drawn.

    Since the very beginning, the TYPO3 QueryBuilder was an decorator around
    the Doctrine QueryBuilder and accessibaly with *getConcreteQueryBuilder()*
    which is still possible.

    The argument for the dropped add() method was, that it is doable by the
    generic methods, in case of order things by using oderBy() or addOrderBy().

    The TYPO3 levele does automatic quoting and stuff, and in cases that is not
    usefull, the underlying concrete builder instance needs to be used – and should
    have be done so already earlier.

    The raw doctrine QueryBuilder does not do any quoting, so you have to take care
    completly on your own.

    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
      ->getQueryBuilderForTable('pages');
    $queryBuilder
      ->select('uid', 'pid', /* ... */ )
      ->from('pages')
      ->where(
        $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(123, Connection::PARAM_INT)),
      );
    
    $concreteQueryBuilder = $queryBuilder->getConcreteQueryBuilder();
    $concreteQueryBuilder
      // proper quoting must be done manually on the concrete query builder
      ->orderBy('FIELD(' .$queryBuilder->quoteIdentifier('eventtype') . ', 0, 4, 1, 2, 3)');
    
    $queryBuilder->addOrderBy('sorting'); // use correct identifier quoting
    
    $result = $queryBuilder->executeQuery();
    while ($row = $result->fetchAssociative()) {
      // ..
    }

    Further, you can also revieve the Doctrine DBAL Expression Builder from the concrete QueryBuilder
    instead of the advanced TYPO3 Expression Builder.

    Note, since TYPO3 v13 the TYPO3 ExpressionBuilder has additional expression variants, not provided
    by the underlying Doctrine DBAl ExpressionBuilder – see corresponding v13 ChangeLog for that.

    In general, it is a good advice to build a helper class for custom expression which is tested against
    all TYPO3 supported Database vendors (using the Platform class) etc. Can be done from the outside.

    Or providing changes (FEATURE) to add missing expression (with full test coverage ) to the TYPO3
    implementation or contribute directly to Doctrine DBAL.

    From my point of view this is not a bug, just an oversight of the proper API to use. I hope I could
    point out how to do the required stuff and going to close this issue in a couple of days if there is
    not a real bug involved.

    Please give feedback if the explanation helped you to build your needed query.

  2. Michael Stopp commented <span data-processor="relativetime" data-value="2024-10-29T09:57:03+00:00" > 10/29/24, 09:57 </span>

    Thank you very much, this is extremely helpful! And I guess you’re right, this isn’t really a bug and the issue can be closed.

    However, I still think the documentation could be more helpful here. I just checked, this option to use the concrete QueryBuilder as demonstrated in you example isn’t mentioned anywhere. And the suggested migration for ->add() doesn’t work for cases as described in my issue, so it isn’t helping either. And the latest version of the docs for ->orderBy() still recommends to use ->add() for extended use cases (actually still based on my old issue…). I’ll try to make a change request for that. Anyway, thanks again!

  3. Garvin Hicking commented and changed status Needs Feedback Closed <span data-processor="relativetime" data-value="2024-11-07T15:46:45+00:00" > 11/07/24, 15:46 </span>

    Thanks, closing as suggested. Please feel free to make a docs PR / issue on the TYPO3-Documentation GitHub repo to address this (unless it slipped by me and you already did).

  4. Michael Stopp commented <span data-processor="relativetime" data-value="2024-11-08T10:07:23+00:00" > 11/08/24, 10:07 </span>

    I just made a PR with docs update suggestions.