在SQLAlchemy（ORM）中，如何对3个表的多对多关系建模？

Question

我对 SQL 感到生疏，而且完全不了解 SQL Alchemy，但我有一个即将使用两者的项目。因此，我想写点东西来适应一下。在宿醉的情况下，我决定写点东西来跟踪酒精水平。

我有事件，参与其中的用户会消费饮料。这是我的三个基本表（还有一个帮助表guestlist，用于映射用户和事件之间的m:n关系）。

饮料列出了所有事件中所有用户随时可以使用的饮料（无需映射任何内容）。用户和事件都会不时地创建，所有用户都可以加入所有事件，因此我使用guestlist表来映射这些内容。

现在进入正题：我需要记录每个用户在哪个事件上在什么时间喝了什么饮料。我尝试使用另一张表shots来解决这个问题（见下文），但我不确定这是否是一个好的解决方案。 _{(来源：anyimg.com)} 在SQL Alchemy中，它可能看起来像这样（或者不是，但这是我目前想出的）：

guestlist_table = Table('guestlist', Base.metadata,
    Column('event_id', Integer, ForeignKey('events.id')),
    Column('user_id', Integer, ForeignKey('users.id'))
)

class Event(Base):

  __tablename__ = 'events'

  id = Column(Integer, primary_key=True)
  name = Column(String(80), nullable=False)
  started = Column(DateTime, nullable=False, index=True,
    default=datetime.datetime.now
  )
  users = relationship("User", secondary=guestlist_table, backref="events")
  # ...

class User(Base):

  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False, unique=True, index=True)
  birthdate = Column(Date, nullable=False)
  weight = Column(Integer, nullable=False)
  sex = Column(Boolean, nullable=False)
  # ...

class Drink(Base):

  __tablename__ = 'drinks'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False)
  volume = Column(Numeric(5,2), nullable=False)
  percent = Column(Numeric(5,2), nullable=False)
  # ...

class Shots(Base):

  __tablename__ = 'shots'

  id = Column(Integer, primary_key=True)
  at = Column(DateTime, nullable=False,
    default=datetime.datetime.now
  )
  user_id = Column(Integer, ForeignKey('users.id'), index=True)
  event_id = Column(Integer, ForeignKey('events.id'), index=True) 
  drink_id = Column(Integer, ForeignKey('drinks.id'))
  user = relationship("User", backref="shots")
  event = relationship("Event", backref="shots")
  drink = relationship("Drink", uselist=False) # one-to-one, no backref needed

我很难找到一个好的方法来构建一个将“事件”、“用户”和“饮料”映射在一起的表格：我应该如何制定关系，以及如何查询它？ 问题是我感觉我忽视了某些东西。 而且坦率地说，我完全不知道如何查询它？

以下是我大部分时间要进行的查询：

- 我至少需要获取在活动中消耗的所有Shot（可能按用户排序） - 有时我也需要特定用户的所有Shot（可能按活动排序） - 还有很多计数：

- 每个事件的Shot数量 - 每个用户的Shot数量 - 用户在事件上喝下的Shot数量 shots表是管理这个的好方法吗？

- Brutus

---

你好@Brutus，根据我的看法，你们的关系就像一个用户可以参加多个活动，而一个活动可以有多个用户（嘉宾名单）。一个活动可以有很多饮料，而一种饮料可以在多个活动中提供（饮料清单）。一个用户可以喝很多饮料，而一种饮料可以为多个用户提供服务（嘉宾饮料清单）。这样说对吗？ - Nilesh

所有活动都提供所有饮料，所以我认为可能不需要 drinklist。你所称的 guestdrinklist 我试图在 Shots 类中建模。将该类拆分成两个表是否更好？ - Brutus

如果所有饮料都在所有活动中都可用，那么无需指定饮料和活动之间的关系。如果您的字段子集与其他实体相关联，则必须指定关系，如果全部关联，则不需要关系。您可以在饮料和用户之间创建关系。 - Nilesh

我认为Victor的答案非常全面。实际上，查询各种报告应该相当容易。如果您需要这些报告，我肯定会尝试在查询中执行它们，以便每个报告部分获得尽可能少的SQL语句。但是，您可能希望将其中一些作为属性（请参见http://docs.sqlalchemy.org/en/latest/orm/relationships.html?highlight=object_session#building-query-enabled-properties）。您的模型非常好。但有一件明确的事情要添加，即从“Shots”到“guestlist”表的复合FK（下一个评论）。 - van

__table_args__ = ( ForeignKeyConstraint( ["user_id", "event_id"], ["guestlist.user_id", "guestlist.event_id"], name="fk_guestlist", ), ) - van

谢谢Van。我回家后会测试一切。（我接受了Victor的答案，虽然我还没有测试过，但它似乎很可靠。） - Brutus

Answer 1

我建议您绘制一个实体关系图，这样人们和您更容易理解您的问题。

回答您的问题：

我至少需要获取事件中所有拍摄的镜头（可能按用户排序）。

要获取事件的所有镜头，您可以尝试：

session.query(Shots).filter_by(event_id=event_id)

事件ID是您想查询的事件ID。要按用户存储，可以尝试使用以下方法：

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .order_by(asc(Shots.user_id))

当然，如果您想按照用户属性进行排序，您可以连接用户表。

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .join(User) \
    .order_by(asc(User.name))

很简单。

我有时候也需要为特定用户获取所有的镜头（可能按事件排序）

就像之前的例子一样

每个事件的镜头数量

session.query(Shots) \
    .filter_by(event_id=event_id) \
    .count()

我没有运行它们，只是在这里写下来了，但如果我没有打错字的话，它们都应该能正常工作。

- Fang-Pen Lin

---

谢谢你的回答。我一回家就试试看。我还添加了一个ERP。关于我的表格布局：你认为“shots”表是将数据映射在一起的有效方式吗，考虑到我的查询情况？或者我应该使用另一种方法来组织表格和关系？ - Brutus