关于Flask-SQLAlchemy中的多关键字过滤查询

单字段过滤(某一个键符合即可)

from sqlalchemy import or_

all_results =Model.query.filter(
                            or_(Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
                            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
                            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
                            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else "")
                            ).all()

多字段过滤(多个键同时符合)

all_results =Model.query.filter(
                            Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
                            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
                            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
                            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else ""
                            ).all()

先根据一个键过滤，然后再单字段过滤

from sqlalchemy import or_

all_results =Model.query.filter_by(id=_id).filter(
                            or_(Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
                            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
                            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
                            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else "")
                            ).all()

先根据一个键过滤，然后再多字段过滤

all_results =Model.query.filter_by(id=_id).filter(
                            Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
                            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
                            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
                            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else ""
                            ).all()

search_value为单过滤字段(模糊过滤字段)，
如果模糊过滤字段为空，则进行精准过滤，
如果精准过滤字段为空，则进行模糊过滤，
如果模糊字段与精准字段都不为空，则先进行模糊过滤，然后对结果再进行精准过滤。

from sqlalchemy import or_

if search_value == '':
    all_results =Model.query.filter_by(id=_id).filter(
        Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
        Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
        Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
        Model.key_4.like("%" + words_4 + "%") if words_4 is not None else ""
            ).all()
elif words_1 == '' and words_2 == '' and words_3 == '' and words_4 == '':
    all_results =Model.query.filter_by(id=_id).filter(
        or_(Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else "")
        ).all()
else:
    all_results =Model.query.filter_by(id=_id).filter(
        or_(Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
            Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
            Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
            Model.key_4.like("%" + words_4 + "%") if words_4 is not None else "")
        ).filter(Model.key_1.like("%" + words_1 + "%") if words_1 is not None else "",
                 Model.key_2.like("%" + words_2 + "%") if words_2 is not None else "",
                 Model.key_3.like("%" + words_3 + "%") if words_3 is not None else "",
                 Model.key_4.like("%" + words_4 + "%") if words_4 is not None else ""
        ).all()