Dear list... I'm having trouble with two assign-mappers with a custom JOIN condition. (And I admit that I'm not yet good at that in SQLAlchemy. So be gentle.)
powerdns_records_table = Table( 'records', meta, Column('id', Integer, primary_key=True), Column('name', Unicode(80)), Column('type', Unicode(10)), Column('content', Unicode(200)), ) dhcp_hosts_table = Table( 'dhcp_hosts', meta, Column('id', Integer, primary_key=True), Column('ip_address', PGInet, nullable=False), ) assign_mapper(session_context, DhcpHost, dhcp_hosts_table) assign_mapper(session_context, DnsRecord, powerdns_records_table, properties={ 'dhcphost': relation(DhcpHost, primaryjoin=(cast(powerdns_records_table.c.content,PGInet)==dhcp_hosts_table.c.ip_address), foreign_keys=[powerdns_records_table.c.content]), } ) Yes, this may look a bit dirty. The JOIN condition is really that and I have no chance to alter the table schemas because that's what another application demands. The powerdns_records_table uses a string and dhcp_hosts_table uses a PostgreSQL "inet" object (defined in SQLAlchemy as types.PGInet) and I am trying to match those. I added the "foreign_keys" parameter when SQLAlchemy complained: ArgumentError: Can't locate any foreign key columns in primary join condition 'CAST(records.content AS INET) = dhcp_hosts.ip_address' for relationship 'DnsRecord.dhcphost (DhcpHost)'. Specify 'foreign_keys' argument to indicate which columns in the join condition are foreign. Now when I try DnsRecords.get_by(content='10.0.0.1') I get: ArgumentError: No syncrules generated for join criterion CAST(records.content AS INET) = dhcp_hosts.ip_address When I try that again (or use DnsRecords.select(DnsRecords.c.content='10.0.0.1') then I get: AttributeError: 'PropertyLoader' object has no attribute 'strategy' The actual SQL query I'd expect would be: SELECT * FROM records JOIN dhcp_hosts ON CAST(records.content AS inet)=dhcp_hosts.ip_address WHERE records.content='10.0.0.1'; My SQLAlchemy version is 0.3.7. Any idea what might be the problem? If that join condition is generally not supposed to work in SA I'll have to do that matching manually in my Python code (which I expect to be way slower than pure SQL). Are primaryjoins with less than trivial integer unique IDs unsupported? Christoph --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---