Materialized Key in SQLAlchemy

often when you have a table with selfrefernece, say in a tree, you have an attribute as a foreign key, which is pointing to the primary key of the table. (like in the Adjacency List Relationships example from the docs)

However, I have a natural key, which works like this: "" is the root

• "a", "b", "c" etc. are the children of ""
• "aa", "ab", "ac" etc. are the children of "a"
• "ba", "bb", "bc" etc. are the children of "b"
• "aaa", "aab", "aac" etc. are the children of "aa"

So there is one character for each level in the tree and the child node开发者_运维百科s of each node are those with the same beginning and one character added. This is called a "Materialized Key"

How would I do this using an SQLAlchemy mapper without adding an extra attribute to reference the parent?

Note: I'm only interested in reading the relationship, if something like node.children.append(child) isn't working thats fine. Also I'm stuck to version 0.4.8, however if this is not possible in this version but only in a newer one I might take the effort to update.

EDIT I've gotten a first answer on the SA mailinglist. It works, but it's not mapper-based as I'd like it to be (e.g. to use eagerload)

I do understand the constraint of the question, but why are you trying to avoid having an extra key? Sure, it is redundant information, but depending you your data (number of row, average length of the keys), having additional keys might actually improve your performance on SELECTs and you would be able to use eager-loading of the relationships in SA in the most trivial way.

For example, you could do the following:

• Add another numeric identifier as PK and link the child/parent on this.
• Add another string identifier to point to the parent object (Key: Varchar, ParentKey: Varchar). In some RDBMS (like MSSQL) you can have a PERSISTENT COMPUTED column, so that your client does not even have to provide the parent key and it will be calculated from the KEY column automatically using the formula (cut the last character). In this case you have have a DB Index on this column and very fast relationship retrievals.