Search code examples
phprandomdoctrine-ormdql

Get random records with Doctrine


I wonder how to get a random number of Members from a Group, but I do not know what is the best way to do this, and I think ORDER BY RAND() is not the best alternative, as a Group can have more than 100,000 Members, performing this type of query could be very slow.

I found this way to make using SQL, but I do not know how to do the same thing in DQL: How can i optimize MySQL's ORDER BY RAND() function?


Solution

  • I'm not aware of any way to ORDER BY RAND() "efficiently" from Doctrine. In your situation, the best thing is probably to get primary keys first, shuffle these keys and then make use of them within an IN statement.

    You could also add a caching layer where to put (a subset of) the keys from the first query, especially if you have many records, so to avoid repeating the query on keys each time.