SQLAlchemy ORM - 使用联结表属性进行多对多关系查询

Question

我有一个类似于此处描述的多对多关系，链接为https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object。请注意我的Association表包括一个extra_data字段。

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(ForeignKey('left.id'), primary_key=True)
    right_id = Column(ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary="association", back_populates="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Parent", secondary="association", back_populates="children")

如果我想获取一个特定的父对象及其子对象，可以这样做：

db_parent = db.query(Parent).where(Parent.id == 1).first()
print(db_parent.children[0].id)  # works fine

但是，“extra_data”字段并未作为子元素的属性包含在内。

print(db_parent.children[0].extra_data)

如何编写获取父级的子元素，以便包含extra_data属性？出现AttributeError: 'Child' object has no attribute 'extra_data'错误。

---

完整的工作示例

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=False)

# Make the DeclarativeMeta
Base = declarative_base()


class Association(Base):
    __tablename__ = 'association'
    left_id = Column(ForeignKey('left.id'), primary_key=True)
    right_id = Column(ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", secondary="association", back_populates="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Parent", secondary="association", back_populates="children")


# Create the tables in the database
Base.metadata.create_all(engine)

# Test it
with Session(bind=engine) as session:

    # add parents
    p1 = Parent()
    session.add(p1)

    p2 = Parent()
    session.add(p2)

    session.commit()

    # add children
    c1 = Child()
    session.add(c1)

    c2 = Child()
    session.add(c2)

    session.commit()

    # map children to parents
    a1 = Association(left_id=p1.id, right_id=c1.id, extra_data='foo')
    a2 = Association(left_id=p1.id, right_id=c2.id, extra_data='bar')
    a3 = Association(left_id=p2.id, right_id=c2.id, extra_data='baz')

    session.add(a1)
    session.add(a2)
    session.add(a3)

    session.commit()


with Session(bind=engine) as session:
    db_parent = session.query(Parent).where(Parent.id == 1).first()
    print(db_parent.children[0].id)
    print(db_parent.children[0].extra_data)

- Ben

---

你有读到你链接的文档部分后面的警告吗？在我看来，你需要移除“secondary”，并按照文档中所示构建关系。 - snakecharmerb

嘿，谢谢。我看了警告，但我认为它只涉及对数据的更改。我对像示例中所示的结构化模型的问题是，我必须使用类似于parent.children[0].child而不是简单地使用parent.children[0]来引用子对象。这会破坏下游的Pydantic模型，我使用查询结果进行初始化。 - Ben

所以你希望parent.children包含Child实例，但是你想通过Child访问相应连接表行的extra_data属性？ - snakecharmerb

@snakecharmerb 是的，没错。 - Ben

请阅读 https://docs.sqlalchemy.org/en/14/orm/extensions/associationproxy.html，这可能是实现你目标的方法。 - van

Answer 1

您所要求的内容无法使用SQLAlchemy完全按照您想要的方式完成。确实，Parent.children中的项应该是Child类的实例。如果您的子类具有从关联表加载的extra_data属性，则它将指向其父级之一。

我试图解释的是：您希望在Child中拥有对"extra_data"的隐式引用，只有在Child对象从父对象引用时才有意义。

例如，想象以下场景。

session.add_all(
   Association(left=parent_a.id, right=child.id, extra_data="hello")
   Association(left=parent_b.id, right=child.id, extra_data="world")
)

你期望在child.extra_data中看到哪个父元数据？

此外，大多数情况下，如果您需要将对象作为关联表，则意味着该对象本身具有意义。因此，您不应该试图隐藏它。请看以下具体示例。

class Account(Base):
    __tablename__ = "accounts"
    id = Column(Integer, primary_key=True)
    username = Column(String(10), nullable=False)
    groups = relationship("Membership", back_populates="account")


class Group(Base):
    __tablename__ = "groups"
    id = Column(Integer, primary_key=True)
    name = Column(String(10), nullable=False)
    members = relationship("Membership", back_populates="group")


class Membership(Base):
    """Membership is our association table here"""
    __tablename__ = "memberships"
    id = Column(Integer, primary_key=True)

    account_id = Column(Integer, ForeignKey("accounts.id"))
    account = relationship("Account", back_populates="groups")

    group_id = Column(Integer, ForeignKey("groups.id"))
    group = relationship("Group", back_populates="members")

    # extra data embed in association table
    role = Column(String(10), nullable=False)

Base.metadata.create_all()

# create user "toto" that belongs to group "Funny people" with role "joker"
toto = Account(username="toto")
funny_people = Group(name="Funny people")
session.add(Membership(account=toto, group=funny_people, role="joker"))
session.commit()

注意这两种方法之间的区别。在这里，Account.groups 包含成员资格而不是直接的 Group 对象。然后您可以按照以下方式使用它：

toto = session.query(Account).first()

toto.username
toto.groups[0].group.name
toto.groups[0].role

我知道这不完全是您要求的，但这可能是最接近的方法，而不会引入奇怪的逻辑，干扰应用程序的正常运行。

- Sylvan LE DEUNFF

Answer 2

感谢@van向我介绍了SQLAlchemy的AssociationProxy。使用AssociationProxy，我几乎可以得到我想要的东西，但还不够理想。

这里的想法是像往常一样创建三个表/类：

1. left（父） 2. right（子） 3. association（关联）

然后，我给Parent添加了一个名为children的关联属性。我还给Association添加了一个名为parent和child的关联属性。

最后，在Association中设置了关联代理，以便它“承载”其相关子对象具有的所有我想要的东西。下面是一个工作示例。

from sqlalchemy import create_engine, Column, Integer, String, Float, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session
from sqlalchemy.ext.associationproxy import association_proxy

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=True)

# Make the DeclarativeMeta
Base = declarative_base()


class Association(Base):
    __tablename__ = 'association'

    left_id = Column(ForeignKey('left.id'), primary_key=True)
    right_id = Column(ForeignKey('right.id'), primary_key=True)
    parent = relationship("Parent", back_populates="children")
    child = relationship("Child")
    extra_data = Column(String(50))

    # Association proxies
    child_name = association_proxy("child", "name")
    child_weight = association_proxy("child", "weight")

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    weight = Column(Float, nullable=False)


# Create the tables in the database
Base.metadata.create_all(engine)

# Test it
with Session(bind=engine) as session:

    # add parents
    p1 = Parent()
    session.add(p1)

    p2 = Parent()
    session.add(p2)

    session.commit()

    # add children
    c1 = Child(name = "A", weight = 5)
    session.add(c1)

    c2 = Child(name = "B", weight = 3)
    session.add(c2)

    session.commit()

    # map children to parents
    a1 = Association(left_id=p1.id, right_id=c1.id, extra_data='foo')
    a2 = Association(left_id=p1.id, right_id=c2.id, extra_data='bar')
    a3 = Association(left_id=p2.id, right_id=c2.id, extra_data='baz')

    session.add(a1)
    session.add(a2)
    session.add(a3)

    session.commit()

现在，如果我获取一个“parent”实例，我可以引用“parent.children”，它会返回一个子元素列表，其中包含我需要的所有属性。

with Session(bind=engine) as session:
    db_parent = session.query(Parent).where(Parent.id == 1).first()
    print(db_parent.children[0].extra_data)
    print(db_parent.children[0].child_name)
    print(db_parent.children[0].child_weight)

从技术上讲，parent.children 返回一个 关联（Association） 列表，其中每个关联通过我的关联代理从其相关的 Child 实例获取属性。这样做的缺点是，我必须将这些属性标记为 child_name 和 child_weight，而不是简单地使用 name 和 weight，否则，如果我决定设置反向关系，那么 name 和 weight 就不会明确表示是子项的属性而不是父项的属性。

- Ben

Answer 3

我想到的另一个解决方案是定义一个只读属性children，它仅执行所需的SQL查询以获取我需要的确切数据。

from sqlalchemy import create_engine, Column, Integer, String, Float, ForeignKey
from sqlalchemy.orm import declarative_base, Session, object_session

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=True)

# Make the DeclarativeMeta
Base = declarative_base()


class Association(Base):
    __tablename__ = 'association'

    left_id = Column(ForeignKey('left.id'), primary_key=True)
    right_id = Column(ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)

    @property
    def children(self):
        s = """
            SELECT foo.* FROM (
                SELECT
                    right.*,
                    association.extra_data,
                    association.left_id
                FROM right INNER JOIN association ON right.id = association.right_id
            ) AS foo
            INNER JOIN left ON foo.left_id = left.id
            WHERE left.id = :leftid
            """
        result = object_session(self).execute(s, params={'leftid': self.id}).fetchall()
        return result


class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    weight = Column(Float, nullable=False)


# Create the tables in the database
Base.metadata.create_all(engine)

# Test it
with Session(bind=engine) as session:

    # add parents
    p1 = Parent()
    session.add(p1)

    p2 = Parent()
    session.add(p2)

    session.commit()

    # add children
    c1 = Child(name = "A", weight = 5)
    session.add(c1)

    c2 = Child(name = "B", weight = 3)
    session.add(c2)

    session.commit()

    # map children to parents
    a1 = Association(left_id=p1.id, right_id=c1.id, extra_data='foo')
    a2 = Association(left_id=p1.id, right_id=c2.id, extra_data='bar')
    a3 = Association(left_id=p2.id, right_id=c2.id, extra_data='baz')

    session.add(a1)
    session.add(a2)
    session.add(a3)

    session.commit()

使用方法

with Session(bind=engine) as session:
    db_parent = session.query(Parent).where(Parent.id == 1).first()
    print(db_parent.children[0].extra_data)  # foo
    print(db_parent.children[0].name)        # A
    print(db_parent.children[0].weight)      # 5.0

- Ben