Why isn't Rose::DB::Object sort_by RAND() doing what I expect?

Question

I can't get it to work. I'm using this query:

my $user_questions 
    = RoseDB::UserSecurityQuestion::Manager->get_user_security_questions(
        query        => [
                          'user.username' => $username,
                        ],
        with_objects => ['User','SecurityQuestion'],
        sort_by      => 'RAND()',
        limit        => 2,
    );

When I turn on debugging in Rose::DB::Object::Manager, I see that the order clause is:

ORDER BY t1.id, RAND()

Where is that t1.id coming from? And any idea how I can correct the ORDER BY to be just RAND()?

Answer 1

From the documentation for the sort_by parameter:

If selecting sub-objects (via require_objects or with_objects) that are related through "one to many" or "many to many" relationships, the first condition in the sort order clause must be a column in the primary table (t1). If this condition is not met, the list of primary key columns will be added to the beginning of the sort order clause automatically.

This is required in order to correctly associate sub-objects with their parent objects.

If you'd like to override this behavior, you can use the (as-yet undocumented) no_forced_sort boolean parameter.

my $user_questions =
  RoseDB::UserSecurityQuestion::Manager->get_user_security_questions(
    ...
    sort_by        => 'RAND()',
    no_forced_sort => 1);

But it is highly likely that this will cause sub-objects to be associated with the incorrect parent objects. What you'll need to make this work is a sort that is deterministically based on a unique characteristic of t1, but is otherwise random. That is, somefunc(t1.id) would be random but would always return the same result for a given value of t1.id, keeping all the children with the correct parents.

An obvious (and probably a lot more pragmatic) approach is to fetch all security questions for the user $username and then just randomly pick two:

my $user_questions =
  RoseDB::UserSecurityQuestion::Manager->get_user_security_questions(
    query        => [ 'user.username' => $username ],
    with_objects => ['User','SecurityQuestion']);

use constant NUM_RANDOM_QUESTIONS => 2;

my @questions;

for(1 .. NUM_RANDOM_QUESTIONS)
{
  last unless(@$user_questions);
  push(@questions, splice(@$user_questions, int(rand(@$user_questions)), 1));
}

Now you've got your (maximum) NUM_RANDOM_QUESTIONS randomly selected questions in @questions.