Lightning Talk: SQLAlchemy question for PUB (June 2015)

flask_m2m_problem.py

'''
This code example is a little gist example of the m2m problem
I am facing with flask and sqlalchemy. The purpose of this short
code sample is to select one or many grocery lists (List) based
on the grocery items (Grocery).

All query examples return either an empty list or all lists which contain
any of the items (OR selection instead of AND)

Below I have listed three approaches I have tried without any luck.
'''

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///sqlite.db'
db = SQLAlchemy(app)

# MODELS #

grocery_to_list = db.Table(
    'grocery_to_list',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('grocery_id', db.Integer, db.ForeignKey('grocery.id')),
    db.Column('list_id', db.Integer, db.ForeignKey('list.id'))
    )


class Grocery(db.Model):
    __tablename__ = 'grocery'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    def __repr__(self):
        return '<{}>'.format(self.name)


class List(db.Model):
    __tablename__ = 'list'
    id = db.Column(db.Integer, primary_key=True)
    groceries = db.relationship(
        Grocery, secondary=grocery_to_list, backref="list")

    def __repr__(self):
        return '<List containing {}>'.format(self.groceries)

with app.app_context():
    db.create_all()

    # create grocery items
    beer = Grocery(name='beer')
    bananas = Grocery(name='bananas')
    bread = Grocery(name='bread')

    # set up sample grocery lists
    first_list = List()
    first_list.groceries.append(beer)
    first_list.groceries.append(bananas)

    second_list = List()
    second_list.groceries.append(bread)
    second_list.groceries.append(bananas)
    db.session.add_all([beer, bananas, bread, first_list, second_list])
    db.session.commit()

    '''
    first approach using the in_ operator. This query returns all
    lists with at least one grocery item (OR selected)
    '''
    grocery_ids = [bread.id, bananas.id]
    query = db.session.query(List).filter(
        List.groceries.any(Grocery.id.in_(grocery_ids)))

    print ('First approach returns:\n', query.all())
    # First approach returns:
    # [<List containing [<beer>, <bananas>]>,
    # <List containing [<bread>, <bananas>]>]

    '''
    second approach using chained filters returns an empty list
    since it first selects all breads and then banana items
    The query seems to treat them as mutual exclusive and therefore
    return an empty list
    '''
    query = List.query.join(
        grocery_to_list, (grocery_to_list.c.list_id == List.id))
    for grocery_id in grocery_ids:
        query = query.filter(grocery_to_list.c.grocery_id == grocery_id)
    print ('Second approach returns:\n', query.all())
    # Second approach returns:
    #  []

    '''
    third approach is using the join and the in_ operator
    the query result is the same as in the first approach
    and the in_ seems to work as OR
    '''
    query = List.query.join(
        grocery_to_list, (grocery_to_list.c.list_id == List.id)).filter(
        List.groceries.any(Grocery.id.in_(grocery_ids)))
    print ('Third approach returns:\n', query.all())
    # Third approach returns:
    # [<List containing [<bread>, <bananas>]>,
    # <List containing [<beer>, <bananas>]>]

    # clean up the db mess
    db.drop_all()