you can probably do it in all versions including 0.5 just using two separate
outerjoin calls:
outerjoin(Activity.task_am).\
outerjoin(Activity.task_pm)
each new call to outerjoin() starts back from the parent Activity.
the TaskAM/TaskPM targets can be implicit there.
On Dec 1, 2011, at 11:12 AM, Thierry Florac wrote:
> 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 <tflo...@gmail.com>:
>> 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 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.
>
--
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.
