Order by match on multiple columns (and with mutiple conditions)

My issue is I want to order by match on a specific search string.

This is the logic:

If the title, author or isbn are the same as search, show these books first

If the title, author or isbn are the like %search, show these books second

If the title, author or isbn are the like search%, show these books third

I’m using SQLAlchemy, Flask and PostgreSQL

This is the code:

#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()

This is the error I’m getting:

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

Answer

You are mixing string aggregation (which is what string_agg() does) and string concatenation (which is what you want). So Postgres understands your query as an aggregation one, and bad things happen. You can use || to concatenate strings.

Also, you should fix your where clause so it really does pattern matching (as of now, it is looking for exact matches on the parameter) – ilike comes handy to avoid lower() on both operands.

Finally, you can use booleans in the order by clause so shorten the query.

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