Flask-sqlalchemy; filter a query by list of foreign keys

user_587650 0
Viewed 2114

My postgres db has classes Category and Product. A Category can have many Products. I am trying to get the products that belongs to a list of multiple categories (full_tree is a list of the id's of the categories). My attempted queries doesn't seem to work, as they always return all the products in the db, i.e. it seems the filtering part doesn't work.

Can anyone help me point out what I am doing wrong? Or show best practice for doing such a query?

My classes:

class Parent_product(SearchableMixin, db.Model):
    __searchable__ = ['name']
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String)

    manufacturer_id = db.Column(db.Integer, db.ForeignKey('manufacturer.id'))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

class Category(db.Model, BaseNestedSets):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(400), index=True, unique=True)
    products = db.relationship("Parent_product", backref='category', lazy='dynamic')

My query:

def products(parent_category = None):
    # init query
    my_query = Parent_product.query

    if parent_category:

        # Find selected category from db
        category = Category.query.filter_by(name = parent_category).first()

        # Get list of all category id's to be used in query
        full_tree = []
        full_tree.append(category.id)
        for children in category.get_children():
            full_tree.append(children.id)

        # Add filter to query
        my_query.filter_by(category_id = Category.id.in_(full_tree))

    # Execute query, get list of products, paginate
    products = my_query.paginate('PAGE', 'PRODUCTS_PER_PAGE', False)
flask-sqlalchemy flask sqlalchemy python
1 Answers

Query.filter_by() is meant for simple equality checks against the primary entity, or the latest entity that was the target of a Query.join(). If you need more complex predicates, use Query.filter(). In that light

my_query.filter_by(category_id = Category.id.in_(full_tree))

will produce SQL along the lines of

FROM parent_product p, category c
WHERE p.category_id = (c.id IN (...))

Whether or not that produces an error depends on the DBMS in use. PostgreSQL would complain, but for example SQLite would happily execute that. But, in your case that does not matter, because you do not assign the new query anywhere. SQLAlchemy Query objects are generative, and operations on them return new queries.

So the fix is to assign the new query to a name, and to not include the Category table in the query:

my_query = my_query.filter(Parent_product.category_id.in_(full_tree))
user_26816320
Related