SQLAlchemy отношения с Postgresql ARRAY

Предположим, что у нас есть база данных PostgreSQL с двумя таблицами A, B.

table A columns: id, name
table B columns: id, name, array_a

Столбец array_a в таблице B содержит массив переменных длины из таблицы A. В SQLAlchemy мы имеем два класса, которые моделируют эти таблицы, например, классы A и B.

Следующее прекрасно работает, чтобы получить все объекты A, которые указаны в объекте B:

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

Как мы можем создать отношения в B, ссылающиеся на объекты A, соответствующие массиву? Пробные сопоставители столбцов с использованием func.any выше, но он жалуется, что ANY(array_a) не является столбцом в модели. Указание условий primaryjoin, как указано выше, похоже, не сокращает его.

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 необходимо, и вы не сможете получить приятную, динамичную объективную доброту, если бы избежали jaywalking.

licensed under cc by-sa 3.0 with attribution.