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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
