How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?

by

Here’s the UNION approach I hinted at on the mailing list earlier today.

from sqlalchemy import Integer, Table, Column, ForeignKey, \
    create_engine, String, select
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

friendship = Table(
    'friendships', Base.metadata,
    Column('friend_a_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True),
    Column('friend_b_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True)
)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    # this relationship is used for persistence
    friends = relationship("User", secondary=friendship, 
                           primaryjoin=id==friendship.c.friend_a_id,
                           secondaryjoin=id==friendship.c.friend_b_id,
    )

    def __repr__(self):
        return "User(%r)" % self.name

# this relationship is viewonly and selects across the union of all
# friends
friendship_union = select([
                        friendship.c.friend_a_id, 
                        friendship.c.friend_b_id
                        ]).union(
                            select([
                                friendship.c.friend_b_id, 
                                friendship.c.friend_a_id]
                            )
                    ).alias()
User.all_friends = relationship('User',
                       secondary=friendship_union,
                       primaryjoin=User.id==friendship_union.c.friend_a_id,
                       secondaryjoin=User.id==friendship_union.c.friend_b_id,
                       viewonly=True) 

e = create_engine("sqlite://",echo=True)
Base.metadata.create_all(e)
s = Session(e)

u1, u2, u3, u4, u5 = User(name="u1"), User(name="u2"), \
                    User(name="u3"), User(name="u4"), User(name="u5")

u1.friends = [u2, u3]
u4.friends = [u2, u5]
u3.friends.append(u5)
s.add_all([u1, u2, u3, u4, u5])
s.commit()

print u2.all_friends
print u5.all_friends

More Related Contents:

  1. SQLAlchemy ManyToMany secondary table with additional fields
  2. How to serialize SqlAlchemy result to JSON?
  3. How to execute raw SQL in Flask-SQLAlchemy app
  4. How do I access the child classes of an object in django without knowing the name of the child class?
  5. SQLAlchemy: print the actual query
  6. SQLAlchemy create_all() does not create tables
  7. Query for list of attribute instead of tuples in SQLAlchemy
  8. How to give column name dynamically from string variable in sql alchemy filter?
  9. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  10. SQL Alchemy ORM returning a single column, how to avoid common post processing
  11. How do I execute inserts and updates in an Alembic upgrade script?
  12. SQLAlchemy, get object not bound to a Session
  13. Bulk insert with SQLAlchemy ORM
  14. How can I verify Column data types in the SQLAlchemy ORM?
  15. Flask sqlalchemy many-to-many insert data
  16. How to delete a record by id in Flask-SQLAlchemy
  17. Connection problems with SQLAlchemy and multiple processes
  18. How to upsert pandas DataFrame to Microsoft SQL Server table?
  19. How to do an upsert with SqlAlchemy?
  20. SQLAlchemy printing raw SQL from create()
  21. SQLAlchemy: engine, connection and session difference
  22. Relations on composite keys using sqlalchemy
  23. SQLAlchemy: how to filter date field?
  24. method of iterating over sqlalchemy model’s defined columns?
  25. flake8 complains on boolean comparison “==” in filter clause
  26. How to execute “left outer join” in SqlAlchemy
  27. SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)
  28. Can’t change system default SQLite binary
  29. SQLAlchemy column name with space
  30. Connecting to SQL Server 2012 using sqlalchemy and pyodbc

Leave a Comment