Project

General

Profile

Actions

Feature #33345

closed

Query orderings can't use custom expressions

Added by Tobias Liebig about 12 years ago. Updated about 4 years ago.

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

90%

Estimated time:
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();
Actions #1

Updated by Tobias Liebig about 12 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();
Actions #2

Updated by Gerrit Code Review about 12 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

Actions #3

Updated by Gerrit Code Review about 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

Actions #4

Updated by Gerrit Code Review about 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

Actions #6

Updated by Tobias Liebig about 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.

Actions #7

Updated by Tilo Baller about 11 years ago

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

Actions #8

Updated by Tobias Liebig about 11 years ago

.

Actions #9

Updated by Alexander Schnitzler almost 11 years ago

  • Target version set to Extbase 6.2
Actions #10

Updated by Anja Leichsenring almost 11 years ago

  • Target version changed from Extbase 6.2 to Extbase 6.3
Actions #11

Updated by Anja Leichsenring almost 11 years ago

  • Target version changed from Extbase 6.3 to Extbase 6.2
Actions #12

Updated by Alexander Schnitzler almost 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.

Actions #13

Updated by Bastian Zagar over 10 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);
Actions #14

Updated by Pascal Dürsteler over 10 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.

Actions #15

Updated by Stefan Neufeind almost 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).

Actions #16

Updated by Alexander Opitz over 9 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?

Actions #17

Updated by Peter Niederlag over 9 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 :(

Actions #18

Updated by Mathias Schreiber over 9 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)
Actions #19

Updated by Alexander Opitz almost 9 years ago

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

Updated by Benni Mack over 8 years ago

  • Target version changed from 7.5 to 8 LTS
Actions #21

Updated by PAMART Sylvain about 8 years ago

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

Actions #22

Updated by Riccardo De Contardi about 7 years ago

  • Target version changed from 8 LTS to 9.0
Actions #24

Updated by Susanne Moog about 6 years ago

  • Target version deleted (9.0)
Actions #27

Updated by Susanne Moog about 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.

Actions

Also available in: Atom PDF