SQLAlchemy: if you are with me, I am with you - M2M self-referential relationship again

Question

I am trying to create a social network, such as matching many to many in SQLAlchemy. That is, I have a character class and a character_relations crosstab to reference from Character to Character. So far, it's easy:

character_relationships = Table('character_relationships', Base.metadata, Column('me_id', Integer, ForeignKey('characters.id', ondelete=True, onupdate=True), nullable=False, primary_key=True), Column('other_id', Integer, ForeignKey('characters.id', ondelete=True, onupdate=True), nullable=False, primary_key=True), UniqueConstraint('me_id', 'other_id', name='uix_1') ) class CharacterRelationship(object): def __init__(self, me_id, other_id): self.me_id = me_id self.other_id = other_id mapper(CharacterRelationship, character_relationships) class Character(IdMixin, TimestampMixin, Base): __tablename__ = "characters" name = Column(Unicode, nullable=False) friends = relationship(lambda: Character, secondary=character_relationships, primaryjoin=lambda: Character.id==character_relationships.c.me_id, secondaryjoin=lambda: Character.id==character_relationships.c.other_id, backref=backref('knows_me') ) 

There are two possible connections: one-way and two-way. That is, in the crosstab I can have

 CharacterRelationship(me_id=1, other_id=2) CharacterRelationship(me_id=2, other_id=1) 

The argument to Character.friends above is for one-way relationships. How can I add the / column _property property for two-way relationships?

This my_character.real_friends only contains entries from CharacterRelationship if the relationship is "on both sides."

I know I can use something like

 @property def real_friends(self): return set(my_character.friends) & set(my_character.knows_me) 

but it doesn’t translate well into sql, and I would expect tremendous speedup to perform this given intersection at the database level. But to achieve even more!

Even better would be to have a destination object like:

 character.friends.other_character character.friends.relationship_type = -1 | 0 | 1 

Where

• -1 means I know the other one-sidedly,
• 0 double-sided,
• 1 means that others know me unilaterally.

Do you see the reason this logic is at the database level? If so, how would you do it? If you know, do you know how to put a simple two-way part of real_friend at the database level?

Answer 1

For real_friends, you would like to query this at the database level. It should look something like this:

 @property def real_friends(self): char_rels1 = aliased(CharacterRelationship) char_rels2 = aliased(CharacterRelationship) return DBSession.query(Character).\ join(char_rels1, char_rels1.other_id == Character.id).\ filter(char_rels1.me_id == self.id).\ join(char_rels2, char_rels2.me_id == Character.id).\ filter(char_rels2.other_id == self.id).all() 

You can, of course, establish this as a relationship.

For other_character (I assume these are not mutual friends), I would make a similar request, but with an external interface.

For the relationship_type parameter, I would do the following when caching know_person ():

 def knows_person(self, person): return bool(DBSession.query(CharacterRelationship).\ filter(CharacterRelationship.me_id == self.id).\ filter(CharacterRelationship.other_id == person.id).\ first()) def rel_type(self, person): knows = self.knows_person(person) known = person.knows_person(self) if knows and known: return 0 elif knows: return -1 elif known: return 1 return None 

Answer 2

Having defined your friends relationship, real_friends easily done as follows:

 @property def real_friends(self): qry = (Session.object_session(self).query(User). join(User.friends, aliased=True).filter(User.id == self.id). join(User.knows_me, aliased=True).filter(User.id == self.id) ) return qry.all() 

but this will make two more redundant JOIN to User tables, so although IMO is the best code, it can be inferior to the Jonathan version where a simple JOIN in the CharacterRelationship table is used.

As for the option even better that you are requesting: this can be done again in the request as shown below (accepted as a reference in @Jonathan's answer):

 @property def all_friends(self): char_rels1 = aliased(Friendship) char_rels2 = aliased(Friendship) qry = (Session.object_session(self).query(User, case([(char_rels1.id <> None, 1)], else_=0)+case([(char_rels2.id <> None, 2)], else_=0)). outerjoin(char_rels1, and_(char_rels1.friends == User.id, char_rels1.knows_me == self.id)). outerjoin(char_rels2, and_(char_rels2.knows_me == User.id, char_rels2.friends == self.id)). filter(or_(char_rels1.id <> None, char_rels2.id <> None)) # @note: exclude those that are not connected at all ) return qry.all() 

A few notes:

• filter moves to the JOIN condition - this is very important for external joins, otherwise the result will be incorrect.
• the result will be [(User, _code_), ..] , where _code_ : 0 - not connected, 1 - friend, 2 - knows me, 3 - real friend (both sides)