Struggling With SQLObject

Working with SQLObject is a lot of fun - unless you meet its limitations. Or other limitations.

En detail: Each of my releases has two dates (the day it was added here and the day of its last code modification). To correctly sort the 'latest releases' list on the right bar, I have to use the greater of those two dates. This works with the GREATEST() function, which unfortunately seems to be only available in MySQL and Oracle instead of being part of any of the ANSI SQL standards.

Since I already stumbled upon Thraxil's "raw queries with SQLObject" I knew where I had to get in. What I came up with was this:

rows = Product._connection.queryAll('''
    SELECT id, title, version, added,
           GREATEST(version, added) latestChange,
           (version > added) isUpdated
    FROM `%s`
    ORDER BY latestChange DESC, id DESC;
    ''' % Product.sqlmeta.table)[:limit]

Although I could at least get the table name from the table object, the rest is more SQL than I wanted. If you know a way to reduce that to a minimum, please let me know.

Looks like I need detailed SQL statement manipulation possiblities. I'm also gonna take a closer look at SQLAlchemy and PyDO/PyDO2, although I don't really like their table definition syntaxes (yet).

Update (27-May-2006): Looks like there is a very pythonic solution:

class Product(SQLObject):

    # ...

    def _get_latestChange(self):
        return max(self.version, self.added)

products = list(
products.sort(key=lambda x: x.latestChange, reverse=True)

The Product will get an additional read-only attribute (see SQLObject documentation) which is then used to sort the list in Python - not in the DBMS. The key and reverse arguments of list.sort() were introduced in Python 2.4 (which I can use here since a few weeks, yay!), but the cmp argument should do the trick, too.