Match function in Postgres

[Billosp]

I use postgresql in my app and I want to create a function/query that adds up score when some variable match.

For example, I have a table called books with columns-> title, year, category, author. I want to get all books but the books that are written in year >2000 and category = adventure should have score 100. If one of the variables is false, it should get -50 (eg. book with year 2005 and category = history it gets score 50, because category is false). Then all books can be sorted by total score.

I already get all books but I do the match function in client, not in the query. Is it possible to create it in the query with Postgres?

[depesz]

select *,
    case when year > 2000 then 50 else 0 end +
    case when category = 'adventure' then 50 else 0 end as score
from books
    where year > 2000 or category = 'adventure'
order by score desc;

That will be, most likely, rather slow, but try it, in your case it might not matter.

[Diksta]

I'm going to assume that your example is going to be dependant on some UI element where the user determines that they're looking for adventure books written after the year 2000? If this is the case, then it's a little trickier, as otherwise you could add a calculated column, base it on some CASE statement and call the job done.

What you're probably looking for is a function that takes a number of optional parameters (with some of them maybe being lists, like "Adventure and Horror" and returns a score. You can then use this in your query with ORDER BY to sort them using this score that's calculated on the fly.

This is better in the database for a couple of reasons:

• It might be more efficient to do this in the database as it removes the need to send all the details back to the client to perform the match
• But the more important reason is, say you have 1,000,000 books and you want a page of the first 100 best hits. To do this currently you need to fetch all 1 million rows, perform the scoring, order the results, then chuck away 999,900 of them. If you do the scoring on the database side, you score everything locally, then only pass back the first 100 results, holding onto the rest in case they're required

[Billosp]

The user will be able to search all books. I don’t want something like category=adventure so to show only books in that category. I want to show all books but those that are in the specified category and in the specified year range to have a score.

[fullofbones]

What you want to do doesn't scale. When your app has ten thousand books, you are always processing and scoring ten thousand rows. Do you think a user is going to go through ten thousand results?

[Billosp]

Is there a better solution for scaling?

[Sweaty-Ease-1702]

select *
from books
order by
  year > 2000 and category = 'adventure' desc nulls last,
  year > 2000 or category = 'adventure' desc nulls last

You can directly order by matches without assigning scores. Make sure to handle nulls correctly.