Feature #33345
closed
Query orderings can't use custom expressions
90%
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();
Updated by Tobias Liebig almost 13 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();
Updated by Gerrit Code Review almost 13 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
Updated by Gerrit Code Review over 12 years ago
Patch set 2 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/8619
Updated by Gerrit Code Review over 12 years ago
Patch set 3 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/8619
Updated by Tobias Liebig over 11 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.
Updated by Tilo Baller over 11 years ago
I agree with Tobias. Had the need for FIELD ordering in two of the four last projects.
Updated by Alexander Schnitzler over 11 years ago
- Target version set to Extbase 6.2
Updated by Anja Leichsenring over 11 years ago
- Target version changed from Extbase 6.2 to Extbase 6.3
Updated by Anja Leichsenring over 11 years ago
- Target version changed from Extbase 6.3 to Extbase 6.2
Updated by Alexander Schnitzler over 11 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.
Updated by Bastian Zagar over 11 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);
Updated by Pascal Dürsteler almost 11 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.
Updated by Stefan Neufeind over 10 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).
Updated by Alexander Opitz about 10 years ago
- Project changed from 534 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?
Updated by Peter Niederlag about 10 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 :(
Updated by Mathias Schreiber almost 10 years ago
- Target version changed from 7.0 to 7.1 (Cleanup)
Updated by Alexander Opitz over 9 years ago
- Status changed from Needs Feedback to New
- Assignee deleted (
Tobias Liebig) - Target version changed from 7.1 (Cleanup) to 7.5
Updated by Benni Mack about 9 years ago
- Target version changed from 7.5 to 8 LTS
Updated by PAMART Sylvain almost 9 years ago
+1, need this and seems a failry reccurent need that it makes sense to implement it
Updated by Riccardo De Contardi over 7 years ago
- Target version changed from 8 LTS to 9.0
Updated by
Updated by Wolfgang Klinger almost 5 years ago
Here is another (easy) Solution:
https://gist.github.com/wazum/5b21cfa2f3da04189b52ea86a1da85c0
Updated by Susanne Moog over 4 years ago
- Status changed from New to Closed
Though I see the need to have a solution for this in individual projects, I don't think it is something extbase should provide by default. I would suggest using either of the two solutions in the last comments with the additional note that building complex queries is something that should be done with the doctrine querybuilder directly (which allows more flexibility not only in terms of ordering but also in the area of selecting records). Our extbase querybuilder is targeted at making the default tasks easier, not at providing a solution for all possible use cases (SQL can be as complex as it is useful ;)) so here is where we should draw a line.