Re: [SQLObject] Select Single Random Record

[Oleg B. <ph...@ph...>]

On Wed, Apr 18, 2012 at 05:18:41PM +0000, Adam Berg <ada...@gm...> wrote:
> Oleg Broytman <phd <at> phdru.name> writes:
> > On Wed, Apr 18, 2012 at 12:30:10PM -0400, Adam Berg <adamberg <at> gmail.com> 
> wrote:
> > > What is the best way to select a single random record from a table and
> > > falls within the "where" criteria i have defined? I don't want to return a
> > > huge result set each time and then pick a random one
> > 
> >    Please define "random" in the context of your task. Do you want a
> > different record every time? Or some non-deterministic record that could
> > be the same every time is a possible solution?
> 
> It should be a different record each time. 

   Do it in two steps. First, count the number of objects that satisfy
your criteria:

count = MyTable.select(whereClause).count()

   Optimize your whereClause and indices. Generate an int in range
0..count-1 (see module `random`). And select the item:

row = MyTable.select(whereClause, orderBy=MyTable.q.id)[random_int]

   With this query SQLObject uses OFFSET and LIMIT=1 to select exactly
that row.

> To give context to the issue, I am building my own icecast2 broadcaster. I want 
> to have an Auto DJ feature which will pick a random song from the database which 
> hasn't recently been played plus some other criteria. It really doesn't matter 
> which record i get back from the database, I just need one at random.

   Aha, you mark played songs in the DB, so your whereClause certainly
contains "if was not played yet" criteria. In this case selecting just
the first item without ordering the result set ought to be enough:

row = MyTable.select(whereClause)[0]

   You have to experiment.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.