Review main Abandoned

61443[FEATURE] Allow special ordering in Extbase

Tizian Schmidlin CR 0 V 0 Normal +118 −10 updated 07/04/23, 07:42

Commit message

7 years ago I published a feature request on forge.typo3.org and I
finally came around to write a proper implementation, that supports more
than just "I'd like to have concat in Extbase orderings". The idea is to
offer a way to have an easy way to have complex orderings as they happen
in SQL and applications, without being limited by the Object Model
regarding ordering.

This change shouldn't break anything since all it does is open the API
to new functionality. The idea is that with this feature, a developer is
able to pass a native ordering to extbase and it will treat it like that
and just pass it to Doctrine DBAL.

Some examples that work with this, but didn't before:

This would return all results, ordered by the concatenated fields
`field1`, `field2` and `field3`:

.. code-block:: php

   $query = $this->createQuery();
   $query->setOrderings([ 'CONCAT(field1, field2, field3)' =>
   QueryInterface::EXTBASE_ORDER_DESCENDING_NATIVE]);

   return $query->execute();

Until now, this would render an Extbase Exception because it would try
to convert `CONCAT` into `c_o_n_c_a_t`.

This also works with other cases like this, like ordering by specific
uids:

.. code-block:: php

   $query = $this->createQuery();
   $query->setOrderings([ 'uid = 1, uid = 4, uid = 3, uid = 5' =>
   QueryInterface::EXTBASE_ORDER_DESCENDING_NATIVE]);

   return $query->execute();

Review

  • Code review CR 0
  • Verified V 0

Issues 1

Files 4

Review log 3446

Show 1421 earlier messages
  1. Tizian Schmidlin uploaded patch set 1 08/05/19, 14:08

  2. TYPO3com voted on patch set 1 V -1 08/05/19, 14:09 Completed build in 16s on Mon, 5 Aug, 02:09 PM Test Summary: No tests found Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-35592

  3. Benni Mack uploaded patch set 2 11/09/19, 14:04

  4. Benni Mack uploaded patch set 3 11/09/19, 14:10

  5. TYPO3com voted on patch set 2 V +1 11/09/19, 14:12 Completed build in 08m 13s on Sat, 9 Nov, 02:12 PM Test Summary: 48243 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-38181

  6. TYPO3com voted on patch set 3 V -1 11/09/19, 14:18 Completed build in 08m 16s on Sat, 9 Nov, 02:18 PM Test Summary: 48236 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-38182

  7. TYPO3com voted on patch set 3 V +1 11/09/19, 14:25 Completed build in 23m 36s on Sat, 9 Nov, 02:25 PM Test Summary: 48243 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-38182

  8. Simon Gilli commented on patch set 3 CR -1 11/09/19, 14:36

    Typo3DbQueryParser.php:307

    shouldn't that be an elseif? afais the next elseif won't be reached
  9. Benni Mack commented on patch set 3 11/09/19, 16:29

    Typo3DbQueryParser.php:307 Reply

    I dont think so. The existing implementation is the same. Look at the code before.
  10. Andreas Kienast commented on patch set 3 CR -1 11/09/19, 16:44

    I'll fix the Rest
  11. Andreas Kienast uploaded patch set 4 11/09/19, 16:49

  12. TYPO3com voted on patch set 4 V +1 11/09/19, 16:57 Completed build in 08m 14s on Sat, 9 Nov, 04:57 PM Test Summary: 48292 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-38206

  13. Simon Gilli commented on patch set 4 CR +1 11/09/19, 22:09

    Typo3DbQueryParser.php:307 Reply

    You're right. I thought the next elseif is on the same level but it's one upper
  14. Oliver Klee commented on patch set 4 11/10/19, 09:33

    Can we please cover this with (functional) tests?
  15. Guido Schmechel rebased onto patch set 5 12/24/19, 11:02

  16. Guido Schmechel uploaded patch set 6 12/24/19, 11:06

  17. TYPO3com voted on patch set 5 V -1 12/24/19, 11:12 Completed build in 09m 11s on Tue, 24 Dec, 11:12 AM Test Summary: 69128 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-39565

  18. TYPO3com voted on patch set 6 V +1 12/24/19, 11:22 Completed build in 13m 11s on Tue, 24 Dec, 11:21 AM Test Summary: 69134 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-39566

  19. Alexander Schnitzler commented on patch set 6 02/15/20, 12:41

    QueryInterface.php:97

    Please rename the constants. At best, prefix with EXTBASE_ because the QueryInterface and the rest of the QOM is taken from the java content repository and I want us to be able to distinguish what's our own implementations are and what we just copied over back then. And while you're at it, please mark the constants public.
  20. Elias Häußler wrote 02/18/20, 22:10

    Removed reviewer Elias Häußler.
  21. Benni Mack uploaded patch set 7 04/27/21, 19:25

  1. core-ci voted on patch set 7 V +1 04/27/21, 19:36 Core CI is happy: https://gitlab.typo3.org/TYPO3/CI/cms/-/pipelines/10774

  2. Benni Mack commented on patch set 8 04/27/21, 20:19

    Published edit on patch set 7.
  3. Benni Mack updated the commit message in patch set 9 04/27/21, 20:20

  4. core-ci voted on patch set 8 V +1 04/27/21, 20:31 Core CI is happy: https://gitlab.typo3.org/TYPO3/CI/cms/-/pipelines/10784

  5. core-ci voted on patch set 9 V +1 04/27/21, 20:36 Core CI is happy: https://gitlab.typo3.org/TYPO3/CI/cms/-/pipelines/10785

  6. Oliver Klee wrote 04/27/21, 22:21

    Removed reviewer Oliver Klee.
  7. Tizian Schmidlin commented on patch set 9 04/28/21, 06:38

    Is there a need for any more reviews or would this be able to be considered as a new feature for TYPO3 11 and maybe backport to 10 and 9?
  8. Benni Mack commented on patch set 9 04/28/21, 07:22

    We need reviews and people to test and since it's a feature, it will only go into v11.
  9. Benni Mack voted on patch set 9 V +1 CR +1 07/29/21, 12:03

  10. Torben Hansen commented on patch set 9 V -1 07/31/21, 11:47

    I really think this is a useful feature and I would like to get it merged, but I'm not able to verify the functionality. Tested both provided code examples and also a custom ordering using the MySQL FIELD function but with no success. $query->setOrderings(['FIELD(uid,1,5,4,3)' => QueryInterface::EXTBASE_ORDER_DESCENDING_NATIVE]); This leads to this error message: Unknown column 'FIELD(uid,1,5,4,3)' in 'order clause' Reason for this seems to be, that the fieldname of the ORDER BY statement is automatically quoted (see https://github.com/TYPO3/typo3/blob/master/typo3/sysext/core/Classes/Database/Query/QueryBuilder.php#L850), so you end up with a query like shown below: SELECT * FROM `tt_content` WHERE `uid` IN (1,3,4,5) ORDER BY `FIELD(uid,1,5,4,3)` DESC Due to the quotes, the content inside the quotes is treated as a column. If you remove the quotes manually, the query works fine. In order to resolve the problem, the ORDER BY can be added manually to the query in Typo3DbQueryParser as shown below: $this->queryBuilder->add('orderBy', $columnName . ' '. $order, true); Note, that this is unsafe and leads to SQL injection, if either $columnName or $order is provided as unsanitized user input. So if the alternative solution would be implemented, the docs should clearly point this out. We should also have a functional test for this feature to ensure, native sorting works as expected.
  11. Benni Mack wrote 08/03/21, 07:59

    Topic set to extbase
  12. Simon Gilli commented on patch set 9 11/12/21, 11:31

    QueryInterface.php:97 Reply

    Done
  13. Benni Mack wrote 11/29/21, 10:14

    Change destination moved from master to main
  14. Benni Mack updated the commit message in patch set 10 11/29/21, 13:38

  15. Simon Schaufelberger uploaded patch set 11 06/17/22, 20:28

  16. core-ci voted on patch set 11 V -1 06/17/22, 20:36 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/28166

  17. Simon Schaufelberger uploaded patch set 12 06/17/22, 21:34

  18. core-ci voted on patch set 12 V +1 06/17/22, 21:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/28167

  19. Oliver Klee commented on patch set 12 CR -1 07/14/22, 16:01

    QueryInterface.php:101 Unresolved

    In the meantime, we have gotten references to QueryInterface::ORDER_* in type annotations (which are parsed by PHPStan). So the new constants also need to start with ORDER_
  20. Oliver Klee commented on patch set 12 07/14/22, 20:09

    Typo3DbQueryParser.php:251 Unresolved

    I propose we have a constant VALID_ORDERS or VALID ORDERINGS for this and then do an in_array check.

    Typo3DbQueryParser.php:258 Unresolved

    I propose we have a constant NATIVE_ORDERS or NATIVE_ORDERINGS for this and then do an in_array check.

    Typo3DbQueryParserTest.php:596

    Please let's split this up into two tests - one for the native ASC and one for the native DESC. Also, this should be a functional tests. Otherwise, we'll be re-building most of the functionality in form of mocks, with makes the tests less useful.
  21. Stefan Bürk commented on patch set 12 07/14/22, 22:56

    Thanks Tizian for this patch. However, I cannot get warm with the used solution which feels kind of hacky and dirty and kinda wrong. In the end, even if it may be more work especial to keep "backwards compatible" alive, would be to overwork the "orderings" at all. I think we should introduce something more like following: Having "NATIVE_ASC" as sorting direction or something like that smells and is technically wrong. This patch tries to implement "unquoted/literal" ordering posibillity to extbase - and we should build something which refects that instead of doing some nasty workarounds/dirty hack. ``` * ExtbaseQueryOrderingInterface (interface) public constructor(private readonly string $identifier, private readonly ExtbaseQueryOrderType $order); // Build the ordering plain sql for a specific connection (and thus // driver/platform) public function buildForConnection(Connection $connetion): string; public function getIdentifier(): string; public function getOrder(): ExtbaseQueryOrderType; * ExtbaseQueryOrdering implementing the interface * ExtbaseQueryOrderingLiteral implementing the interface along with a big red alert that no auto quoting of the identifer/expression part is done and must properly be implemented/taken care by the user !!!! * ExtbaseQueryOrderType (as Enum with ASC/DESC) as valid options. * having new collection/array ($orderingDefinitions) with only object of ExtbaseQueryOrderingInterface * old setOrderings() will loop through and simply generate ExtbaseQueryOrdering instances per ordering definition * getOrdering() simply loops the new $orderingDefinitions array and make simple "'field' => 'ASC/DESC'" array again out of it * deprecate setter/getter of orderings (old methods) * if not already private/protected, deprecate old property. If private/protected, remove it (!?! must be checked) * .... tests tests tests (functional, not unit tests) ``` Note: just a quick shot for the alternative, not deeply thought. There may be better approches/namings in the detail possible (will bet on it). E.g. eventually not having a build() method at all and do this with a helper method then at the proper place etc. Having a build method how ever would make it possible that people may introduce own implementations on there own and have proper information provided based on the interface. So - I would appreciate if we can step a little back and rethink, if not going with the "first" hackish approach - or better thinking about some more robust and cleaner. I may be wrong - what do you/other people think about my comments/thoughts ?

    Typo3DbQueryParserTest.php:596 Reply Unresolved

    Ack. Functional tests will show that "CONCAT()" is not cross-dbms however. So test has to be adjusted for different dbms systems. (e.g. sqlite). Mocking methods/functions generating/manipulating generated sql in the end will never keep up with eventual dbms changes.
  22. Simon Schaufelberger commented on patch set 12 07/15/22, 01:22

    Reply

    I would look to other frameworks: How do they implement it if they do? We are close to Symfony framework. Is there a solution from them available?
  23. Stefan Bürk commented on patch set 12 07/17/22, 13:24

    Reply Unresolved

    Symonfy itself have nothing like that directly. It uses doctrine/dbal and doctrine/orm along combined with a doctrine bundle. Howwever, quering is done basicly with doctrine QueryBuilder. There is a special DQL from doctrine/orm and a NativeSQL (basicly retrieving the array rows and tranforming this into objects). TYPO3 has a QueryBuilder fascade to ensure proper quoting of identifiers and values, with having the ability to add some stuff as literal or at least to the underlaying concreqte QueryBuilder (doctrine/dbal native QueryBuilder). Other frameworks like laravel, laminas etc have similar concepts etc. But all togheter, only have "ASC" and "DESC" and nothting like "NATIVE_ASC" as a pass through to decide if something should be quoted or not. Some systems do not auto enquote or have objects e.g. for orderings. Extbase is an additional bunch of layers above that. Doing mostly own stuff. So I'm still convinced that doing a proper overhaul and passthrough with some Object interface is the proper way instead of introducing some hackish sorting direction identifier which are "extbase special" again. In the end .. if needed, someone could build a proper QueryBuilder query instead of using the extbase stuff in a Extbase repository and still combining it with the datamapper to properly retrieve extbase models. Only caveat is, that sorting on model "property" styles syntax must be properly build with joins etc. But's alrady possible - just not in the plain basic extbase way. Thinking more and more about this patch and the choosen approach it feels worser and worser .... personally I would not take or merge this approach. Not blocking it for now, but you could take my comment in that way that that may happen ...
  24. Alexander Schnitzler wrote 04/01/23, 17:16

    Removed cc Alexander Schnitzler.
  25. Benni Mack abandoned the change 07/04/23, 07:42

    it's been 4 years since the inception of this change and we are still stuck. I will abandon this change for the time being, however it can be restored if anyone wants to pick it up