Home SQLAlchemy: Query Parent with Efficient Child Search & Filter
Reply: 0

SQLAlchemy: Query Parent with Efficient Child Search & Filter

user1642 Published in April 25, 2018, 12:15 pm

I've seen a few topics that are similar, but can't seem to get any of them to work efficiently.

E.g.: SQLAlchemy: filter many-to-one relationship where the one object has a list containing a specific value

I have a DB structure like so:

class Base(db.Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)

class Items(Base):
    title = db.Column(db.String(250))

class Criteria(Base):  
    _name = db.Column('name', db.String(250), nullable=False, unique=False)

class Response(Base):
    item_id = db.Column(db.Integer(), db.ForeignKey('items.id'))
    criterion_id = db.Column(db.Integer(), db.ForeignKey('criteria.id'))
    value = db.Column('value', db.Text(), nullable=True)
    item = db.relationship('Items', backref=db.backref('responses', lazy='joined', cascade='all, delete'))

I'm trying to filter Items by a series of Response values like so:

filters = [{'c_id':5, 'value': 'yes'}, {'c_id':6, 'value': 'no'}]

items = Items.query
for f in filters:
    items = items.filter(Item.responses.any(and_(Response.value == f['value'], Response.criterion_id == f['c_id'])))

The query succeeds and I get my Items but the query is very slow on datasets as small as 100 Items with less than 2,000 combined Responses.

I'm paginating the results so only 25 results are returned to the user max.

My questions is there a more efficient way to return these results using join()?

Python 3.6, Flask-sqlalchemy, MySQL

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.392516 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO