在多个列上按匹配顺序排序（并带有多个条件）

debugcn 发表于 Dev

古斯塔沃·席尔瓦（Gustavo Silva）

我的问题是我想按特定的搜索字符串进行匹配排序。

这是逻辑：

如果title, author或isbn与相同search，请先显示这些书

如果title, author或isbn相似%search，请再显示这些书

如果title, author或isbn相似search%，则将这些书显示在第三位

我正在使用SQLAlchemy，Flask和PostgreSQL

这是代码：

#keep an unformatted version of search, to be able to order
    unformattedSearch = search
    search = "%" + search + "%"

    result = db.execute("SELECT title, author, isbn FROM books WHERE \
        LOWER(title) LIKE LOWER(:search) OR \
        LOWER(isbn) LIKE LOWER(:search) OR \
        LOWER(author) LIKE LOWER(:search) \
        ORDER BY \
        CASE \
            WHEN title = :search THEN 0 \
            WHEN author = :search THEN 1 \
            WHEN isbn = :search THEN 2 \
            \
            WHEN title LIKE string_agg(:unformattedSearch,'%') THEN 3\
            WHEN author LIKE string_agg(:unformattedSearch,'%') THEN 4 \
            WHEN isbn LIKE string_agg(:unformattedSearch,'%') THEN 5 \
            \
            WHEN title LIKE string_agg('%',:unformattedSearch) THEN 6 \
            WHEN author LIKE string_agg('%',:unformattedSearch) THEN 7 \
            WHEN isbn  LIKE string_agg('%',:unformattedSearch) THEN 8 \
        ELSE 9 \
        END",
        {"search": search, "unformattedSearch": unformattedSearch}).fetchall()

这是我得到的错误：

“ books.title”列必须出现在GROUP BY子句中或在聚合函数中使用

专线小巴

您正在混合字符串聚合（这是做什么string_agg()）和字符串串联（这是您想要的）。因此Postgres将您的查询理解为一种汇总查询，并且会发生不好的事情。您可以||用来连接字符串。

另外，您应该修复where子句，使其真正进行模式匹配（到目前为止，它正在寻找参数的完全匹配项）-ilike方便避免lower()两个操作数。

最后，您可以在order by子句中使用布尔值，从而缩短查询时间。

select title, author, isbn 
from books 
where  
       title  ilike '%' || :search || '%' 
    or isbn   ilike '%' || :search || '%' 
    or author ilike '%' || :search || '%' 
order by 
    (title  = :search) desc,
    (author = :search) desc,
    (isbn   = :search) desc,
    (title  ilike :search || '%') desc,
    (author ilike :search || '%') desc,
    (isbn   ilike :search || '%') desc,
    (title  ilike '%' || :search) desc,
    (author ilike '%' || :search) desc,
    (isbn   ilike '%' || :search) desc

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。