Order By Random

[erykwalder]

Hey guys, I've been digging through the tests and source, but I'm just missing how to do an order by random() query.

Passing it directly to order, it gets escaped as a column, which fails. Is there a way to select it as a column, then use that in the ordering list?

Thanks for any assistance.

[dxg]

This is a bad idea. See here and here.

A much better solution is to select all id's, pick some randomly, and then query the database for just those rows.

[notheotherben]

Also saw those, looks like it is possible to get random elements from a table which makes use of sequential IDs by using a locally generated random number between 1 and the number of elements in the table (non-inclusive). Then you execute a query like this:

SELECT * FROM table OFFSET $random_number, 1

It should be possible for us to write a plugin which adds this functionality as a chain argument, running n queries for n elements to be returned. How do you feel about that?

[erykwalder]

I can tell you the precise situation that I'm in, and you guys can see if you think there's an available solution. Basically I have a static table with about 1000 records, which is then filtered down to 25-50 records using conditions. From those 25-50 I need a random record.

Unfortunately this leaves out the possibility of relying on sequential ids. I could definitely select all possible ids and then randomly choose from those in the node side.

However, dealing with such a small subset, there aren't really performance issues for me to be concerned with, so I've just been going around the ORM and directly querying the table.

[dresende]

From what I understood of @dxg link, it doesn't matter if you have conditions to filter the subset, the random number will probably be assigned to every one of the 1000 records and then based on conditions one will be chosen. What do you have on that table?

[erykwalder]

From what I've seen, that's actually not the case. If you run an EXPLAIN on the query, the results are filtered first, then ordered.

My table is id, name, rank(int), category(int). I need a random name given a rank and category.

[dresende]

@dxg maybe we could merge this with #311 (similar to #304)

[dxg]

Definitelly. Let's consider this closed and continue discussion in there.