[sqlalchemy] Re: Self-referential property - Christoph Haas - com.googlegroups.sqlalchemy

4 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: Self-referential pro...

From	Sent On	Attachments
Christoph Haas	22 Aug 2007 05:27
Michael Bayer	22 Aug 2007 09:37
Christoph Haas	22 Aug 2007 11:30
Michael Bayer	22 Aug 2007 13:19

Subject:	[sqlalchemy] Re: Self-referential property
From:	Christoph Haas (ema...@christoph-haas.de)
Date:	08/22/2007 11:30:59 AM
List:	com.googlegroups.sqlalchemy

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