title: Flask-sqlalchemy; filter a query by list of foreign keys
body: <p>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.</p>
  
  <p>Can anyone help me point out what I am doing wrong? Or show best practice for doing such a query?</p>
  
  <p>My classes:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>My query:</p>
  
  <pre><code>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)
  </code></pre>