CODEWITHSUNDEEP
sqlpostgresqlsqlalchemy
eran
eran

Reputation: 15136

nested sqlalchemy filter with parent and son

With the following scheme:

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)

How do I create an SQLAlchemy query to get all the photos of a specific user, that don't have a specific tag and version.

As in "all the photos of the user with id "1234" that don't have a "cat" of version "2" tagged in them".

Also interesting would be "all the users who have at least one photo without a specific tag"

I'm using postgreSQL btw.

Upvotes: 0

Views: 3629

Answers (2)

van
van

Reputation: 76962

Assuming existance of backrefs Tag.photo = relationship(Photo, backref='tags') and Photo.user = relationship(User, backref="photos") both can be done using any construct. This might not generate the most optimal SQL SELECT statement, but it is a very clean sqlalchemy.

Part-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)

Part-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)

Upvotes: 1

davidism
davidism

Reputation: 127180

Here is a complete example that sets up relationships, creates some sample data, then performs your two queries.

Setup:

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()

Query all photos with no tags at all:

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

Query all photos without the tag 'night':

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

Upvotes: 2

Related Questions