Hi,
Problem is solved with last SQLAlchemy 0.7.6 !!
I was a little "affrayed" to change but only had minor incompatibilities :-)
For anybody interested, the good syntax is :
session.query(Activity, TaskAM.libelle, TaskPM.libelle) \
.outerjoin(TaskAM, Activity.task_am) \
.outerjoin(TaskPM, Activity.task_pm) \
.filter(...)
Best regards,
Thierry
2011/12/1 Thierry Florac <[email protected]>:
> 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
--
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.
