sqlobject views

slight flaw in that code example - i had to use this:

class SiteSearch(sqlobject.SQLObject):
    class sqlmeta:
        #lazyUpdate = True
        cacheValues = False
        _cacheValue = False
    count = sqlobject.IntCol(default=None)

     def dropTable(cls, ifExists=False):

         if ifExists and not cls._connection.tableExists(cls.sqlmeta.table):
            return

         sql = "DROP VIEW %s" % (cls.sqlmeta.table,)
        cls._connection.query(sql)

     dropTable=classmethod(dropTable)

     def createTable(cls, ifNotExists=False, createJoinTables=True,
                    createIndexes=True, applyConstraints=True,
                    connection=None):
        conn = connection or cls._connection
        if ifNotExists and conn.tableExists(cls.sqlmeta.table):
            return

         sql = cls.createTableSQL()

         cls._connection.query(sql)

     createTable=classmethod(createTable)

 

i've just encountered the most _horrendous_ sql query i've ever had to design - it even beats the multi-alias-join thing to turn a sparse-entry recordset into a variable-width 2D table (for a demographic search)

the reason why i've had to use VIEWs is because the query has a COUNT record in it, and so requires a GROUP BY. the GROUP BY makes it impossible to do sensible multi-alias-joins, and not even a HAVING clause will do the trick.

so i had to first create the VIEW, then do a multi-alias-join multiple times on the VIEW.

aaaagh!