On Jun 18, 2007, at 3:34 PM, Christoph Haas wrote:
> 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?
primary joins of all kinds are supported. however the target column
of the primary join, i.e. the one SA needs to be able to set values
within, has always been expected to be a Column object, not a CAST.
this is just something that nobody has tried to do before. my only
guess of what to try right now, i dont think will work. its:
mycast = cast(powerdns_records_table.c.content,PGInet)
assign_mapper(session_context, DnsRecord, powerdns_records_table,
properties={
'dhcphost': relation(DhcpHost,
primaryjoin=(mycast==dhcp_hosts_table.c.ip_address),
foreign_keys=[mycast]),
}
)
the other is to place the CAST on the other side, i.e. cast
dhcp_hosts_table.c.ip_address as a string, since thats not the
"foreign key" side of things. but this also might create problems
with lazy loaders.
ticket 610 is added for this, and it may be a 5 minute fix or it
might be an involved refactoring. for now you might try using a
property-based loader for this relationship.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
