Pfthb

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.

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.

A query to fetch all Pizza with given Topics (and possibly more) can be expressed using a slightly hard to read double negation:

session.query(Pizza).
 filter(~session.query(Topic).
 filter(Topic.product.in_(values),
 ~session.query(TopicToPizzaAssociation).
 filter(TopicToPizzaAssociation.topic_id == Topic.id,
 TopicToPizzaAssociation.pizza_id == Pizza.id).
 correlate(Pizza, Topic).
 exists()).
 exists())

In English it reads along the lines of "Fetch pizza where not exists a given topic [sic] that is not in this pizza."

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

SQL does not have universal quantification and so there is no all() operator for relationships the way that any() maps to EXISTS. But

FORALL x ( p ( x ) )

is logically equivalent to

NOT EXISTS x ( NOT p ( x ) )

which the above query takes advantage of. It is also described as how to perform relational division in SQL:

• https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/


• https://www.jooq.org/doc/3.11/manual/sql-building/table-expressions/relational-division/


• http://www.inf.usi.ch/faculty/soule/teaching/2016-fall/db/division.pdf