SqlAlchemy: Self Referential Many To Many

Ok this was just plain annoying to figure out. A couple of misteps and a few F—! later I finally got it. So the situation is this:

You have a table for user to ignore users. So it’s basically a many to many where both sides of the relationship are the user table. The creation might look like this:

class User(Base):
    __tablename__ = "User"
    id = Column(String(36), primary_key=True, default=lambda : str(uuid1()))

Basically a table named User and it has a primary key. Yee haw. Now for the hanging table:

class UserIgnore(Base):
    __tablename__ = "UserIgnore"
    id = Column(String(36), primary_key=True, default=lambda : str(uuid1()))

    ignored_by_id = Column("ignored_by_id", String(36), ForeignKey("User.id"))
    ignored_by = relationship("User", backref="ignored_list",  primaryjoin=(User.id == ignored_by_id))
    ignored_id = Column("ignored_id", String(36), ForeignKey("User.id"))
    ignored = relationship("User", backref="ignored_by_list",  primaryjoin=(User.id == ignored_id))

Basically you need a table/object with two columns: ignored_by_id (The one doing the ignoring) and ignored_id (The one being ignored). And at this point the word “ignored” should look like it’s not even a word anymore because I’ve typed it too much already.

Now the relationship is made using two different properties:

    ...
    ignored_by = relationship("User", backref="ignored_list",  primaryjoin=(User.id == ignored_by_id))
    ...
    ignored = relationship("User", backref="ignored_by_list",  primaryjoin=(User.id == ignored_id))

As you can see, I had to hit it from both angles. (I’d hit it! LOLOLOLHARHARORAALROARH) Not only how to describe the relationship from ignorer to ignoree (Those are words now) but also in reverse. That way I can have a constructor like this:

    def __init__(self, user, userToIgnore):
        self.ignored_by = user
        self.ignored = userToIgnore

And also if I do something like this:

    UserIgnore(_user, _user2)
    UserIgnore(_user2, _user)

I can have it all save correctly just by adding _user to the session or saving _user id at some point.

Also both those lists will be added to the User object without having to do anything further.

someUser.ignored_list

or

someUser.ignored_by_list

Yay for that.