SQLAlchemy – Filtering with multiple conditions

May 30, 2022

I append number of queries into a list and then filter the table using sqlachemies query function and or_. Table consists of warehouses, and I want to query them using names, available storage, prices and services. The logic should be correct but I get error

subquery must return only one column

@search.route('/search/filter', methods = ['POST'])
def filter():
    name = request.form.get('name')
    n_storage = request.form.get('n_storage')
    #MIN PRICE MAX PRICE
    min_p = request.form.get('min_p')
    max_p = request.form.get('max_p')
    #SERVICES
    labelling = True if request.form.get('labelling') else False
    manual_geo_data_entry = True if request.form.get('manual_geo_data_entry') else False
    item_packaging = True if request.form.get('item_packaging') else False 
    palette_packaging = True if request.form.get('palette_packaging') else False
    
    filters = []
    
    if name:
        filters.append(Warehouse.query.filter(Warehouse.name.match(name)))

    if n_storage:
        filters.append(Warehouse.query.filter(Warehouse.volume_available > n_storage)) 

    #FILTERING BASED ON SERVICES
    if labelling:
        filters.append(Warehouse.query.filter(Warehouse.labelling.is_(True)))

    if manual_geo_data_entry:
        filters.append(Warehouse.query.filter(Warehouse.manual_geo_data_entry.is_(True)))

    if item_packaging:
        filters.append(Warehouse.query.filter(Warehouse.item_packaging.is_(True)))

    if palette_packaging:
        filters.append(Warehouse.query.filter(Warehouse.palette_packaging.is_(True)))

    results = Warehouse.query.filter(or_(*filters)).all()

    return render_template('search/search.html', title = 'Search', data = results)

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery must return only one column
LINE 3: WHERE (SELECT "PilotApp_warehouse_test".id, "PilotApp_wareho...
              ^

[SQL: SELECT "PilotApp_warehouse_test".id AS "PilotApp_warehouse_test_id", "PilotApp_warehouse_test".name AS "PilotApp_warehouse_test_name", "PilotApp_warehouse_test".volume_available AS "PilotApp_warehouse_test_volume_available", "PilotApp_warehouse_test".volume_total AS "PilotApp_warehouse_test_volume_total", "PilotApp_warehouse_test".labelling AS "PilotApp_warehouse_test_labelling", "PilotApp_warehouse_test".manual_geo_data_entry AS "PilotApp_warehouse_test_manual_geo_data_entry", "PilotApp_warehouse_test".item_packaging AS "PilotApp_warehouse_test_item_packaging", "PilotApp_warehouse_test".palette_packaging AS "PilotApp_warehouse_test_palette_packaging", "PilotApp_warehouse_test".address AS "PilotApp_warehouse_test_address", "PilotApp_warehouse_test".email AS "PilotApp_warehouse_test_email", "PilotApp_warehouse_test".phone AS "PilotApp_warehouse_test_phone", "PilotApp_warehouse_test".owner AS "PilotApp_warehouse_test_owner" 
FROM "PilotApp_warehouse_test" 
WHERE (SELECT "PilotApp_warehouse_test".id, "PilotApp_warehouse_test".name, "PilotApp_warehouse_test".volume_available, "PilotApp_warehouse_test".volume_total, "PilotApp_warehouse_test".labelling, "PilotApp_warehouse_test".manual_geo_data_entry, "PilotApp_warehouse_test".item_packaging, "PilotApp_warehouse_test".palette_packaging, "PilotApp_warehouse_test".address, "PilotApp_warehouse_test".email, "PilotApp_warehouse_test".phone, "PilotApp_warehouse_test".owner 
FROM "PilotApp_warehouse_test" 
WHERE "PilotApp_warehouse_test".manual_geo_data_entry IS true) OR (SELECT "PilotApp_warehouse_test".id, "PilotApp_warehouse_test".name, "PilotApp_warehouse_test".volume_available, "PilotApp_warehouse_test".volume_total, "PilotApp_warehouse_test".labelling, "PilotApp_warehouse_test".manual_geo_data_entry, "PilotApp_warehouse_test".item_packaging, "PilotApp_warehouse_test".palette_packaging, "PilotApp_warehouse_test".address, "PilotApp_warehouse_test".email, "PilotApp_warehouse_test".phone, "PilotApp_warehouse_test".owner 
FROM "PilotApp_warehouse_test" 
WHERE "PilotApp_warehouse_test".item_packaging IS true)]

>Solution :

You should not pass to filter queries, but only conditions to avoid subqueries. I think it should work for you:

@search.route('/search/filter', methods = ['POST'])
def filter():
    name = request.form.get('name')
    n_storage = request.form.get('n_storage')
    #MIN PRICE MAX PRICE
    min_p = request.form.get('min_p')
    max_p = request.form.get('max_p')
    #SERVICES
    labelling = True if request.form.get('labelling') else False
    manual_geo_data_entry = True if request.form.get('manual_geo_data_entry') else False
    item_packaging = True if request.form.get('item_packaging') else False 
    palette_packaging = True if request.form.get('palette_packaging') else False
    
    filters = []
    if name:
        filters.append(Warehouse.name.match(name))
    if n_storage:
        filters.append(Warehouse.volume_available > n_storage)
    #FILTERING BASED ON SERVICES
    if labelling:
        filters.append(Warehouse.labelling.is_(True))
    if manual_geo_data_entry:  
        filters.append(Warehouse.manual_geo_data_entry.is_(True))
    if item_packaging:
        filters.append(Warehouse.item_packaging.is_(True))
    if palette_packaging:
        filters.append(Warehouse.palette_packaging.is_(True))
    results = Warehouse.query.filter(or_(*filters)).all()
    return render_template('search/search.html', title = 'Search', data = results)