在数据库中实现[用户订阅/订阅者]关系的最佳方法

Question

我正在使用Swift 4+ Vapor框架、Fluent ORM和PostgreSQL作为驱动程序编写服务器。我有一个用户模型，它应该有订阅者和订阅(这也是用户模型)。这里有两个选项: 1.具有订阅/订阅者唯一I的存储数组；2.构建一对多的用户-用户关系。你认为哪一种更好，我该如何实施呢？

Answer 1

存储数组不是最优的。查询数据库以查找所有用户的订阅者将需要解析每个用户的订阅数组，并找到包含目标用户ID的订阅数组。

Fluent使用枢轴类来建模多到多的关系。因为这是一个自引用关系，为了避免ID键冲突，您可能会发现创建自己的“通过”模型是最容易的。

import FluentProvider
import Vapor

final class Subscription: Model, PivotProtocol {

  typealias Left = User
  typealias Right = User

  var subscriberId: Identifier
  var subscribedId: Identifier

  init(
    subscriberId: Identifier,
    subscribedId: Identifier
  ) {
    self.subscriberId = subscriberId
    self.subscribedId = subscribedId
  }

  let storage = Storage()

  static let leftIdKey = "subscriber_id"
  static let rightIdKey = "subscribed_id"

  init(row: Row) throws {
    subscriberId = try row.get("subscriber_id")
    subscribedId = try row.get("subscribed_id")
  }

  func makeRow() throws -> Row {
    var row = Row()
    try row.set("subscriber_id", subscriberId)
    try row.set("subscribed_id", subscribedId)
    return row
  }

}

extension User {
  var subscribers: Siblings<User, User, Subscription> {
    return siblings(localIdKey: "subscriber_id", foreignIdKey: "subscribed_id")
  }
  var subscribed: Siblings<User, User, Subscription> {
    return siblings(localIdKey: "subscribed_id", foreignIdKey: "subscriber_id")
  }
}