SQLAlchemy Self-Referential Many to Many Symmetric Relationship

Question

I am using python 2.7 with SQLAlchemy, and trying to model a friendship relation with a many to many relationship.

I need the table to be completely symmetric; if A is B's friend, then it must be the other way around also.

I've tried to model the relationship with secondary friendship table, and connect it to the model using primary- and secondaryjoin, but I'm starting to get the feeling that I'm going in the wrong direction.

I found this post where someone tried to model the same thing using a one to many relationship, but this does not work for me, because my friendship relationship is not a one to many.

I have managed to achieve a working model using many to many table, if I am kipping a "duplicate": when I want to add B as A's friend, I add A as B's friend too. But I feel that the proposed solution should be more neat.

The end game here is similar to Facebook's friendship modeling. A can only be B's friend if B is A's friend.

Answer 1

The first attempt of using custom primary- and secondaryjoin conditions could be augmented with a composite "secondary", which in this case would be a union of both possible ways of selecting from the association table. Given a toy user model such as

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    email = Column(Unicode(255), unique=True)

the association table could look like

friendship = Table(
    "friendship", Base.metadata,
    Column("left_id", ForeignKey("user.id"), primary_key=True),
    Column("right_id", ForeignKey("user.id"), primary_key=True))

and the composite "secondary"

friends = select([friendship.c.left_id.label("left_id"),
                  friendship.c.right_id.label("right_id")]).\
    union_all(select([friendship.c.right_id,
                      friendship.c.left_id])).\
    alias("friends")

Using the above, User model would define the relationship as

User.friends = relationship(
    "User", secondary=friends,
    primaryjoin=User.id == friends.c.left_id,
    secondaryjoin=User.id == friends.c.right_id,
    viewonly=True)

The unfortunate side effect is that the relationship is readonly and you'd have to manually insert rows to friendship to make users friends. Also there's the issue of duplicates, because friendship could still contain both (1, 2) and (2, 1), for example. Adding a check constraint that enforces an ordering on left and right id tackles the duplicate issue:

# NOTE: This has to be done *before* creating your tables. You could also
# pass the CheckConstraint as an argument to Table directly.
chk = CheckConstraint(friendship.c.left_id < friendship.c.right_id)
friendship.append_constraint(chk)

The application would have to order the ids upon insertion, though. To remedy this the union used as "secondary" could be hidden in a writable view. SQLAlchemy does not have a construct for handling views out of the box, but there's a usage recipe for just that. Using the recipe friends becomes:

friends = view(
    "friends",
    Base.metadata,
    select([friendship.c.left_id.label("left_id"),
            friendship.c.right_id.label("right_id")]).\
        union_all(select([friendship.c.right_id,
                          friendship.c.left_id])))

and to make the view writable some triggers are required:

# For SQLite only. Other databases have their own syntax for triggers.
DDL("""
    CREATE TRIGGER friends_insert_trg1 INSTEAD OF INSERT ON friends
    WHEN new.left_id < new.right_id
    BEGIN
        INSERT INTO friendship (left_id, right_id)
        VALUES (new.left_id, new.right_id);
    END;
    """).execute_at("after-create", Base.metadata)

DDL("""
    CREATE TRIGGER friends_insert_trg2 INSTEAD OF INSERT ON friends
    WHEN new.left_id > new.right_id
    BEGIN
        INSERT INTO friendship (left_id, right_id)
        VALUES (new.right_id, new.left_id);
    END;
    """).execute_at("after-create", Base.metadata)

It'd be nice to bind these to the creation of the view more closely, but this will do as well, as long as you register them after defining the view. With the triggers in place you can remove the viewonly=True argument from the User.friends relationship.

Putting it all together:

from sqlalchemy import \
    Table, Column, Integer, Unicode, ForeignKey, CheckConstraint, DDL, \
    select

from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

from view import view

Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    email = Column(Unicode(255), unique=True)

friendship = Table(
    "friendship",
    Base.metadata,
    Column("left_id", ForeignKey("user.id"), primary_key=True),
    Column("right_id", ForeignKey("user.id"), primary_key=True),
    CheckConstraint("left_id < right_id"))

friends = view(
    "friends",
    Base.metadata,
    select([friendship.c.left_id.label("left_id"),
            friendship.c.right_id.label("right_id")]).\
        union_all(select([friendship.c.right_id,
                          friendship.c.left_id])))

User.friends = relationship(
    "User", secondary=friends,
    primaryjoin=User.id == friends.c.left_id,
    secondaryjoin=User.id == friends.c.right_id)

DDL("""
    CREATE TRIGGER friends_insert_trg1 INSTEAD OF INSERT ON friends
    WHEN new.left_id < new.right_id
    BEGIN
        INSERT INTO friendship (left_id, right_id)
        VALUES (new.left_id, new.right_id);
    END;
    """).execute_at("after-create", Base.metadata)

DDL("""
    CREATE TRIGGER friends_insert_trg2 INSTEAD OF INSERT ON friends
    WHEN new.left_id > new.right_id
    BEGIN
        INSERT INTO friendship (left_id, right_id)
        VALUES (new.right_id, new.left_id);
    END;
    """).execute_at("after-create", Base.metadata)