如何从sqlalchemy中的联接表进行筛选？

Question

大家好，我是sqlalchemy的新手，我尝试从用户输入的任何字段通过下面的代码执行搜索模块

    filters = []

    if 'inputVendorName' in inputfield:
        filters.append(Vendors.vendor_name.contains(inputfield['inputVendorName']))

    if 'inputProductName' in inputfield:
        filters.append(Product.product_name.contains(inputfield['inputProductName']))

    if 'inputCustomerName' in inputfield:
        filters.append(Customers.customer_name.contains(inputfield['inputCustomerName']))

    if 'inputSalePrice' in inputfield:
        filters.append(Sales.price.contains(inputfield['inputSalePrice']))

    # jointable --> how to join table
    results = jointable.query.filter(db.or_(*filters)).all()

以fiters开始是一个包含用户输入值的列表，我想在列表中使用这些值从联接表中筛选

例如，用户输入了一些product_name，我想使用这个product_name来过滤和获取Products表中与product_name匹配的任何记录值，并且还从另一个表（供应商、销售、客户）中获取与此“产品名称”相关的其他记录

那我该怎么做呢

Answer 1

下面是一段基于一组“动态”过滤器运行查询的代码

filters = []

# this is an example: 
inputfield = {
    "inputVendorName": "J", 
    "inputProductName": "Pen", 
    "MinPrice": 10
}

if 'inputVendorName' in inputfield:
    filters.append(Vendor.vendor_name.contains(inputfield["inputVendorName"]))

if  'inputProductName' in inputfield:
    filters.append(Product.product_name.contains(inputfield["inputProductName"]))

if 'MinPrice' in inputfield:
    filters.append(Sale.price > inputfield["MinPrice"])

base_query = session.query(Customer, Product, Vendor, Sale).filter(
    Sale.customer_id == Customer.customer_id, Vendor.vendor_id == Product.vendor_id, Sale.product_id == Product.product_id)

for res in base_query.filter(*filters).all(): 
    print(res)