How to sort DataObject by rand() in 5.1

Questions
1

Silverstripe Version: 5.1

Question:

In 4.7.2 this used to work

Dataobject::get()
   ->filter(['CategoryID' => 1, 'TypeID' => 1])
   ->sort('rand()')
   ->limit(3);

In 5.1

[Emergency]: Uncaught InvalidArgumentException: Invalid sort column RAND()
Line 432 in /var/www/html/vendor/silverstripe/framework/src/ORM/DataList.php

There is a comment in the code around that line:

$columnName is a param that is passed by reference so is essentially as a return type
it will be returned in quoted SQL “TableName”.“ColumnName” notation
if it’s equal to $col however it means that it WAS orginally raw sql, which is disallowed for sort()

Is there a way to still sort randomly through the db?

2

Hey @LanceH

I believe this was changed in 5.1 with a dedicated function for it.

Try this (not tested):

Dataobject::get()
   ->filter(['CategoryID' => 1, 'TypeID' => 1])
   ->limit(3)
   ->shuffle();  // shuffle does the random sorting

Edit
There used to be a note in the source which mentions sort() doesn’t support raw SQL (updated September '23), which is effectively what what rand() was. But shuffle() seems to be the suggested way to do this now.

1 Like