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

Question

我的问题是我想在特定的搜索字符串上按匹配排序

这就是逻辑：

如果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()

这就是我得到的错误：

column "books.title" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

您正在混合字符串聚合（这是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