sqlalchemy, select objects that has all tags

Question

I have sqlalchemy models:

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


engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
Base = declarative_base()

class TopicToPizzaAssociation(Base):
    __tablename__ = 'association'
    pizza_id = Column(Integer, ForeignKey('pizza.id'), primary_key=True)
    topic_id = Column(Integer, ForeignKey('topic.id'), primary_key=True)
    topic = relationship("Topic")
    pizza = relationship("Pizza")

class Pizza(Base):
    __tablename__ = 'pizza'
    id = Column(Integer, primary_key=True)
    topics = relationship("TopicToPizzaAssociation")

    def add_topics(self, topics):
        used_topics = {t.topic.product for t in self.topics}
        associations = []
        for topic in topics:
            if topic.product not in used_topics:
                associations.append(TopicToPizzaAssociation(pizza=self, topic=topic))
                used_topics.add(topic.product)
        p1.topics.extend(associations)

class Topic(Base):
    __tablename__ = 'topic'
    id = Column(Integer, primary_key=True)
    product = Column(String(), nullable=False)

I need to select all pizza objects which have the required set of topics:

t1 = Topic(product='t1')
t2 = Topic(product='t2')
t3 = Topic(product='t3')

session = Session()
session.add_all([t1, t2, t3])

p1 = Pizza()
p2 = Pizza()

p1.add_topics([t1, t2, t1])
p2.add_topics([t2, t3])

Base.metadata.create_all(engine)

session.add_all([p1, p2])
session.commit()

values = ['t1', 't2']
topics = session.query(Topic.id).filter(Topic.product.in_(values))
pizza = session.query(Pizza).filter(Pizza.topics.any(TopicToPizzaAssociation.topic_id.in_(
    topics
))).all()

This returns all pizza that have one of topics. If I try to replace any with all, it doesn't work.

I've found that it is possible to make a query with JOIN and COUNT, but I couldn't build sqlalchemy query. Any possible solution will suit me.

Answer 1

Firstly, there is a stack of reading you can do about SQLAlchemy relationships in the docs.

Your code closely matches the Association Object pattern which is (from the docs):

...used when your association table contains additional columns beyond those which are foreign keys to the left and right tables

I.e., if there was something specific about the individual relationship between a Pizza and Topic, you would store that information in line with the relationship between foreign keys in the association table. Here's the example that the docs give:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

Note the column extra_data defined on the Association object.

In your example, there is no such need for an extra_data type field in Association so you can simplify expressing the relationship between Pizza and Topic by using the Many to Many Pattern outlined in the docs.

The main benefit that we can get from that pattern is that we can directly relate the Pizza class to the Topic class. The new models look something like this:

class TopicToPizzaAssociation(Base):
    __tablename__ = 'association'
    pizza_id = Column(Integer, ForeignKey('pizza.id'), primary_key=True)
    topic_id = Column(Integer, ForeignKey('topic.id'), primary_key=True)


class Pizza(Base):
    __tablename__ = 'pizza'
    id = Column(Integer, primary_key=True)
    topics = relationship("Topic", secondary='association')  # relationship is directly to Topic, not to the association table

    def __repr__(self):
        return f'pizza {self.id}'


class Topic(Base):
    __tablename__ = 'topic'
    id = Column(Integer, primary_key=True)
    product = Column(String(), nullable=False)

    def __repr__(self):
        return self.product

The differences to your original code are:

• No relationships defined on the TopicToPizzaAssociation model. With this pattern we can directly relate Pizza to Topic without having relationships on the association model.
• Added __repr__() methods to both models so they print nicer.
• Removed the add_topics method from Pizza (will explain that more later).
• Added the secondary='association' argument to Pizza.topics relationship. This tells sqlalchemy that the foreign key path needed for the relationship to Topic is through the association table.

Here is the testing code and I've put some comments in there:

t1 = Topic(product='t1')
t2 = Topic(product='t2')
t3 = Topic(product='t3')

session = Session()
session.add_all([t1, t2, t3])

p1 = Pizza()
p2 = Pizza()

p1.topics = [t1, t2]  # not adding to the pizzas through a add_topics method
p2.topics = [t2, t3]

Base.metadata.create_all(engine)

session.add_all([p1, p2])
session.commit()

values = [t2, t1]  # these aren't strings, but are the actual objects instantiated above

# using Pizza.topics.contains
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all())  # [pizza 1]

values = [t2, t3]
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all())  # [pizza 2]

values = [t2]
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all())  # [pizza 2, pizza 1]

So this only returns pizzas that have all of the prescribed topics, but not only the prescribed topics.

The reason I left out your add_topics method is that you used that method to check for duplicate Topics added to a given Pizza. That's fine, but the primary key of the association table won't let you add a duplicate topic for a pizza anyway, so I think it's better to let the database layer manage that and just handle the exception that occurs in application code.