I'm trying to create a subselect that has a where condition using an identically named column as the outer select. I'm not able to figure out how to tell SQLAlchemy that I need two parameters--one for each query. Here is what my SQL would look like if I wrote it by hand:
SELECT signals.* FROM module_outputs WHERE module_id = :module_id AND signal_id NOT IN ( SELECT signal_id FROM module_inputs WHERE module_id = :module_id) This is how the joining tables are defined: self.module_inputs = sqla.Table('module_inputs', metadata, sqla.Column('module_id', sqla.Integer, sqla.ForeignKey('modules.module_id'), nullable = False), sqla.Column('signal_id', sqla.Integer, sqla.ForeignKey('signals.signal_id'), nullable = False), sqla.PrimaryKeyConstraint('module_id', 'signal_id') ) self.module_outputs = sqla.Table('module_outputs', metadata, sqla.Column('module_id', sqla.Integer, sqla.ForeignKey('modules.module_id'), nullable = False), sqla.Column('signal_id', sqla.Integer, sqla.ForeignKey('signals.signal_id'), nullable = False), sqla.PrimaryKeyConstraint('module_id', 'signal_id') ) Below is some code that attempts to pass the necessary parameters. I'd like to place both parameters in the execute() line, but it seems I may need alias one or both parameters. So far, I'm stumped on what to and what I'm doing wrong. db = model.db.tables # Construct the subselect not_in_join = sql.join(db.signals, db.module_outputs) not_in_sel = sql.select([db.signals.c.signal_id], db.module_outputs.c.module_id == m.module_id, from_obj=[not_in_join]) # Construct the primary query to return signal_ids sel = sql.select([db.signals.c.signal_id], sql.not_(db.signals.c.signal_id.in_(not_in_sel)), from_obj=[sql.join(db.signals, db.module_inputs)]) # >>> print sel # SELECT signals.signal_id # FROM signals # JOIN module_inputs ON signals.signal_id = # module_inputs.signal_id # WHERE signals.signal_id NOT IN ( # SELECT signals.signal_id AS signal_id # FROM signals # JOIN module_outputs ON signals.signal_id = # module_outputs.signal_id # WHERE module_outputs.module_id = ?) # The above generated query would be fine if execute() would work. id_res = sel.execute(module_id = m.module_id) # This does not add an extra # AND module_inputs.module_id = ? sel = sql.select([db.signals.c.signal_id], sql.and_(sql.not_(db.signals.c.signal_id.in_(not_in_sel)), db.module_inputs.c.module_id == m.module_id), from_obj=[sql.join(db.signals, db.module_inputs)]) # The above doesn't work either --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---