I'm having difficulty with table joins not using my foreign keys. A corrected query would produce "modules.module_id = module_extra_keys.module_id" in the WHERE clause. But in the below test code, it does not. However, if I use the get_by() function of the data_mapper the SQL is correct. Clearly, I'm doing something wrong....but I've not figured it out yet. Help....
import sqlalchemy as sqla db_con = sqla.create_engine('sqlite:///:memory:') metadata = sqla.BoundMetaData(db_con) modules = sqla.Table('modules', metadata, sqla.Column('module_id', sqla.Integer, primary_key = True), sqla.Column('module_name', sqla.String(25), nullable = False, unique = True), sqla.UniqueConstraint('module_name', name = 'modules_idx1') ) module_extra_keys = sqla.Table('module_extra_keys', metadata, sqla.Column('module_extra_key_id', sqla.Integer, nullable = False, primary_key = True), sqla.Column('module_id', sqla.Integer, sqla.ForeignKey('modules.module_id'), nullable = False), sqla.Column('module_extra_key_name', sqla.String(25), nullable = False), sqla.UniqueConstraint('module_id', 'module_extra_key_name', name = 'module_extra_key_idx1') ) metadata.create_all() session = sqla.create_session(db_con) class Module(object): "Model class for the Modules table" def __init__(self, name): self.module_name = name class Module_extra_key(object): "Model class for Module_extra_keys table" def __init__(self, name): self.module_extra_key_name = name # Table modules # Primary Key: module_id module_mapper = sqla.mapper(Module, modules) # Table module_extra_keys # Primary Key: module_extra_key_id # Foreign Key: modules(module_id) module_extra_keys_mapper = sqla.mapper(Module_extra_key, module_extra_keys) module_mapper.add_property('extra_keys', sqla.relation(Module_extra_key, order_by = module_extra_keys.c.module_extra_key_name)) module_extra_keys_mapper.add_property('module', sqla.relation(Module)) ########################################################################### # Add database data module_1 = Module('module_one') module_2 = Module('module_two') module_2.extra_keys.append(Module_extra_key('key1')) module_2.extra_keys.append(Module_extra_key('key2')) session.save(module_1) session.save(module_2) session.flush() query = session.query(Module_extra_key) res = query.select_by(modules.c.module_name == 'module_two', module_extra_keys.c.module_extra_key_name == 'key1') assert(res[0].module_extra_key_name == 'key1') # Passes # This doesn't produce a query to produce a correct result res = query.select_by(modules.c.module_name == 'module_one', module_extra_keys.c.module_extra_key_name == 'key1') assert(res == []) # FAILS !!! k = session.query(Module_extra_key).get_by(module_name = 'module_two', \ module_extra_key_name = 'key1') assert(k.module_extra_key_name == 'key1') # Passes k = session.query(Module_extra_key).get_by(module_name = 'module_one', \ module_extra_key_name = 'key1') assert(k == None) # Passes session.flush() --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---