On Wed, Aug 22, 2007 at 12:37:32PM -0400, Michael Bayer wrote:
>
>
> On Aug 22, 2007, at 8:27 AM, Christoph Haas wrote:
>
> >
> > But that example deals with User and Address tables and not with
> > self-references. I suspect I have to alias the table. Roughly I'm
> > thinking of something like:
> >
> > properties={
> > 'ptr_records': relation(Record, primaryjoin=and_(
> > records_table.c.type=='PTR',
> > records_table.c.inet=records_table2.c.inet
> > ))
> > }
> >
> > I don't know how to say "match other Record objects where the 'inet'
> > column contains the same value". How do I do that correctly?
>
> Well the issue here is that while its a self-referential relationship
> you're dealing with, its not an "adjacency list" model, which is
> really what our "self-referential" relation() is designed to do.
Understood. That's the easy/supported case that I will need at a later
time. :)
> Normally you can use the "remote_side" attribute to indicate
> whichever column on the join condition is "remote", but here its the
> same column.
>
> Doing the alias thing is possibly a solution. it would look like this:
>
> records2 = records.alias()
>
> rmapper = mapper(Record, records2, non_primary=True)
>
> 'ptr_records':relation(rmapper, primaryjoin=and_(
> records2.c.type=='PTR', records.c.inet=records2.c.inet),
> viewonly=True)
> )
>
> the above should work in theory but I havent tried it.
> notice that the records2 alias indicates the "child" side of the
> relationship so its the one which gets the "PTR" criterion.
> "viewonly=True" is to eliminate whatever issues arise in calculating
> the persistence aspect of the relation since you only need loading
> here.
Tried it. It raises this error:
ArgumentError: Can't locate any foreign key columns in primary join
condition 'records_1.type = :records_1_type AND records.inet =
records_1.inet' for relationship 'Record.ptr_records (Record)'.
Specify 'foreign_keys' argument to indicate which columns in the
join condition are foreign.
So I added:
foreign_keys=[records_table2.c.inet]
The error disappeared. But getting the joined PTR records for a certain
inet took very long. So I analyzed the query that SQLAlchemy did:
SELECT records.id AS records_id, records.domain_id AS
records_domain_id, records.dhcpzone_id AS records_dhcpzone_id,
records.name AS records_name, records.type AS records_type,
records.content AS records_content, records.ttl AS records_ttl,
records.prio AS records_prio, records.change_date AS
records_change_date, records.mac_address AS records_mac_address,
records.inet AS records_inet
FROM records, records AS records_1
WHERE records_1.type = %(records_1_type)s AND %(param_2)s =
records_1.inet ORDER BY records.id
{'records_1_type': 'PTR', 'param_2': '10.20.30.40'}
The "FROM records, records AS records_1" looks suspiciously like a
cartesian product. And I get all possible results - not just rows with
type=='PTR'. The "childs" from records_1 have the right criteria. But
I get all "records". :(
I'm not sure if JOIN is the right paradigm here. Usually I'd use a
SELECT. But "select" in SQLAlchemy's world doesn't seem to return mapped
objects but rather plain rows. I assume it's not supported to add a
"filter()" statement as a property?
Perhaps you have another hint. Would be a pity to give up and use my
dirty @property method. Thanks so far.
Christoph
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
