Reputation: 563
Hello everyone, I'm very new with sqlalchemy and I try to do a searching module from any field that I input from the user by the below code.
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()
Begin with fiters
is a list that contains any input value from the user, and I want to use these values in a list to filter from my join table.
For example, the user has input some product_name
and I want to use this product_name
to filter and get any record value in Products
table that matches to product_name
and also gets the other record from another table (Vendors, Sales, Customers) that related to this 'product_name'.
So how can I do that?
Upvotes: 0
Views: 182
Reputation: 12543
Here's a piece of code that runs a query, based on a set of 'dynamic' filters.
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)
Upvotes: 1