Pagination query with LIKE function

Asked

Viewed 51 times

0

good afternoon. I have an error in paging query when I pass a search parameter. Here’s the problem: When I’m on page 2 and I do a search for an item on page 20 of mine sql returns nothing. Follows the sql:

SELECT *
  FROM tabela
 WHERE UPPER(name) LIKE UPPER(‘%cadern%’)
 LIMIT 10
OFFSET (2 - 1) * 10

That one LIKE returns two lines, but the OFFSET is to return from line 10 so always comes empty.

Someone’s had this problem before?

database postgresql query offset

  • I don’t understand your problem. Do you, by any chance, mean that throughout your table there are only 2 lines that meet the condition expressed in clause WHERE? If so then your OFFSET is correct. LIMIT and OFFSET apply to the SELECT result.

  • This with this LIKE only returns two lines, but because I am on page 2 of my web application the calculation of OFFSET is wrong. Because when I use this same filter on page 1 of the web application the OFFSET calculation works. The calculation I use is this: (pageSize * (page -1))

  • So the problem is not with Postgresql but with your application that should not be using the correct value in the SQL command.

No answers

Browser other questions tagged database postgresql query offset

You are not signed in. Login or sign up in order to post.