wrong outerjoin results

The following example shows an outerjoin that return wrong results. The SQL query is correct but the result in terms of return python instances is incorrect. The first instance is None and the only return instance correspond to the row that would be present in a simple join.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, ForeignKey, text, func
from sqlalchemy.types import *
from sqlalchemy.orm import relation, sessionmaker, mapper, column_property
from sqlalchemy.orm.interfaces import SessionExtension
from datetime import datetime, timedelta

Base = declarative_base()
Base.metadata.bind = 'sqlite://'
Session = sessionmaker(bind=Base.metadata.bind)
sess = Session()

class Entry(Base):
    __tablename__ = 'calendar_entry'  # Todo and Events
    id = Column(Integer, primary_key=True)
    summary = Column(String(100))
    dtstart   = Column(DateTime(timezone=False), nullable=False,
index=True)

class Alarm(Base):
    __tablename__ = 'calendar_alarm'

    ida = Column(Integer, primary_key=True)
    trigger  = Column(Interval, nullable=False)

    # o2m
    entry_id = Column(ForeignKey(Entry.id), nullable=False)
    entry = relation(Entry, backref='alarm', lazy=True)

Base.metadata.create_all()

e1 = Entry(summary="sum1", dtstart=datetime.now())
e2 = Entry(summary="sum2", dtstart=datetime.now())
a = Alarm(trigger=timedelta(days=1))
ea1 = Entry(summary="entry w/ alarm", dtstart=datetime.now(),alarm=[a](a))


for e in (e1, e2, ea1, a):
   sess.add(e)

sess.commit()

entry_table = Entry.__table__
alarm_table = Alarm.__table__

class MyJoin(object):
   def __str__(self):
       return "%s" % self.dtstart

m = mapper(MyJoin, entry_table.outerjoin(alarm_table), )


q = sess.query(m)
print q.count()
for r in q.all():
    print r

That return the following:

3     # count for the matches
None    ???
2009-04-19 20:34:04.188442  # the only joined entry (entry w/ alarm)

Comments (1)