Skip to content

Instantly share code, notes, and snippets.

@hanneshapke
Created June 11, 2015 12:14
Show Gist options
  • Save hanneshapke/cf8de0b04d798adda320 to your computer and use it in GitHub Desktop.
Save hanneshapke/cf8de0b04d798adda320 to your computer and use it in GitHub Desktop.
Lightning Talk: SQLAlchemy question for PUB (June 2015)
'''
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment