self referencing many-to-many association object

[bika]

Hi,

I could someone help me in putting together a self-referencing association object?

The docs write about self-referencing many2many and association objects separately, but I simply can't figure out how merge the two techniques (for example one uses declarative, the other doesn't)

This is what I've tried:

class Character(Base):
    __tablename__ = "characters"

    id = Column(Integer, primary_key=True)
    name = Column(Unicode, nullable=False)
    sex = Column(Enum("male", "female"))

    sympathy_list = relationship("Sympathylist",
            backref="loved_by")

class Sympathylist(Base):
    __tablename__ = "sympathylists"

    character_id = Column(Integer, ForeignKey("Character.id"), nullable=False,
            primary_key=True)
    sympathic_id = Column(Integer, ForeignKey("Character.id"), nullable=False,
            primary_key=True)
    order = Column(Integer, nullable=False)

    sympathy_list = relationship("Character", backref="loves_me")

But this gives

Foreign key associated with column 'sympathylists.character_id' could not find table 'Character' with which to generate a foreign key to target column 'id'

I've tried the non-declarative way too, and thought to add a mapping to the table later.

sympathylist = Table("sympahtylists", Base.metadata,
    Column("character_id", Integer, ForeignKey("characters.id"), nullable=False,
            primary_key=True),
    Column("sympathic_id", Integer, ForeignKey("characters.id"), nullable=False,
            primary_key=True),
    Column("order", Integer, nullable=False))

class Character(Base):
    __tablename__ = "characters"

    id = Column(Integer, primary_key=True)
    name = Column(Unicode, nullable=False)
    sex = Column(Enum("male", "female"))
#    sympathy_ids = Column(Integer, ForeignKey("Character.id"))

    sympathy_list = relationship("Character",
            secondary=sympathylist,
            primaryjoin=sympathylist.c.character_id,
            secondaryjoin=sympathylist.c.sympathic_id,
            backref="loved_by")

but this fails with

sqlalchemy.exc.ArgumentError: Could not determine relationship direction for primaryjoin condition 'sympahtylists.character_id', on relationship Character.sympathy_list. Ensure that the referencing Column objects have a ForeignKey present, or are otherwise part of a ForeignKeyConstraint on their parent Table, or specify the foreign_keys parameter to this relationship.

thanks for any help

[Michael Bayer]

ForeignKey accepts the name of the table in its string argument, hence the error "could not find *table* 'Character'", so that would be ForeignKey('characters.id').

Additionally, since there is more than one way to join "characters" to "sympathylists", you'll need primaryjoin on each relationship.

Here's all that:

class Character(Base):

    __tablename__ = "characters"

    id = Column(Integer, primary_key=True)

    name = Column(Unicode, nullable=False)

    sex = Column(Enum("male", "female"))

    sympathy_list = relationship("Sympathylist", backref="loved_by", 

            primaryjoin="Character.id==Sympathylist.character_id")

class Sympathylist(Base):

    __tablename__ = "sympathylists"

    character_id = Column(Integer, ForeignKey("characters.id"), nullable=False,

            primary_key=True)

    sympathic_id = Column(Integer, ForeignKey("characters.id"), nullable=False,

            primary_key=True)

    order = Column(Integer, nullable=False)

    sympathy_list = relationship("Character", backref="loves_me", 

            primaryjoin="Character.id==Sympathylist.sympathic_id")

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.