SQLAlchemy recursive many-to-many relation

Question

Heya!

I've a case where I'm using one table to store user and group related datas. This column is called profile. So, basically this table is many-to-many table for the cases where one user is belonging in to many groups or there are many users in one group.

I'm a bit confused how it should be described...

Here's a simplified presentation of the class.

Entity relationship model

user_group_table = Table('user_group', metadata,
Column('user_id', Integer,ForeignKey('profiles.id',
    onupdate="CASCADE", ondelete="CASCADE")),
Column('group_id', Integer, ForeignKey('profiles.id',
    onupdate="CASCADE", ondelete="CASCADE"))
)

class Profile(Base)
  __tablename__ = 'profiles'

  id = Column(Integer, autoincrement=True, primary_key=True)
  name = Column(Unicode(16), unique=True) # This can be either user- / groupname
  groups = relationship('Profile', secondary=user_group_table, backref = 'users')
  users = relationship('Profile', secondary=user_group_table, backref = 'groups')

#Example of the usage:
user = Profile()
user.name = 'Peter'

salesGroup = Profile()
salesGroup.name = 'Sales'

user.groups.append(salesGroup)

salesGroup.users
>[peter]

Answer 1

First of all, I agree with Raven's comment that you should use separate tables for Users and Groups. The reason being that you might get some inconsistent data where a User might have other Users as its users relations, as well as you might have cycles in the relationship tree.

Having said that, to make the relationship work declare it as following:

...
class Profile(Base):
    __tablename__ = 'profiles'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True) # This can be either user- / groupname

Profile.groups = relationship('Profile', 
    secondary=user_group_table,
    primaryjoin=(user_group_table.c.user_id==Profile.__table__.c.id),
    secondaryjoin=(user_group_table.c.group_id==Profile.__table__.c.id),
    backref = 'users')
...

Also see Specifying Alternate Join Conditions to relationship() documentation section.