On Feb 14, 2011, at 6:06 PM, Victor Poluksht wrote:
> I have two classes
>
> table_external_numbers = sa.Table('external_numbers', meta.metadata,
> sa.Column('en_id', sa.types.Integer, primary_key = True),
> sa.Column('en_prefix', sa.types.Integer, nullable = False),
> sa.Column('en_use_called', sa.types.Boolean, nullable = False),
> )
>
> class ExternalNumbers(object): pass
> orm.mapper(ExternalNumbers, table_external_numbers)
>
> and
>
> table_gateways = sa.Table('gateways', meta.metadata,
> sa.Column('gw_id', sa.types.Integer, primary_key = True),
> sa.Column('gw_active', sa.types.Boolean, nullable = False),
> sa.Column('gw_server', sa.types.Unicode(64), nullable = True),
> sa.Column('gw_maxlines', sa.types.Integer, nullable = False),
> sa.Column('gw_curlines', sa.types.Integer, nullable = False)
> )
>
> class Gateways(object): pass
> orm.mapper(Gateways, table_gateways, properties = {'external_numbers':
> orm.relation(ExternalNumbers, secondary = table_gateways_external_numbers,
> backref = 'gateways')})
>
> and also many-to-many relation table
> table_gateways_external_numbers = sa.Table('gateways_external_numbers',
> meta.metadata,
> sa.Column('gw', sa.types.Integer, sa.ForeignKey('gateways.gw_id'),
> nullable = False),
> sa.Column('en', sa.types.Integer, sa.ForeignKey('external_numbers.en_id'),
> nullable = False)
> )
>
> I need to select all records from both tables (gateways, external_numbers)
> joined but only that one, that have relation.
>
> The raw query looks like:
>
> select * from external_numbers e, gateways g, gateways_external_numbers ge
> WHERE
> ge.en = e.en_id AND
> ge.gw = g.gw_id AND
> (e.en_prefix = 100 and g.gw_active is TRUE and g.gw_server =
> 'XXX.XXX.XXX.XXX') OR
> (e.en_prefix = 101 and g.gw_active is TRUE and g.gw_server =
> 'YYY.YYY.YYY.YYY')
>
> I have trouble writing a sqlalchemy query doing the same.
you want to look into and_ and or_() for this.
e = ExternalNumbers
g = Gateways
ge = table_gateways_external_numbers
session.query(e, g, ge).filter(ge.en == e.en_id).filter(ge.gw==g.gw_id).filter(
or_(
and_(e.en_prefix==100, g.gw_active==True, g.gw_server == '...'),
and_(e.en_prefix==101, g.gw_active==True, g.gw_server == '...'),
)
)
>
> --
> 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.
