Forum OpenACS Q&A: Random Book off bookshelf

Posted by Joel Natividad on
Hi folks,
I'm setting up my blog right now using OACS and I'd like to pull a by having a random book review pulled from my bookshelf (see

Any hints on how to do this?

Thanks in advance!

Posted by Joel Natividad on
Got word from Lars that he just checked it in last nite:

Posted by Sebastiano Pilla on
I didn't find how Lars has done it, but anyway I have something similar on my site: to pull a song at random from a database table (using PostgreSQL) the query is
select id, artist, album, title, artist_url, album_url
      from SONGS
      order by random()
      limit 1 offset 0
The key is the order by random() to achieve the random ordering and the limit 1 offset 0 clause to get only the first row of the results.

Of course if there are better ways to do it I would be happy to know.
Posted by Jarkko Laine on

Yes, that's exactly how it's done in Lars's as well as in random-photo script. You can leave 'offset 0' away though, since it's the default behaviour and it wouldn't really matter anyway from which place we took the random song.

Posted by Mark Aufflick on
What I do for random photo and book on my site ( to minimise db hits on the front page is to util_memoize a list of photo (or book) id's, and then pick one at random in tcl.

Sure the list is pretty darn long, but not really a lot of data (they're just integers) and every db hit you save on your front page is worth it.

I stole this idea (and some of the code) from Lars' old photo and book packages he hacked for his old website. You can download my diffs from his code on my website ( but I don't think you can download Lars' old modules from CVS any more. The new ones are much better anyway.

If you want, I can post the tcl/sql snippets that do the work.

Posted by Joel Natividad on
Hey Mark!
That would be great if you can post the relevant snippets here so I can hack it into my blog.

Hopefully, it gets folded into the main photo-album package soon by the package owners... ;)

BTW, I see that you're still running on Lar's old code.  Are you planning on upgrading to OACS 5.1?

One thing I really miss about Lar's old photo-album is the ratings functionality.  Activating general-comments was fairly straightforward but I'm still decyphering ratings.

I checked out the ratings package per Jeff Davis' suggestion, but there is no doc with it at the moment.

Anybody using ratings at the moment?  Has anybody tried integrating it with lars-blogger, bookshelf and photo-album?

Posted by Jarkko Laine on

Do you only memoize the id's? Doesn't that mean that you still need the db hit to get the other attributes (and the photo if it's in the db, too)? If you need to hit the db anyway, I don't think the random functionality adds too much overhead. But maybe I understood something wrong.

Posted by Jeff Davis on
Jarkko, the "select ... orderby random() limit 1" can be pretty slow for a large table. On a users table with 8300 users I get:
head=# explain analyze select * from users order by random() limit 1 offset 0;
                                                     QUERY PLAN                                                      
 Limit  (cost=803.12..803.12 rows=1 width=1225) (actual time=113.42..113.42 rows=1 loops=1)
   ->  Sort  (cost=803.12..823.86 rows=8298 width=1225) (actual time=113.41..113.41 rows=2 loops=1)
         Sort Key: random()
         ->  Seq Scan on users  (cost=0.00..262.98 rows=8298 width=1225) (actual time=0.02..39.11 rows=8298 loops=1)
 Total runtime: 114.64 msec
(5 rows)
whereas if you have the id picked randomly from a cached list the query to get the single row is small numbers of milliseconds.

I think caching the id's is generally a win...

Posted by Mark Aufflick on
Keepin in mind that the old lars-images uses image-name rather than image-id as the primary key:

proc_doc pinds_img_random_name { } {
    Returns the name of a random image
} {
    set image_names [util_memoize { db_list image_names { select name from pinds_images where promote_p =\
 't' } } 43200 ]
    set image_no [randomRange [llength $image_names]]
    return [lindex $image_names $image_no]
And then the front page just calls pinds_img_tag -front with the random image name to display the widget.

The random book code does exactly the same thing. I also applied the old lars-ratings package to books and a few other things - it's really easy since all you need to pass the ratings widgets is an object_id. I haven't used the new ratings package but I assume the principle is the same.

As to performance, the proc could easily memoize the image description, type and dimensions to avoid a db hit altogether (since in the old lars-images the photos are just filesystem files stored with a filename of image_name), but I think the biggest gain is from avoiding randomizing across a whole table in the db.

Hmm - upgrading to OpenAcs 5? I have upgraded all my client sites to get all the great new codebase, but is a bit too hacked for a simple upgrade - I'll have to throw the code away and just migrate the data (which would still be very painful). I suspect it will stay as is until I want to totally redesign it, but that won't be happening any time soon since I quite like it! The tabs and auto generated ascii titles took FOREVER too align properly in (nearly) all browsers. There's quite a few browser specific case statements scattered around...

Posted by Mark Aufflick on
heh - the proc_doc dates the code a little ;) Think ad_proc.
Posted by Jarkko Laine on
Thanks, Mark and Jeff,

As I'm updating the random photo widget to respect the permissions to individual photos, I'll slip the caching scheme in, too.