使用PostgreSQL数组来存储多对多关系

Question

假设我们有一个包含两个表A和B的PostgreSQL数据库。

表A列: id, name
表B列: id, name, array_a

在表B中，列array_a包含了一组来自表A的id。在SQLAlchemy中，我们有两个类来对应这些表，称为类A和B。

以下代码可以很好地获取所有在对象B中引用的对象A：

session.query(A).join(B, A.id == func.any(B.array_a)).filter(B.id == <id>).all()

我们如何在B中创建一个引用数组A对象的关系？尝试使用上面的func.any列比较器，但是它抱怨ANY（array_a）不是模型中的列。指定以上primaryjoin条件似乎也行不通。

- TR.

Answer 1

这个反模式被称为"Jaywalking"，而PostgreSQL强大的类型系统使其非常诱人。你应该使用另一个表：

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE a_b (
    a_id INTEGER PRIMARY KEY REFERENCES table_a(id),
    b_id INTEGER PRIMARY KEY REFERENCES table_b(id)
)

哪一个被映射了:

from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()

a_b_table = Table("a_b", Base.metadata,
    Column("a_id", Integer, ForeignKey("table_a.id"), primary_key=True),
    Column("b_id", Integer, ForeignKey("table_b.id"), primary_key=True))

class A(Base):
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(Base):
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    a_set = relationship(A, secondary=a_b_table, backref="b_set")

示例:

>>> print Query(A).filter(A.b_set.any(B.name == "foo"))
SELECT table_a.id AS table_a_id, table_a.name AS table_a_name 
FROM table_a 
WHERE EXISTS (SELECT 1 
FROM a_b, table_b 
WHERE table_a.id = a_b.a_id AND table_b.id = a_b.b_id AND table_b.name = :name_1)

如果您陷入了ARRAY列的困境，最好的选择是使用另一种可选项，它“看起来”像是一个正确的关联表。

from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()


class A(Base):
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(Base):
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    array_a = Column(postgresql.ARRAY(Integer))

a_b_selectable = select([func.unnest(B.array_a).label("a_id"),
                         B.id.label("b_id")]).alias()

A.b_set = relationship(B, secondary=a_b_selectable,
                          primaryjoin=A.id == a_b_selectable.c.a_id,
                          secondaryjoin=a_b_selectable.c.b_id == B.id,
                          viewonly=True,)

B.a_set = relationship(A, secondary=a_b_selectable,
                          primaryjoin=A.id == a_b_selectable.c.a_id,
                          secondaryjoin=a_b_selectable.c.b_id == B.id,
                          viewonly=True)

这将给你：

>>> print Query(A).filter(A.b_set.any(B.name == "foo"))
SELECT table_a.id AS table_a_id, table_a.name AS table_a_name 
FROM table_a 
WHERE EXISTS (SELECT 1 
FROM (SELECT unnest(table_b.array_a) AS a_id, table_b.id AS b_id 
FROM table_b) AS anon_1, table_b 
WHERE table_a.id = anon_1.a_id AND anon_1.b_id = table_b.id AND table_b.name = :name_1)

显然，由于那里没有真正的表格，所以viewonly=True是必要的，如果你避免乱穿马路，你就不能获得漂亮的、动态的对象好处。

- SingleNegationElimination

---

我认为relation是一个已经弃用的别名，自几个版本以来被替换为relationships。 - ThiefMaster

可选择的功能符合我们的需求，是的。 - TR.

我不确定当PostgreSQL数组可查询且具有明确定义的类型时，这是否属于“违章横穿马路”。似乎更像是SQLAlchemy在处理PostgreSQL数组时存在限制。 - Kiran Jonnalagadda

感谢提供 SQL Antipatterns 的参考资料。我今天买了这本书，它非常棒。 - Kiran Jonnalagadda

@Kiran Jonnalagadda：使用数组类型没有问题；它肯定非常有用，甚至通过SQLAlchemy的类型。限制不是因为Array类型不好，它并不是；而是通过复合类型强制数据完整性既难以表达又难以正确实现。通过简单的、每个引用一个单元格的安排，一致性约束可以表达为只需references footable(foocolumn)，这就是它的违规之处。 - SingleNegationElimination

这非常有帮助。谢谢你。 - Chris

Answer 2

否则，您可以像下面这样明确地加入:

class A(Base):
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(Base):
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    array_a = Column(postgresql.ARRAY(Integer))

    a_ids= relationship('A',primaryjoin='A.id == any_(foreign(B.array_a))',uselist=True)

- KR32