Repository navigation
Order By Random #308
Description
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.
Activity
dxg commentedon Aug 21, 2013
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 commentedon Aug 22, 2013
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:
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 commentedon Aug 26, 2013
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 commentedon Aug 27, 2013
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 commentedon Aug 27, 2013
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 commentedon Aug 27, 2013
@dxg maybe we could merge this with #311 (similar to #304)
dxg commentedon Aug 28, 2013
Definitelly. Let's consider this closed and continue discussion in there.