Hi Many,
Thanks for the clarifications. I get it now. In any case, you have to
append the ID column for all of your keyset paginating queries, just
because you need some uniqueness criteria in your sorts. This is unrelated
with name being nullable.
Since you know your domain and thus your possible data points, you could
use coalesce as a workaround here. For example:
ctx.selectFrom(account)
.orderBy(coalesce(account.name, inline("ZZZZZZZZZZZZZZZZZZZZ")),
account.id)
.seekAfter(nameValue, idValue)
.fetch();
I put ZZZZ... as a placeholder there. You could use any possible non-null
string that is guaranteed to be ordered after (or before, depending on how
you want to handle nulls) all your data points.
Obviously, this is a workaround. A more thorough fix should be implemented
via
https://github.com/jOOQ/jOOQ/issues/2786
I hope this helps,
Lukas
On Thu, May 2, 2019 at 4:21 PM <m...@streetcontxt.com> wrote:
> Hi Lukas!
>
> Thanks for getting back to me! Yes, seeking on a nullable column by itself
> wouldn't make much sense, so I figure I would have to append a non-nullable
> unique column to my queries. Let me provide a more detailed example.
>
>
> For example:
>
> Say I have a table of accounts like this:
>
> CREATE TABLE account (
> id bigint primary key,
> email text non-null unique, -- assume an account email address is
> required but the name is not
> name text,
> ... other fields
> );
>
>
> Some example rows in this table:
>
> |id |email |name |other fields |
> ------------------------------------
> |1 |al...@gmail.com |Alice Eve |..... |
> |2 |b...@gmail.com |NULL |..... |
> |3 |c...@gmail.com |Carl Frank |..... |
> |4 |d...@gmail.com |NULL |..... |
>
> .
> .
> .
>
> My use case involves fetching data from this database table to display in
> a table on the client side. Since this table can have a large number of
> rows, I've chosen to use keyset pagination to order the result set and
> serve up results in small batches.
>
>
> With typical usage of keyset pagination, if I wanted to get the list of
> accounts ordered by ID, I'd do something like:
>
> DSL.(...).selectFrom(account).orderBy(account.id).seekAfter(<some id value
> >).fetch();
>
> and since ID is the primary key, it's unique and non-nullable so I'd get
> my result set as expected.
>
>
> However, say I want to display accounts on the client side ordered by the
> name column, which is this case is a nullable column. Doing something like:
>
> DSL.(...).selectFrom(account).orderBy(account.name).seekAfter(<some name
> value>).fetch();
>
> would produce unexpected results, since some of the name fields are null
> and null values themselves aren't unique.
>
>
> What I'd like to do is something like this:
>
> DSL.(...).selectFrom(account).orderBy(account.name, account.id).seekAfter
> (<some name value>, <some id value>).fetch();
>
> However, the generated SQL for the predicate will have the potential to
> compare a null value to a non-null value for the name field. So this query
> will fetch all accounts with a non-null name field and return them
> according to the order by clause. I'd miss out on any accounts with a null
> name field.
>
>
> Looking at the generated SQL, the generated predicate looks like:
>
> where (account.first_name > <some non-null account name> ) or
> (account.first_name
> = <some non-null account name> and account.id > <some non-null account id
> >)
>
> When that query is comparing an account record in the table that has a
> null name, it won't pass the predicate, so that record won't ever be
> returned. So I can't currently use keyset pagination/seekAfter to fetch a
> list of accounts and display them in a table when the client asks for the
> list of accounts ordered by the name field.
>
>
> Hope that makes sense and provides a better look at my use case. Any
> thoughts?
>
> Thanks,
> Mani
>
> This e-mail message is intended for the named recipient(s) above, and may
> contain information that is privileged, confidential and/or exempt from
> disclosure under applicable law. If you have received this message in
> error, or are not the named recipient(s), please do not read the content.
> Instead, immediately notify the sender and delete this e-mail message. Any
> unauthorized use, disclosure or distribution is strictly prohibited.
> Quantify Labs Inc and the sender assume no responsibility for any errors,
> omissions, or readers' misinterpretations and/ or understanding of the
> content or transmission of this email.
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
