It's possible to do, but it is just very slow.
Currently, the query looks something like:
select * from Post left join User on Post.user_id_fk = User.user_id
order by xxx ASC|DESC
limit nn (typically 25)
GT::SQL currently can't handle the needed view query, which would be something along the lines of:
select * from Post left join User on Post.user_id_fk = User.user_id, PostView
where post_id_fk = post_id
order by post_thread_views ASC|DESC
limit nn
GT::SQL doesn't handle combining a left join with a normal join, plus the query itself is going to be quite a bit slower than the other ones.
Testing out the queries manually, selecting the top 25 sorted by views takes about 1.5 seconds on my system, compared to <0.2 seconds to select the 25 most recent.
BTW, Rumour has is that MySQL will have row-level locking in the upcoming version 4.0.
Also, expecting hosting clients to upgrade is wishful thinking. There are still a large number of servers out there still running Perl 5.004_04. For a little history lesson, 5.004_05 was released October 15, 1997. 5.005 was release July 22, 1998 (shortly followed by 5.005_01 5 days later).
Though I share your Perl 6 sentiment, judging from current trends it will be at least 3 years after its release until we could even consider writing Perl 6-specific code. A real shame :(
Jason Rhinelander
Gossamer Threads jason@gossamer-threads.com