Присоединиться после externaljoin в SQLAlchemy

Question

Предположим, у меня отношения один-ко-многим, когда родители и дети сгруппированы по group_id,

Примечание: этот пример является урезанной версией моего кода, которая на самом деле является отношением многих ко многим. Там могут быть некоторые ошибки, не связанные с вопросом.

class Node(Base):
    __tablename__ = 'node'
    id = Column(GUID, default=uuid.uuid4, primary_key=True)
    group_id = Column(GUID, nullable=False, primary_key=True)
    parent_id = Column(GUID)
    title = Column(Text, nullable=False)

class Leaf(Base):
    __tablename__ = 'leaf'
    id = Column(GUID, nullable=False, primary_key=True)
    group_id = Column(GUID, nullable=False, primary_key=True)
    parent_id = Column(GUID, nullable=False)

group_id используется как способ создания новых версий - поэтому узлы и листья с одинаковыми id может существовать в нескольких группах.

Я хочу сравнить две группы и найти все листья, чьи родители изменились. Я пытаюсь использовать внешнее соединение для сравнения, а затем два соединения для фильтрации родительских узлов:

def find_changed_leaves(group_id_a, group_id_b, session):
    NodeA = model.Node
    NodeB = aliased(model.Node, name='node_b')
    LeafA = model.Leaf
    LeafB = aliased(model.Leaf, name='leaf_b')

    query = (session.query(LeafA, LeafB)
        .outerjoin(LeafB, LeafA.id == LeafB.id)

        .join(NodeA, (LeafA.group_id == NodeA.group_id) &
                     (LeafA.parent_id == NodeA.id))
        .join(NodeB, (LeafB.group_id == NodeB.group_id) &
                     (LeafB.parent_id == NodeB.id))

        # Group membership
        .filter(LeafA.group_id == group_id_a,
                LeafB.group_id == group_id_b)

        # Filter for modified parents
        .filter(NodeA.title != NodeB.title)
    )

    return query.all()

Это работает, но не показывает листья, которые находятся только в одной из групп (например, если лист был добавлен к узлу в новой группе). Как я могу показать все листья, возвращаясь None для листа, который отсутствует в одной из групп?

Редактировать: я вижу, есть опасности смешивания соединения с внешним соединением. Я пытался наивно изменить его на .outerjoin(NodeA, ..., но это не помогло.

Answer 1

Как уже упоминалось в комментарии, не совсем ясно, что должно быть достигнуто. Тем не менее, код ниже должен по крайней мере дать вам несколько указаний.

Прежде всего, я бы не пытался объединить все это в одном запросе (возможно, с использованием полных объединений и подзапросов), а разделил его на 3 отдельных запроса:

1. получить LeafA, LeafB чьи родители изменились
2. получить LaefA которые не имеют соответствующих LeafB
3. получить LaefB которые не имеют соответствующих LeafA

Ниже приведен код, который должен работать как в обоих sqlite а также postgresql, Обратите внимание, что я добавил отношения и использую их в запросах. Но вы можете сделать то же самое с явными условиями соединения, как в вашем фрагменте кода.

import uuid

from sqlalchemy import (
    create_engine, Column, Integer, String, ForeignKey, Text, and_,
    ForeignKeyConstraint, UniqueConstraint, exists
)
from sqlalchemy.orm import sessionmaker, relationship, eagerload, aliased
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.dialects.postgresql import UUID as GUID

_db_uri = 'sqlite:///:memory:'; GUID = String
# _db_uri = "postgresql://aaa:bbb@localhost/mytestdb"
engine = create_engine(_db_uri, echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base(engine)

newid = lambda: str(uuid.uuid4())

# define object model
class Node(Base):
    __tablename__ = 'node'
    id = Column(GUID, default=newid, primary_key=True)
    group_id = Column(GUID, nullable=False, primary_key=True)
    # parent_id = Column(GUID)
    title = Column(Text, nullable=False)


class Leaf(Base):
    __tablename__ = 'leaf'
    id = Column(GUID, nullable=False, primary_key=True)
    group_id = Column(GUID, nullable=False, primary_key=True)
    parent_id = Column(GUID, nullable=False)
    title = Column(Text, nullable=False)

    # define relationships - easier test data creation and querying
    parent = relationship(
        Node,
        primaryjoin=and_(Node.id == parent_id, Node.group_id == group_id),
        backref="children",
    )

    __table_args__ = (
        ForeignKeyConstraint(
            ['parent_id', 'group_id'], ['node.id', 'node.group_id']
        ),
    )


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session()


g1, g2, l1, l2, l3 = [newid() for _ in range(5)]

# Create test data
def _add_test_data():
    n11 = Node(
        title="node1", group_id=g1,
        children=[
            Leaf(id=l1, title="g1 only"),
            Leaf(id=l3, title="both groups"),
        ]
    )

    n21 = Node(
        title="node1 changed", group_id=g2,
        children=[
            Leaf(id=l2, title="g2 only"),
            Leaf(id=l3, title="both groups"),
        ]
    )

    session.add_all([n11, n21])
    session.commit()


def find_changed_leaves(group_id_a, group_id_b):
    """
    Leaves which are in both versions, but a `title` for their parents is changed.
    """
    NodeA = aliased(Node, name='node_a')
    NodeB = aliased(Node, name='node_b')
    LeafA = aliased(Leaf, name='leaf_a')
    LeafB = aliased(Leaf, name='leaf_b')

    query = (
        session.query(LeafA, LeafB)
        .filter(LeafA.group_id == group_id_a)
        # @note: group membership for LeafB is part of join now
        .join(LeafB, (LeafA.id == LeafB.id) & (LeafB.group_id == group_id_b))

        .join(NodeA, LeafA.parent)
        .join(NodeB, LeafB.parent)

        # Filter for modified parents
        .filter(NodeA.title != NodeB.title)
    )
    return query.all()


def find_orphaned_leaves(group_id_a, group_id_b):
    """
    Leaves found in group A, but not in group B.
    """
    LeafA = aliased(Leaf, name='leaf_a')
    LeafB = aliased(Leaf, name='leaf_b')

    query = (
        session.query(LeafA)
        .filter(~(
            session.query(LeafB)
            .filter(LeafA.id == LeafB.id)
            .filter(group_id_b == LeafB.group_id)
            .exists()
        ))

        # Group membership
        .filter(LeafA.group_id == group_id_a)
    )
    return query.all()


def find_deleted_leaves(group_id_a, group_id_b):
    a_s = find_orphaned_leaves(group_id_a, group_id_b)
    return tuple((a, None) for a in a_s)

def find_added_leaves(group_id_a, group_id_b):
    b_s = find_orphaned_leaves(group_id_b, group_id_a)
    return tuple((None, b) for b in b_s)


# add test data
_add_test_data()

# check the results
changed = find_changed_leaves(g1, g2)
assert 1 == len(changed)
le, ri = changed[0]
assert le.id == ri.id == l3

added = find_added_leaves(g1, g2)
assert 1 == len(added)
le, ri = added[0]
assert le is None
assert ri.id == l2

deleted = find_deleted_leaves(g1, g2)
assert 1 == len(deleted)
le, ri = deleted[0]
assert le.id == l1
assert ri is None