I'm working on a web application using Flask-SQLAlchemy.

My model is:

```
    class Product(db.Model):
    __tablename__ = 'product'
    id = db.Column(db.Integer, primary_key=True)
    ...

    product_to_category = db.relationship('ProductCategory',backref='product', lazy=True)
    shop_products = db.relationship('ShopProducts', backref='product', lazy=True)

    def __repr__(self):
        return '<Product: {}>'.format(self.id)

class ProductDescription(db.Model):
    __tablename__ = 'product_description'
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))   
    name = db.Column(db.String(255), index=True, unique=False)
    ...

    def __repr__(self):
        return '<ProductDescription: {}>'.format(self.product_id)

class ProductImage(db.Model):
    __tablename__ = 'product_image'
    product_image_id = db.Column(db.Integer, primary_key=True, unique=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), index=True, unique=False)
    image = db.Column(db.String(255), nullable=True)
    ...

    def __repr__(self):
        return '<Product Image: {}>'.format(self.product_image_id)

class Category(db.Model):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True, unique=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'), index=True, unique=False)
    ...

    products_to_category = db.relationship('ProductCategory',backref='category', lazy=True)

    def __repr__(self):
        return '<Category: {}>'.format(self.id)

class ProductCategory(db.Model):
    __tablename__ = 'product_category'
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))  
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    def __repr__(self):
        return '<Product Category: {}>'.format(self.product_id)

class Shops(db.Model):
    __tablename__ = 'shop'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), index=True, unique=False)
    description = db.Column(db.Text)
    ...

    shop_products = db.relationship('ShopProducts', backref='shop', lazy='joined')

    def __repr__(self):
        return '<Shop: {}>'.format(self.id)

class ShopProducts(db.Model):
    __tablename__ = 'shop_products'      
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey('shop.id'))
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'),index=True)  
    price = db.Column(db.Float,index=True)
    ...

    def __repr__(self):
        return '<Shop Products: {}>'.format(self.price)

```

I want to make a query with filters to show all the products in a category and simultaneously show the lowest price from the 'shop\_products' table.

My 'shop\_products' table:

```
id shop_id product_id  price

1     1        1       34
2     1        2       56
3     1        3       67
4     2        1       35
5     2        2       55
6     2        3       68
7     3        1       32
8     3        2       58
9     3        4       69
10    4        1       101

```

I try with the following code but i see all products in my html page

```
@site.route('/category/<int:id>', methods=['GET', 'POST'])
def category(id):
    subq = ProductCategory.query.filter_by(category_id=id).subquery()
    product=db.session.query(ProductDescription,ProductImage.image,ShopProducts.price).\
    select_entity_from(subq).\
    filter(ProductDescription.product_id == ProductCategory.product_id). \
    filter(ProductImage.product_id ==  ProductCategory.product_id). \
    filter(ShopProducts.product_id ==  ProductCategory.product_id).\
    order_by(ShopProducts.price.asc())
    return render_template('site/category.html', product=product)

```

What can i do? Any ideas?