[SQLObject] multiplejoin and default order on nullable datetime column

[Jan-Wijbrand K. <jw...@in...>]

Hi,


I'm building a search application on top of a database with historical
information. The data model is not very complex, I think, but I found
myself running into the following problem:

Say I have SQLObject class Foo like so:

class Foo(SQLObject):
    _connection = hub

    class sqlmeta:
        defaultOrder = 'startdate'

    startdate = DateCol()
    bar = ForeignKey('Bar')

And a Bar class, that has a relation to Foo, like so:

class Bar(SQLObject):
    _connection = hub

    class sqlmeta:
        defaultOrder = 'name'

    name = UnicodeCol()
    foos = MultipleJoin('Foo')

And then I create some data:

b1 = Bar(name='bar one')
f1 = Foo(startdate=None, bar=b1)
f3 = Foo(startdate=date(2006,1,12), bar=b1)

I can can now do:

for foo in b1.foos:
    print foo.startdate

However, in this case, since some of the records in the Foo table have a
NULL value for the startdate and since the Foo class has a default order
for startdate specified, a TypeError exception is raised (the last couple
of lines of the traceback are pasted at the end of this message).

Apparently (by looking a the traceback), the sorting on the startdate
attribute is done in SQLObject, on a Python level. There it tries to
compare a datetime value for the startdate field of one record to a None
value of another, which breaks (for string or integers this would have
worked).

I think this is an issue in SQLObject that could be solved (see below for a
possible patch), but it could very well be I do something wrong here...

Maybe I should handle this type of relation differently?


kind regards,
jw

--
Jan-Wijbrand Kolman
jw...@in...


tb:

   File
"/usr/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/joins.py",
line 136, in performJoin
     return self._applyOrderBy([self.otherClass.get(id, conn) for (id,)
in ids if id is not None], self.otherClass)
   File
"/usr/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/joins.py",
line 81, in _applyOrderBy
     results.sort(sorter(self.orderBy))
   File
"/usr/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/joins.py",
line 104, in cmper
     return cmp(getattr(a, attr), getattr(b, attr))
TypeError: can't compare datetime.date to NoneType

possible patch:

--- src/SQLObject-0.7.0-orig/sqlobject/joins.py      2005-10-02
00:59:35.000000000 +0200
+++ src/SQLObject-0.7.0/sqlobject/joins.py       2006-01-12
22:00:15.000000000 +0100
@@ -97,11 +97,21 @@
     # @@: but we don't handle more complex expressions for orderings
     if orderBy.startswith('-'):
         orderBy = orderBy[1:]
-        def cmper(a, b, attr=orderBy):
-            return cmp(getattr(b, attr), getattr(a, attr))
+        reverse = True
     else:
-        def cmper(a, b, attr=orderBy):
-            return cmp(getattr(a, attr), getattr(b, attr))
+        reverse = False
+
+    def cmper(a, b, attr=orderBy, rev=reverse):
+        a, b = getattr(a, attr), getattr(b, attr)
+        if rev:
+            a, b = b, a
+        if a is None:
+            if b is None:
+                return 0
+            return -1
+        if b is None:
+            return 1
+        return cmp(a, b)
     return cmper