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) 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();
[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(); 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]
Quote: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. |
Welcome Guest, Not a member yet? Register Sign In |