Random Database Pull

[eluser]codelearn[/eluser]
Hey guys,

I'm having some trouble retrieving random entries from a table. I have a certain amount of multiple choice questions in a table and I need to retrieve a random amount of questions from a random set of questions.

The structure looks like this:

Code:

id (int)
question (text)
varchar (int)
set (int)

What would be the active record code to pull lets say 3 random questions from a particular set?

Thanks so much for the help!

[eluser]xwero[/eluser]

Code:

$this->db->where('set',1)->order_by('id','random')->get();

[eluser]codelearn[/eluser]
xwero,

Thanks for the reply. Would there be a way to randomize the set we are pulling as well?

[eluser]xwero[/eluser]
I forgot the limit in the previous snippet

Code:

$this->db->where('set',1)->order_by('id','random')->limit(3)->get();

I don't think you can do it using one query because you only want one set and three questions.

[eluser]Pygon[/eluser]
[quote author="xwero" date="1205263700"]I forgot the limit in the previous snippet

Code:

$this->db->where('set',1)->order_by('id','random')->limit(3)->get();

I don't think you can do it using one query because you only want one set and three questions.[/quote]

Would this not work for some reason?

Code:

$this->db->where('set',1)->order_by('set','random')->order_by('id','random')->limit(3)->get();

Honestly, I think you'd be better off doing something where the set = random then order_by random id for performance.

[eluser]codelearn[/eluser]
xwero,

Was that in regards to also having a random set?

Pygon,

It would not work because your still asking for set #1.

[eluser]Pygon[/eluser]
[quote author="codelearn" date="1205265056"]xwero,

Was that in regards to also having a random set?

Pygon,

It would not work because your still asking for set #1.[/quote]

I don't think so. You're asking for a random set orders, then random question orders in the sets with my query.

EDIT:

ahh sorry -- didn't notice he had where set=1

Code:

$this->db->order_by('set','random')->order_by('id','random')->limit(3)->get();

[eluser]codelearn[/eluser]
Pygon,

But aren't you still asking for the set = 1? Maybe I'm just confused.

Thanks!

RESPONSE TO EDIT: Would that pull random questions from a specific set? For instance if Set 1 had only 2 questions this would return a third question from a dif. set.. correct?

[eluser]Pygon[/eluser]
Yes -- posted the edit -- I didn't notice the where(set,1) in xwero's query.

The performance on my query, while random, is incredibly bad.

I would suggest implementing something like this: http://jan.kneschke.de/projects/mysql/order-by-rand/

If you're worried about sub-query support, select_max and do your randomizing/ceil in php, then do a second select with your where and order_by. Really the holes is only a concern if you are worried about even random distribution across sets with gaps in the number (1,2,5,12)

[eluser]Pygon[/eluser]

RESPONSE TO EDIT: Would that pull random questions from a specific set? For instance if Set 1 had only 2 questions this would return a third question from a dif. set.. correct?

Yes, this would, but as above, it's incredibly bad performance if you have a large number of rows.