Query drinks with both ingredients

bar6.py

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base

engine = sa.create_engine('sqlite://', echo=True)
session = orm.Session(engine)
Base = declarative_base()


class Drink(Base):
    __tablename__ = 'drink'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, nullable=False, unique=True)
    ingredients = association_proxy(
        '_drink_ingredients', 'ingredient',
        creator=lambda x: DrinkIngredient(ingredient=x)
    )

    def __str__(self):
        return self.name


class Ingredient(Base):
    __tablename__ = 'ingredient'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, nullable=False, unique=True)
    drinks = association_proxy('_drink_ingredients', 'drink')


class DrinkIngredient(Base):
    __tablename__ = 'drink_ingredient'
    drink_id = sa.Column(sa.ForeignKey(Drink.id), primary_key=True)
    ingredient_id = sa.Column(sa.ForeignKey(Ingredient.id), primary_key=True)
    quantity = sa.Column(sa.Integer, nullable=False, default=1)

    drink = orm.relationship(Drink, backref='_drink_ingredients')
    ingredient = orm.relationship(Ingredient, backref='_drink_ingredients')


Base.metadata.create_all(engine)

i1 = Ingredient(name='Cabbage')
i2 = Ingredient(name='Snark')
i3 = Ingredient(name='Whiskey')
i4 = Ingredient(name='Ffisegydd')

d1 = Drink(name='September Chat', ingredients=[i1, i2])
d2 = Drink(name='Friday Night Chat', ingredients=[i1, i3])
d3 = Drink(name='tristan', ingredients=[i2, i3])
d4 = Drink(name='Tongue Twister', ingredients=[i4])

session.add_all((d1, d2, d3))
session.commit()

# one ingredient
q1 = session.query(Drink).join(*Drink.ingredients.attr).filter(
    Ingredient.name == 'Whiskey'
)
print('Has Whiskey:', ', '.join(str(x) for x in q1))

# either of 2 ingredients
q2 = session.query(Drink).join(*Drink.ingredients.attr).filter(
    Ingredient.name.in_(['Cabbage', 'Snark'])
)
print('Has Cabbage or Snark:', ', '.join(str(x) for x in q2))

# less efficient for large collections, uses exists:
q2a = session.query(Drink).filter(
    Drink.ingredients.any(Ingredient.name.in_(['Cabbage', 'Snark']))
)
print(', '.join(str(x) for x in q2a))

# both of 2 ingredients, with exists x2
q3a = session.query(Drink).filter(
    Drink.ingredients.any(Ingredient.name == 'Snark'),
    Drink.ingredients.any(Ingredient.name == 'Whiskey')
)
print('Has Snark and Whiskey:', ', '.join(str(x) for x in q3a))

# both of 2 ingredients without inefficient exists ;_;
q3_s1 = session.query(Drink.id).join(*Drink.ingredients.attr).filter(
    Ingredient.name == 'Snark'
).subquery()
q3_s2 = session.query(Drink.id).join(*Drink.ingredients.attr).filter(
    Ingredient.name == 'Whiskey'
).subquery()
q3 = session.query(Drink).join(
    (q3_s1, Drink.id == q3_s1.c.id),
    (q3_s2, Drink.id == q3_s2.c.id)
)
print(', '.join(str(x) for x in q3))