How to link one table to itself?

Question

Hello,

I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many:

media_group_groups = Table(
            "media_group_groups",
            metadata,
            Column("groupA_id", Integer, ForeignKey("media_groups.id")),
            Column("groupB_id", Integer, ForeignKey("media_groups.id"))
        )

class MediaGroup(rdb.Model):
    """Represents MediaGroup class. Conteins channels and other media groups"""
    rdb.metadata(metadata)
    rdb.tablename("media_groups")

    id = Column("id", Integer, primary_key=True)
    title = Column("title", String(100))
    parents = Column("parents", String(512))

    channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref="media_groups")
    mediaGroup = relationship("MediaGroup", secondary=media_group_groups, order_by="MediaGroup.title", backref="media_groups")

I got this error:

"ArgumentError: Could not determine join condition between parent/child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well."

When I create the tables I don't get any error, it's just when I add any element to it. Any idea???

Thanks in advance!

Answer 1

I could be wrong, but I don't think you can link a table to itself many-to-many, can you?

You'd need to store the links in your records, and those would potentially need to be able to link to every other row in the table, no? Would seem that your columns would need to expand as fast as your records in that case.

What about a separate table that just contains your relationship?

tbl_links (id, primaryMediaGroupID, linkedMediaGroupID)

Answer 2

SQLAlchemy can't figure out which columns in your link table to join on. Try this for the relationship:

mediaGroup = relationship("MediaGroup",
        secondary=media_group_groups,
        order_by="MediaGroup.title",
        backref=backref('media_groups', 
                        secondary="media_media_groups",
                        primaryjoin= id == "groupB_id",
                        secondaryjoin = id == "groupA_id",
                        foreignkeys = ["groupA_id", "groupB_id"] ),  
        primaryjoin = id == "groupA_id",
        secondaryjoin = id == "groupB_id")

This may need some adjustment -- if it doesn't work, try with the join column names being like "media_media_groups.groupA_id" throughout.