Feature #33345

Query orderings can't use custom expressions

Added by Tobias Liebig over 7 years ago. Updated about 2 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2012-01-20
Due date:
% Done:

90%

PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

Currently Tx_Extbase_Persistence_Query->setOrderings does only support ASC and DESC orderings.

One might want to pass a custom expression as ordering. E.g. if you want to get your objects in a predefined order:

$query = $this->createQuery(); $query->setOrderings(array( 'FIELD (uid, ' . implode(', ', $uidList) .')' => Tx_Extbase_Persistence_QueryInterface::ORDER_FIELD )); return $query ->matching($query->in('uid', $uidList)) ->execute();

History

#1 Updated by Tobias Liebig over 7 years ago

fixed code sample


$query = $this->createQuery();
$query->setOrderings(array(
   'FIELD (uid, ' . implode(', ', $uidList) .')' => Tx_Extbase_Persistence_QueryInterface::ORDER_FIELD
));
return $query
  ->matching($query->in('uid', $uidList))
  ->execute();

#2 Updated by Gerrit Code Review over 7 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/8619

#3 Updated by Gerrit Code Review over 7 years ago

Patch set 2 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/8619

#4 Updated by Gerrit Code Review over 7 years ago

Patch set 3 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/8619

#5 Updated by Markus Günther almost 7 years ago

  • Has patch changed from No to Yes

#6 Updated by Tobias Liebig over 6 years ago

Felix: "SQL Injection still in place ..."

I see his point. Maybe we could archive a solution which supports FIELD orderings only, but does not allow any expressions.
I had the need (using FIELD for ordering) in at least two different projects, so i think the issue is still valid.

#7 Updated by Tilo Baller over 6 years ago

I agree with Tobias. Had the need for FIELD ordering in two of the four last projects.

#8 Updated by Tobias Liebig over 6 years ago

.

#9 Updated by Alexander Schnitzler about 6 years ago

  • Target version set to Extbase 6.2

#10 Updated by Anja Leichsenring about 6 years ago

  • Target version changed from Extbase 6.2 to Extbase 6.3

#11 Updated by Anja Leichsenring about 6 years ago

  • Target version changed from Extbase 6.3 to Extbase 6.2

#12 Updated by Alexander Schnitzler about 6 years ago

  • Target version changed from Extbase 6.2 to Extbase 6.3

As we are focusing on stability for 6.2 I retarget this issue. If we have a proper solution soon we can merge this for sure in 6.2.

#13 Updated by Bastian Zagar almost 6 years ago

This is my current solution:

$orderings = array();
foreach ($ids as $_id)
{
  $orderings["uid={$_id}"] = \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING;
}

$query->setOrderings($orderings);

#14 Updated by Pascal Dürsteler over 5 years ago

Wouldn't it be a more readable solution to just allow a mixed argument to setOrderings? If it is an array, behavior is like it is now, but if it is a string, it is used as-is, since that would be treated as a already well formed ORDER BY statement.

#15 Updated by Stefan Neufeind over 5 years ago

I agree that allowing strings for the ordering people might use statements that are non-portable across DBMS. But for most users that use MySQL/MariaDB nowadays (I expect) they could use orderings like RAND or giving a list of uids with a fixed order.

Please re-think if we could allow arbitrary strings "on your own risk" (maybe with a big warning in the docs or so) for certain edge-cases. And if it's not too intrusive maybe we could also allow that in 6.2 still (to stay for some years now sigh).

#16 Updated by Alexander Opitz almost 5 years ago

  • Project changed from Extbase MVC Framework to TYPO3 Core
  • Category changed from Extbase: Generic Persistence to Extbase
  • Status changed from Under Review to Needs Feedback
  • Target version changed from Extbase 6.3 to 7.0

Tobias, are you working on this issue?

#17 Updated by Peter Niederlag over 4 years ago

custom ordering is really a must have, portability is nice to have, that's the prio on all 200 projects I have done in 15 years.

Applying a custom sql order string would be very nice.

This seems to get urgent as current workarounds with parseQuery/buildQuery seem to have been gone on 6.2 :(

#18 Updated by Mathias Schreiber over 4 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)

#19 Updated by Alexander Opitz about 4 years ago

  • Status changed from Needs Feedback to New
  • Assignee deleted (Tobias Liebig)
  • Target version changed from 7.1 (Cleanup) to 7.5

#20 Updated by Benni Mack almost 4 years ago

  • Target version changed from 7.5 to 8 LTS

#21 Updated by PAMART Sylvain over 3 years ago

+1, need this and seems a failry reccurent need that it makes sense to implement it

#22 Updated by Riccardo De Contardi over 2 years ago

  • Target version changed from 8 LTS to 9.0

#24 Updated by Susanne Moog over 1 year ago

  • Target version deleted (9.0)

Also available in: Atom PDF