On Wed, Apr 18, 2012 at 05:18:41PM +0000, Adam Berg <adamb...@gmail.com> 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/ p...@phdru.name
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
