Вложенный sqlalchemy фильтр с родителем и сыном

Question

По следующей схеме:

class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Photo(Base):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)
    user = relationship(User)

class Tag(Base):
    id = Column(Integer, primary_key=True)
    tag_name = Column(String)
    tag_version = Column(Integer)
    photo_id = Column(Integer, ForeignKey(Photo.id), nullable=False)
    photo = relationship(Photo)

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

Как в "all the photos of the user with id "1234" that don't have a "cat" of version "2" tagged in them",

Также интересно было бы "all the users who have at least one photo without a specific tag"

Я использую PostgreSQL, кстати.

Answer 1

Вот полный пример, который устанавливает отношения, создает некоторые примеры данных, а затем выполняет два ваших запроса.

Настроить:

from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, not_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)


class Photo(Base):
    __tablename__ = 'photo'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)

    user = relationship(User, backref='photos')


class Tag(Base):
    __tablename__ = 'tag'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    photo_id = Column(Integer, ForeignKey(Photo.id), nullable=False)

    photo = relationship(Photo, backref='tags')


Base.metadata.create_all()
session.add(User(name='davidism', photos=[
    Photo(name='sun', tags=[Tag(name='bright'), Tag(name='day')]),
    Photo(name='moon', tags=[Tag(name='bright'), Tag(name='night')])
]))
session.add(User(name='eran', photos=[
    Photo(name='party', tags=[Tag(name='people'), Tag(name='night')]),
    Photo(name='cat')
]))
session.commit()

Запросить все фотографии без тегов:

no_tags = session.query(Photo).outerjoin(Photo.tags).filter(not_(Photo.tags.any())).all()
print 'no tags: ', len(no_tags)

Запросить все фотографии без тега 'ночь':

not_night = session.query(Photo).outerjoin(Photo.tags).filter(not_(Photo.tags.any(Tag.name == 'night'))).all()
print 'not night: ', len(not_night)

Answer 2

Предполагая существование обратных ссылок Tag.photo = relationship(Photo, backref='tags') а также Photo.user = relationship(User, backref="photos") оба могут быть сделаны с помощью any построить. Это может не генерировать наиболее оптимальный SQL SELECT Скажите, но это очень чисто sqlalchemy,

Часть-1: "all the photos of the user with id "1234" that don't have a "cat" of version "2" tagged in them"

def get_user_photos_without_tag(user_id, tag_name, tag_version):
    qry = (session.query(Photo)
            .filter(~Photo.tags.any(and_(
                Tag.tag_name == tag_name,
                Tag.tag_version == tag_version))
            )
            .filter(Photo.user_id == user_id)
        )
    return qry.all()

photos = get_user_photos_without_tag(1234, 'cat', 2)

Часть 2: "all the users who have at least one photo without a specific tag"

def get_user_with_photos_without_tag(tag_name, tag_version):
    qry = (session.query(User)
            .filter(User.photos.any(
                ~Photo.tags.any(and_(
                    Tag.tag_name == tag_name,
                    Tag.tag_version == tag_version))
                ))
        )
    return qry.all()

res = get_user_with_photos_without_tag('cat', 2)