首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >多条件SQLAlchemy滤波

多条件SQLAlchemy滤波
EN

Stack Overflow用户
提问于 2022-05-30 11:21:56
回答 1查看 123关注 0票数 0

我将查询数附加到列表中,然后使用sqlachemies查询函数和or_过滤表。表由仓库组成,我想使用名称、可用存储、价格和服务来查询它们。逻辑应该是正确的,但我错了。

子查询只能返回一列

代码语言:javascript
运行
复制
@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)
代码语言:javascript
运行
复制
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)]
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-05-30 11:30:09

不应传递给filter查询,而应只传递避免子查询的条件。我认为它应该适用于你:

代码语言:javascript
运行
复制
@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)
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/72433527

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档