Monday, April 21, 2014

TYPO3 6.2 - Random sorting of QueryResults

In one of my TYPO3 Extensions I use MySQL RAND() to randomize the result of returned records. Actually, I use exactly the method as described here in the comments. You take the original query, extract all statement parts, add RAND() to the sorting in the statement parts and then you rebuild the query and finally you set the SQL statement of the query.

In TYPO3 6.2 LTS, this method of generating a random resultset is not supported anymore, since the method buildQuery() has been removed from Typo3DbBackend. I first tried to just insert the original buildQuery() method in my code and call it, but since ExtBase in TYPO3 6.2 uses prepared statements, you can't use the original buildQuery method.

I really needed random sorting in my extension, so I debugged a couple of hours and came finally to the solution, that it would be best to extend the QueryResult class in ExtbaseAs the QueryResult class uses arrays to store the query result, it should be easy to use PHP shuffle function to randomize the sorting of the array.

My new class randomQueryResult has just some small enhancements as you can see below.

use TYPO3\CMS\Extbase\Persistence\Generic\QueryResult;

class RandomQueryResult extends QueryResult {

 /**
  * Keeps track of, if queryResult has been shuffled
  */
 protected $shuffled = FALSE;

 /**
  * Loads the objects this QueryResult is supposed to hold
  *
  * @return void
  */
 protected function initialize() {
  parent::initialize();
  if (!$this->shuffled) {
   shuffle($this->queryResult);
   $this->shuffled = TRUE;
  }
 }
}

Now you can just use the RandomQueryResult class with your query and you will get the expected query results in random order.

$result = $this->objectManager->get('Vendor\Extension\Path\RandomQueryResult', $query);

Note: The original version of the blogpost contained a version with much more code (see revisions here), where I implemented a new class based on QueryResultInterface.

No comments:

Post a Comment