CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Drublic
Drublic

Reputation: 709

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

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)

Upvotes: 3

Views: 3175

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 53017

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))

Upvotes: 5

Related Questions