I am using version 0.9.3. When defining a many-to-many self-referential relationship, I found that the primary key column has to be defined in the table itself. It cannot be defined in the model base or a mixin class from which this entity is derived. For example:
Given these definitions: 1 #! /usr/bin/python 2 3 from sqlalchemy.ext import declarative 4 from sqlalchemy.schema import DefaultClause, Sequence 5 import sqlalchemy as sa 6 from sqlalchemy.orm import relationship, backref, sessionmaker 7 8 class Model(object): 9 """All model classes must have table name in lower case, with underscores representing 10 camel case 11 They must have 'id' column, populated using uuid 12 Must have a version column for optimistic locking 13 """ 14 @declarative.declared_attr 15 def __tablename__(cls): 16 return cls.__name__.lower() 17 18 id = sa.Column('id', sa.Integer, Sequence('id_seq'), primary_key=True) 19 20 21 ModelBase = declarative.declarative_base(cls=Model) 22 23 24 class VersionColumnMixin(object): 25 version = sa.Column('instance_version', sa.INTEGER, nullable=False) 26 @declarative.declared_attr 27 def __mapper_args__(cls): 28 mapper_dict = { 29 'version_id_col': cls.version 30 } 31 return mapper_dict 32 33 34 # many-to-many between Groups and Groups 35 groups_and_groups_assoc_table = \ 36 sa.Table('groups_and_groups', ModelBase.metadata, 37 sa.Column('parent_group_id', sa.Integer, sa.ForeignKey('groups.id'), 38 primary_key=True), 39 sa.Column('child_group_id', sa.Integer, sa.ForeignKey('groups.id'), 40 primary_key=True) 41 ) 42 43 44 45 class Groups(ModelBase, VersionColumnMixin): 46 *id = sa.Column('id', sa.Integer, Sequence('id_seq'), primary_key=True)* 47 domain_id = sa.Column(sa.String(36), DefaultClause('default'), nullable=False) 48 description = sa.Column(sa.Text) 49 date_created = sa.Column(sa.DateTime) 50 group_name = sa.Column(sa.String(64), nullable=False, index=True) 51 child_groups = relationship('Groups', secondary=groups_and_groups_assoc_table, 52 primaryjoin=id==groups_and_groups_assoc_table.c.parent_group_id, 53 secondaryjoin=id==groups_and_groups_assoc_table.c.child_group_id, 54 backref='parent_groups') 55 __table_args__ = (sa.UniqueConstraint('domain_id', 'group_name'),) 56 57 58 if __name__ == '__main__': 59 60 engine = sa.create_engine('postgresql://admin@localhost:5435/testdb', echo="debug") 61 62 print 'Creating database tables' 63 ModelBase.metadata.create_all(engine) 64 65 Session = sessionmaker(bind=engine) 66 sess = Session() 67 68 g1 = Groups(description='1234', group_name='group1') 69 70 c1_g1 = Groups(description='c1_1234', group_name='c1_group1') 71 c2_g1 = Groups(description='c2_1234', group_name='c2_group1') 72 g1.child_groups = [c1_g1, c2_g1] 73 74 sess.add(g1) 75 sess.commit() If the 'id' column definition (highlighted in *bold red italics* above at line 46 is commented out), but inherited via the ModelBase class, I get this error when running the test code: Traceback (most recent call last): File "./selfref4.py", line 68, in <module> g1 = Groups(description='1234', group_name='group1') File "<string>", line 2, in __init__ File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/instrumentation.py", line 322, in _new_state_if_none state = self._state_constructor(instance, self) ... ... File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/relationships.py", line 2392, in _check_foreign_cols raise sa_exc.ArgumentError(err) sqlalchemy.exc.ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'group_and_group.parent_group_id = :parent_group_id_1' on relationship Groups.child_groups. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. However, the many-to-many relationship works when the 'id' column is inherited from the model base as long as the relationship is between two different classes. I do not like to define the 'id' column in every model class, instead they all should get their definitions from the model base. What could be wrong in the definitions above? Any help is appreciated. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.