[sqlalchemy] Outer join question

[Thierry Florac]

Hi,

I have a little problem with outer joins (I use QSLAlchemy 0.5.6).

I have two tables, managing tasks and activities :

class Task(Base):
    __tablename__ = 'tache'

    id = Column(Integer, Sequence('seq_tache_id'), primary_key=True)
    libelle = Column(Unicode(50))
    description = Column(Unicode(4000))
    ...

class Activity(Base):
    __tablename__ = 'activite'

    id_ressource = Column(Integer, ForeignKey(Resource.id))
    date_realisation = Column(Date)
    id_tache_am = Column(Integer, ForeignKey(Task.id))
    id_tache_pm = Column(Integer, ForeignKey(Task.id))

    __table_args__ = (
        PrimaryKeyConstraint('id_ressource', 'date_realisation'),
        {}
    )

Activity.task_am = relation(Task, primaryjoin=Activity.id_tache_am ==
Task.id, backref='activity_am')
Activity.task_pm = relation(Task, primaryjoin=Activity.id_tache_pm ==
Task.id, backref='activity_pm')


My problem is that I want to select activities and, for each of them,
their related AM and PM tasks labels (which can be null), as follow :

        TaskAM = aliased(Task)
        TaskPM = aliased(Task)
        for activity, libelle_am, libelle_pm in
session.query(Activity, TaskAM.libelle, TaskPM.libelle) \

.outerjoin(TaskAM.activity_am, TaskPM.activity_pm) \

.filter(and_(Activity.id_ressource == User.getCurrentUser().id,

Activity.date_realisation.between(start_date, end_date))):
            ...

The generated SQL query is as follow :

    SELECT projetsdi.activite.id_ressource AS
projetsdi_activite_id_re_1, ..., tache_1.libelle AS tache_1_libelle,
tache_2.libelle AS tache_2_libelle
    FROM projetsdi.tache tache_2, projetsdi.tache tache_1 LEFT OUTER
JOIN projetsdi.activite ON projetsdi.activite.id_tache_am = tache_1.id
    WHERE projetsdi.activite.id_ressource = :id_ressource_1 AND
projetsdi.activite.date_realisation BETWEEN :date_realisation_1 AND
:date_realisation_2

So this query only selects activities for which id_tache_am is defined !!
The "good" query should be something like :

    SELECT ...
    FROM projetsdi.activite
        LEFT OUTER JOIN projetsdi.tache tache_1 ON
projetsdi.activite.id_tache_am = tache_1.id
        LEFT OUTER JOIN projetsdi.tache tache_2 ON
projetsdi.activite.id_tache_pm = tache_2.id

Any idea about how to get such a result ??

Many thanks,
Thierry
-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.