On Friday 03 October 2008 17:43:56 Marco De Felice wrote:
> given the structure below (pasted from SA 0.5 docs of association
> object):
>
> left_table = Table('left', metadata,
> Column('id', Integer, primary_key=True),
> Column('l_data', String(50))
>
> right_table = Table('right', metadata,
> Column('id', Integer, primary_key=True),
> Column('r_data', String(50))
>
> association_table = Table('association', metadata,
> Column('left_id', Integer, ForeignKey('left.id'),
> primary_key=True), Column('right_id', Integer,
> ForeignKey('right.id'), primary_key=True), Column('a_data',
> String(50))
> )
>
> mapper(Parent, left_table, properties={
> 'children':relation(Association, backref="parent")
> })
>
> mapper(Association, association_table, properties={
> 'child':relation(Child, backref="parent_assocs")
> })
>
> mapper(Child, right_table)
>
>
> what I would like to do is query the Association object while
> filtering on parent_data and child_data and ordering the union, see
> below for a pure sql version, but how in orm (if possible)?
>
> select id as id, l_data as data_ass, a_data as data
> from association left outer join left
> where l_data = 'filtervalue'
> UNION
> select id as id, r_data as data_ass, a_data as data
> from association left outer join right
> where r_data = 'filtervalue'
> order by data
i've found two ways (see recent post on "unions in query")
- form the union() from 2 selects/joins on table level then give it
to session.query(Assoc).selectfrom( theunion )
- make it via 2 separate queries on orm level, get the result selects
as query._compile_context().statement, then union(), then as above.
in both the result query(Assoc) can have further
filters/joins/order/...
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
