4

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if you are sorting ASC or DESC. This works great for integers and dates but I am wondering how best to do it with text columns, specifically for the first page.

For example, for the first page when sorting by name it would be

SELECT *
FROM users
WHERE first_name > ''
ORDER BY first_name ASC
LIMIT 5;

Then the next page would be

SELECT *
FROM users
WHERE first_name > 'Caal'
ORDER BY first_name ASC
LIMIT 5;

This works great. I am unsure about DESC order though. This seems to work but I am unsure if it is 'correct'.

 SELECT     *
 FROM   users
 WHERE  last_name < 'ZZZ'
 ORDER BY last_name DESC
 LIMIT 5;

Second page

SELECT  *
FROM    users
WHERE   last_name < 'Smith'
ORDER BY last_name DESC
LIMIT 5;

P.S. I am using the jooq support for the seek method and prefer to not have to hack around the native support, so ideally there is a proper parameter to put in the 'ZZZ' place above. i.e. there WHERE part of the clause is mandatory.

  • Your assumption that ZZZ is the last possible name is based on a limited view on names, such as: 1) They always start with an upper case, 2) Names starting with Z don't have a lower case letter at their second position. 3) There do not exist any unicode names, e.g. starting with Ž, 4) etc... Also, you're assuming that there are no two people with the same last name. – Lukas Eder Aug 13 '15 at 11:48
  • I know collation should play a part. AFAIK with sorting in Postgres it will obey collation rules. I guess the question is what is the 'last' possible value? With the Jooq seek() method, is there a way to pass in something other than a string literal such as a Postgres function? – Collin Peters Aug 13 '15 at 15:44
  • Yes, you can pass in any kind of Field to seekBefore(): jooq.org/javadoc/latest/org/jooq/…. I suspect a trick would be to use the very last character of the collation (which is very unlikely to be used in last_name) – Lukas Eder Aug 13 '15 at 16:09
  • Yeah, that is ultimately my question: what is the last character of the collation? :) – Collin Peters Aug 13 '15 at 16:50
  • Hmm, on the other hand, just leave out the predicate. Or in jOOQ's case, leave out the seek() clause. – Lukas Eder Aug 14 '15 at 13:59
0

I don't think just placing an arbitrary value would work, since you have to put a larger if not possibly the largest value.

Perhaps pass in a boolean value with an OR operand (WHERE someflag OR last_name < @key), than way you do not have to pick arbitrary lowest value and largest values.

Otherwise if you can only control what is passed and not the WHERE operation, you'll have to use whatever the max value is for the column based on encoding.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.