sqlalchemy对称多对一友情 [英] sqlalchemy symmetric many to one friendship

问题描述

我正在尝试使用SQLAlchemy ORM建立友谊模型。我要建模的关系是对称的。与Facebook类似，如果用户a要添加用户b，则用户b必须批准该友谊请求。我当前的模型如下。

I am attempting to model a friendship using SQLAlchemy ORM. The relationship that I am trying to model is symmetric. Similar to Facebook, if user a is to add user b, user b must approve that friendship request. My current model is as follows.

class User(db.Model):
  __tablename__ = 'User'
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(35), unique=False)
  username = db.Column(db.String(25), index=True, unique=True)
  password = db.Column(db.String(35), unique=False)
  email = db.Column(db.String(35), unique=True)
  phone_number = db.Column(db.String(22))

  # define relationships
  requester = db.relationship('Relationship', foreign_keys='Relationship.requesting_user', backref='requester')
  receiver = db.relationship('Relationship', foreign_keys='Relationship.receiving_user', backref='received')

  def __repr__(self):
    return '<User %r>' % (self.username)


class Relationship(db.Model):
  __tablename__ = 'Relationship'
  id = db.Column(db.Integer, primary_key=True)
  requesting_user = db.Column(db.Integer, db.ForeignKey('User.id'))
  receiving_user = db.Column(db.Integer, db.ForeignKey("User.id"))
  status = db.Column(db.Integer)
  __table_args__ = (db.UniqueConstraint('receiving_user', 'requesting_user', name='_receiving_user_uc'), )

该模型有效，但是，我认为它的模型不正确。甚至需要我使用身份吗？我假设可以对其进行建模，以便每个朋友关系都有自己的条目。当前，用户可以与另一个用户发起好友请求。当其他用户批准该请求时，状态更改为已接受。我对关联表做了一些研究，但不太确定它们如何在这样的模型中发挥作用。对我当前模型及其改进方法的任何建议将不胜感激。

The model works, however, I don't think that it is properly modeled. Is it even required that I use a status? I'm assuming it can be modeled so that each friend relationship gets its own entry. Currently, a user can initiate a friend request with another user. When the other user approves the request, the status changes to accepted. I have looked a little into association tables but am not too sure how they would play into a model like this. Any advice on my current model and how it can be improved would be greatly appreciated.

推荐答案

除其他外，您可能希望了解关联代理。关联代理告诉SQLAlchemy，您有一个由可能包含其他数据的中间表介导的多对多关系。在您的情况下，每个用户可以发送多个请求，也可以接收多个请求，而 Relationship 是中介表，其中包含状态列作为附加数据。

Among other things, you may want to learn about association proxies. An association proxy tells SQLAlchemy that you have a many-to-many relationship mediated by an intermediate table which may contain additional data. In your case, each User can send multiple requests and also receive multiple requests and Relationship is the mediating table which contains the status column as additional data.

这里是您代码的一种变体，与您编写的内容相对接近：

Here is a variant of your code which stays relatively close to what you wrote:

from sqlalchemy.ext.associationproxy import association_proxy


class User(db.Model):
    __tablename__ = 'User'
    # The above is not necessary. If omitted, __tablename__ will be
    # automatically inferred to be 'user', which is fine.
    # (It is necessary if you have a __table_args__, though.)

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(35), unique=False)
    # and so forth

    requested_rels = db.relationship(
        'Relationship',
        foreign_keys='Relationship.requesting_user_id',
        backref='requesting_user'
    )
    received_rels = db.relationship(
        'Relationship',
        foreign_keys='Relationship.receiving_user_id',
        backref='receiving_user'
    )
    aspiring_friends = association_proxy('received_rels', 'requesting_user')
    desired_friends = association_proxy('requested_rels', 'receiving_user')

    def __repr__(self):
        # and so forth


class Relationship(db.Model):
    # __tablename__ removed, becomes 'relationship'
    # __table_args__ removed, see below

    requesting_user_id = db.Column(db.Integer, db.ForeignKey('User.id'), primary_key=True)
    receiving_user_id = db.Column(db.Integer, db.ForeignKey('User.id'), primary_key=True)
    # Marking both columns above as primary_key creates a compound primary
    # key, which at the same time saves you the effort of defining the
    # UNIQUE constraint in __table_args__
    status = db.Column(db.Integer)

    # Implicit one-to-many relations: requesting_user, receiving_user.
    # Normally it would be more convenient to define those relations on
    # this side, but since you have two outgoing relationships with the
    # same table (User), you chose wisely to define them there.

（请注意，我对行的排序略有不同，以及如何使用 _id 后缀用于外键列，同时保留相同的名称，但不为相应的 db.relationship s添加后缀。我建议您采用这种样式，

(Note how I ordered the lines slightly differently and how I used the _id suffix for foreign key columns while reserving the same name without the suffix for the corresponding db.relationships. I would suggest that you adopt this style, too.)

现在，您可以采用一种干净的方法直接从您的 User 模型。但是，这仍然不理想，因为您需要编写以下代码才能获得用户的所有确认朋友：

Now you have a clean way to access incoming and outgoing friendship requests as well as the corresponding users directly from your User model. However, this is still less than ideal because you need to write the following code in order to get all confirmed friends of a user:

def get_friends(user):
    requested_friends = (
        db.session.query(Relationship.receiving_user)
        .filter(Relationship.requesting_user == user)
        .filter(Relationship.status == CONFIRMED)
    )
    received_friends = (
        db.session.query(Relationship.requesting_user)
        .filter(Relationship.receiving_user == user)
        .filter(Relationship.status == CONFIRMED)
    )
    return requested_friends.union(received_friends).all()

（我没有对此进行测试；您可能还需要 join 与 User 在两个查询中都起作用，以使工会工作。）

(I did not test this; you might need to also join with User in both queries in order for the union to work.)

更糟糕的是，模型名称 Relationship 以及模型中的几个成员的名称oddes似乎并不能很好地传达其实际含义。

To make things worse, the model name Relationship as well as the names of several members within the models don't seem to convey very well what they actually mean.

您可以通过删除 Relationship.status ，并将关系重命名为 FriendshipRequest 。然后，添加第二个 User -to- User 关联模型，称为 Friendship 并添加相应的第二组 db.Relationship s和 backref s和与 User 的association_proxy 关联。当有人发送友谊请求时，您向 FriendshipRequest 记录。如果请求被接受，则删除记录，并将其替换为 Friendship 中的新记录。这样，不使用状态码，而是通过存储一对用户的表对友谊的状态进行编码。 Friendship 模型可能如下所示：

You can improve matters by removing Relationship.status and renaming Relationship to FriendshipRequest. Then, add a second User-to-User association model called Friendship and add a corresponding second set of db.Relationships with backrefs and association_proxys to User. When somebody sends a friendship request, you file a record to FriendshipRequest. If the request is accepted, you remove the record and replace it with a new record in Friendship. This way, instead of using a status code, the status of a friendship is encoded by the table in which you store a pair of users. The Friendship model may look like this:

class Friendship(db.Model):
    user1_id = db.Column(db.Integer, db.ForeignKey('User.id'), primary_key=True)
    user2_id = db.Column(db.Integer, db.ForeignKey('User.id'), primary_key=True)

    # Implicit one-to-many relations: user1, user2
    # (defined as backrefs in User.)

（对应 db.relationship s和 association_proxy User 中的留给读者练习。）

(Corresponding db.relationships and association_proxys in User are left as an exercise to the reader.)

这种方法当您需要确认的用户好友时，可以节省一半的过滤操作。不过，您仍然需要对两个查询进行联合，因为您的用户可以是 user1 或 Friendship 实例中，> user2 。这是固有的困难，因为我们正在处理自反对称关系。我认为有可能发明出更优雅的方法，但是我认为这很复杂，足以在此处提出关于Stack Overflow的新问题。

This approach saves you half of the filtering operations when you need the confirmed friends of a user. Still, you need to make a union of two queries because your user can be either user1 or user2 in each instance of Friendship. This is inherently difficult because we are dealing with a reflexive symmetric relationship. I think it is possible to invent still more elegant ways to do it, but I think that would be complicated enough to warrant a new question here on Stack Overflow.

这篇关于sqlalchemy对称多对一友情的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！