SQLAlchemy表使用两个外键定义关系 [英] SQLAlchemy table defining relationship using two foreign keys

问题描述

我有两个表，Users和ChatSessions.ChatSessions具有两个字段user_id和friend_id，它们都是Users表的外键.

I have two tables, Users and ChatSessions. ChatSessions has two fields, user_id and friend_id, both foreign keys to the Users table.

user_id始终包含发起聊天会话的用户，friend_id是另一个用户.由于某个用户可以由他或他的朋友发起聊天会话，因此他可以在各种会话中将其id设置为user_id或friend_id.

user_id always contains the user that initiated the chat session, friend_id is the other user. As a certain user can have chat sessions initiated by him, or his friends, he can have his id either as user_id or as friend_id, in various sessions.

是否有可能在用户"表中定义一个关系，无论他的id是在user_id还是friend_id中，我都可以访问该用户的所有chat_sessions?像这样:

Is it possible to define a relationship in the Users table, where i have access to all the chat_sessions of that user, no matter whether his id is in user_id or friend_id? Something like this:

chat_sessions = db.relationship('chat_sessions',
                primaryjoin="or_(User.id==ChatSession.user_id, User.id==ChatSession.friend_id)",
                backref="user")

当我尝试向Users表提交条目时收到以下错误:

I receive the following error when I try to commit an entry to the Users table:

ERROR   main.py:76      [10.0.2.2] Unhandled Exception [93e3f515-7dd6-4e8d-b096-8239313433f2]: relationship 'chat_sessions' expects a class or a mapper argument (received: <class 'sqlalchemy.sql.schema.Table'>)

模型:

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(60), index=True, unique=True)
    password = db.Column(db.String(255))
    name = db.Column(db.String(100))
    active = db.Column(db.Boolean(), nullable=False)

    chat_sessions = db.relationship('chat_sessions',
                    primaryjoin="or_(User.id==ChatSession.user_id, User.id==ChatSession.friend_id)")



class ChatSession(db.Model):
    __tablename__ = 'chat_sessions'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    friend_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    status = db.Column(db.String(50))


    user = db.relationship('User', foreign_keys=[user_id])
    friend = db.relationship('User', foreign_keys=[friend_id])

推荐答案

不看表代码很难确定，但是删除backref参数可能就足够了.

It's difficult to be certain without seeing the tables' code, but it might be sufficient to remove the backref argument.

这是一个纯SQLAlchemy实现，似乎可以实现您想要的功能:

Here's a pure SQLAlchemy implementation that seems to do what you want:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import orm


Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

    all_chats = orm.relationship('Chat',
        primaryjoin="or_(User.id==Chat.user_id, User.id==Chat.friend_id)")

    def __repr__(self):
        return f'User(name={self.name})'


class Chat(Base):
    __tablename__ = 'chats'

    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(sa.Integer, sa.ForeignKey('users.id'))
    friend_id = sa.Column(sa.Integer, sa.ForeignKey('users.id'))

    user = orm.relationship('User', foreign_keys=[user_id])
    friend = orm.relationship('User', foreign_keys=[friend_id])

    def __repr__(self):
        return f'Chat(user={self.user.name}, friend={self.friend.name})'


engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)

usernames = ['Alice', 'Bob', 'Carol']
session = Session()
users = [User(name=name) for name in usernames]
session.add_all(users)
session.flush()

a, b, c = users

session.add(Chat(user_id=a.id, friend_id=b.id))
session.add(Chat(user_id=a.id, friend_id=c.id))
session.add(Chat(user_id=c.id, friend_id=a.id))
session.commit()
session.close()

session = Session()
users = session.query(User)
for user in users:
    for chat in user.all_chats:
        print(user, chat)
    print()
session.close()

这是输出:

User(name=Alice) Chat(user=Alice, friend=Bob)
User(name=Alice) Chat(user=Alice, friend=Carol)
User(name=Alice) Chat(user=Carol, friend=Alice)

User(name=Bob) Chat(user=Alice, friend=Bob)

User(name=Carol) Chat(user=Alice, friend=Carol)
User(name=Carol) Chat(user=Carol, friend=Alice)

这篇关于SQLAlchemy表使用两个外键定义关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！