Need Help To Convert Postgres UNNEST query in JOOQ

[Debapriya Patra]

Hi Knut,

I have a requirement where i wanted to return the result with sorted order of the IDs i am passing in to the query to fetch the data from DB.

I wrote this POSTGRES query and able to get the result with same order as the IDs i am passing. 

So how can i convert this query into JOOQ format ? Can you please help me ?

Thanks,
Deba

with

 

        cta1 as (

             select array[

                    '125d7988-14d7-4898-bf89-0b54eb8aa317',  

                    '63028718-bf33-4e3f-a6bd-ef7c0e941205',  

                    'a1266416-4e7d-4426-9b9d-e5c66d15ffbc',  

                    '940b3c50-7a5e-49d5-8105-b3ddfdfde9c1',  

                    '86a1647f-a24d-45c0-ae04-0e19c2fd10e2'] as a),

        cta2 as (            

             select

                unnest(a)::uuid id,

                generate_series(1, array_length(a, 1)) idx

             from cta1)

select

        idx,

        d.id,

        d.title

from cta2

join public.deck d on d.id = cta2.id order by idx;

[Debapriya Patra]

The below values will be pass as the binding value to the query. 

[Knut Wannheden]

jOOQ has a few PostgreSQL specific functions in the class PostgresDSL (including arrayLength()) and also has the functions DSL#unnest() and DSL#generateSeries(). However, these methods are not applicable in the context of your query, as they return a Table type, which jOOQ doesn't support as an input to select().

With jOOQ you can however typically solve problems like this by relying on the SQL templating mechanisms. So this would be one way to do it:

String[] values = new String[] { "125d7988-14d7-4898-bf89-0b54eb8aa317", "63028718-bf33-4e3f-a6bd-ef7c0e941205" };

ctx.with("cta1").as(

    select(array(values).as("a"))

)

.with("cta2").as(

    select(

        function("unnest", String.class, field("a")).cast(SQLDataType.UUID).as("id"),

        function("generate_series", Integer.class, val(1), function("array_length", Integer.class, field("a"), val(1))).as("idx")

    )

    .from("cta1")).

select(field("id"), field("idx")).from("cta2").fetch();

Note that I omitted the "public.deck" part of your query.

Hope this helps,

Knut

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7bcd822f-1d43-4d1b-8cb3-351138d0e682%40googlegroups.com.

[Debapriya Patra]

Hi Kunt,

Thanks for the reply. Looks like this is causing a performance issue So
planned to change to a user defined function and call that from JOOQ.

*1. I am defining my own function now in postgres:*

*CREATE* *OR* *REPLACE* *FUNCTION* *public*.custom_order(anyarray,
anyelement)

*RETURNS* *INT* *AS*

$$

*SELECT* i *FROM* (

*SELECT* generate_series(array_lower($1,1),array_upper($1,1))

) g(i)

*WHERE* $1[i] *=* $2

*LIMIT* 1;

$$ *LANGUAGE* *SQL* *IMMUTABLE*;

*I tested my function with the below query in which works fine.*

*select*

*public*.deck_history.id,

*public*.deck_history.foreign_id,

*public*.deck_history.title,

*public*.deck_history.deck_type,

*public*.deck_history.confidential,

*public*.deck_history.certified,

*public*.deck_history.deck_id,

*public*.deck_history.created,

*public*.deck_history.updated,

*public*.deck_history.original_created,

*public*.deck_history.original_updated,

*public*.deck_history.num_cards,

*public*.deck_history.num_images,

*public*.deck_history.edition,

*public*.deck_history.deleted

*from* *public*.deck_history

*where* *public*.deck_history.id *in* (

'c02615e9-2ff4-47a0-ab56-103e1be7a20a',
'ae0b0756-8d00-47c9-aa74-a73a57f5e021',

'58ef7c63-3473-43c4-9e84-42e1b337c7d7',
'e9c8f273-5010-41f9-8ad1-eb5f3d661bbc',

'7d4a9263-21ea-4427-8498-12ea5f992157',
'1ec3cd36-514b-4684-920d-8cb23cea4e4e',

'a11fe2f2-07a2-4cb4-baf1-adf37206720f',
'3cd7458f-dd1d-4d34-9bea-0c1664b4b571',

'db692534-2bad-4503-b1f2-030c684a6c37',
'907875c2-00cc-47e9-bc89-1eda3055ae25'

)

*order* *by* *public*.custom_order(

*cast*(
'{c02615e9-2ff4-47a0-ab56-103e1be7a20a,ae0b0756-8d00-47c9-aa74-a73a57f5e021,58ef7c63-3473-43c4-9e84-42e1b337c7d7,e9c8f273-5010-41f9-8ad1-eb5f3d661bbc,7d4a9263-21ea-4427-8498-12ea5f992157,1ec3cd36-514b-4684-920d-8cb23cea4e4e,a11fe2f2-07a2-4cb4-baf1-adf37206720f,3cd7458f-dd1d-4d34-9bea-0c1664b4b571,db692534-2bad-4503-b1f2-030c684a6c37,907875c2-00cc-47e9-bc89-1eda3055ae25}'
*as* *uuid*[]), *public*.deck_history.id)

*2. Trying to call the same function from my repository using JOOQ:*
UUID[] deckIdArray = deckIds.stream().toArray(UUID[]::new);
Field<Integer> field = customOrder(deckIdArray, DECK_HISTORY.ID);
SelectSeekStep1<DeckHistoryRecord, Integer> deckHistoryRecords =
dsl.selectFrom(DECK_HISTORY).where(DECK_HISTORY.ID.in(deckIds)).orderBy(field);
ResultSet rs = deckHistoryRecords.fetchResultSet();

*3. Trying to hit the service to get the response from DB using this code,
its giving me the error below.*
org.jooq.exception.SQLDialectNotSupportedException: Type class
org.jooq.impl.TableFieldImpl is not supported in dialect POSTGRES
at org.jooq_3.11.8.POSTGRES.debug(Unknown Source)
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:884)
at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:823)
at
org.jooq.impl.DefaultBinding$AbstractBinding.sqlCast(DefaultBinding.java:618)
at
org.jooq.impl.DefaultBinding$AbstractBinding.sql(DefaultBinding.java:707)

*And when i tried to pull the query generated by JOOQ, it looks like below.*
select
"public"."deck_history"."id",
"public"."deck_history"."foreign_id",
"public"."deck_history"."title",
"public"."deck_history"."deck_type",
"public"."deck_history"."confidential",
"public"."deck_history"."certified",
"public"."deck_history"."deck_id",
"public"."deck_history"."created",
"public"."deck_history"."updated",
"public"."deck_history"."original_created",
"public"."deck_history"."original_updated",
"public"."deck_history"."num_cards",
"public"."deck_history"."num_images",
"public"."deck_history"."edition",
"public"."deck_history"."deleted"
from "public"."deck_history"
where "public"."deck_history"."id" in (
'c02615e9-2ff4-47a0-ab56-103e1be7a20a',
'ae0b0756-8d00-47c9-aa74-a73a57f5e021',
'58ef7c63-3473-43c4-9e84-42e1b337c7d7',
'e9c8f273-5010-41f9-8ad1-eb5f3d661bbc',
'7d4a9263-21ea-4427-8498-12ea5f992157',
'1ec3cd36-514b-4684-920d-8cb23cea4e4e',
'a11fe2f2-07a2-4cb4-baf1-adf37206720f',
'3cd7458f-dd1d-4d34-9bea-0c1664b4b571',
'db692534-2bad-4503-b1f2-030c684a6c37',
'907875c2-00cc-47e9-bc89-1eda3055ae25'
)
order by "public"."custom_order"(

cast('{"c02615e9-2ff4-47a0-ab56-103e1be7a20a","ae0b0756-8d00-47c9-aa74-a73a57f5e021","58ef7c63-3473-43c4-9e84-42e1b337c7d7","e9c8f273-5010-41f9-8ad1-eb5f3d661bbc","7d4a9263-21ea-4427-8498-12ea5f992157","1ec3cd36-514b-4684-920d-8cb23cea4e4e","a11fe2f2-07a2-4cb4-baf1-adf37206720f","3cd7458f-dd1d-4d34-9bea-0c1664b4b571","db692534-2bad-4503-b1f2-030c684a6c37","907875c2-00cc-47e9-bc89-1eda3055ae25"}'
as uuid[]),
'"public"."deck_history"."id"'
)

Can you please help me figuring out like whats going wrong here ? I see a
difference with the query generated by JOOQ *'"public"."deck_history"."id"'
*which should have been *"public"."deck_history"."id". Becasue when i
change to **"public"."deck_history"."id", *i am able to run that query
against DB successfully. So some . mistake i am doing with that function
argument while passing.

It will . be great if you can help me figuring that out.

*Also one more issue i see is, the function is generated with
deprecated not sure why? I am attaching the screenshot for that.*

*Thanks a lot for your solution and quick response. Its really great.*

[image: Screen Shot 2019-08-11 at 8.51.17 AM.png]

*Thanks,*
*Deba*

[Knut Wannheden]

Before answering your questions I would like to know if you have already looked at the PostgreSQL ARRAY_POSITION() function and the WITH ORDINALITY clause for the UNNEST() function. I believe they could be quite useful for your problem.

Knut

On Sun, Aug 11, 2019, 18:13 Debapriya Patra <debapri...@gmail.com> wrote:

Hi Kunt,

Thanks for the reply.  Looks like this is causing a performance issue So planned to change to a user defined function and call that from JOOQ.

1. I am defining my own function now in postgres:

CREATE OR REPLACE FUNCTION public.custom_order(anyarray, anyelement)

RETURNS INT AS

$$

  SELECT i FROM (

     SELECT generate_series(array_lower($1,1),array_upper($1,1))

  ) g(i)

  WHERE $1[i] = $2

  LIMIT 1;

$$ LANGUAGE SQL IMMUTABLE;

[Debapriya Patra]

Nope i have not tried. Basically i wanted a way to sort the data by the input order i am sending to DB. So i thought of using this array index.

https://wiki.postgresql.org/wiki/Array_Index

Why i likes this is, its basically not doing anything on the DB just sorting with the array index which will be performant i believe.

Do you think using PostgreSQL ARRAY_POSITION() function and the WITH ORDINALITY clause for the UNNEST() function will better than array index ?

Cheers,

Debapriya Patra
650.933.6852

--
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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAFx%3DKgcra_4YUOfsUe7wsbj1HwF%3DR7OXZOJp62VzpGnmMR%2BLEg%40mail.gmail.com.

[Knut Wannheden]

See comments below.

On Sun, Aug 11, 2019 at 10:14 PM Debapriya Patra <debapri...@gmail.com> wrote:

Nope i have not tried. Basically i wanted a way to sort the data by the input order i am sending to DB. So i thought of using this array index.

https://wiki.postgresql.org/wiki/Array_Index

Why i likes this is, its basically not doing anything on the DB just sorting with the array index which will be performant i believe.

Do you think using PostgreSQL ARRAY_POSITION() function and the WITH ORDINALITY clause for the UNNEST() function will better than array index ?

AFAICT your CUSTOM_ORDER() function does exactly what ARRAY_POSITION() already does, but probably in a less efficient way.

What your original query did with UNNEST() and GENERATE_SERIES() would be covered by the WITH ORDINALITY clause I mentioned, but probably more efficient.

Which approach you take is of course up to you.

Hope this helps,

Knut

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAM2NkrTt%2BE6cjUCRHLUp3qWHcnFooyRYEOsWOqc%2BKV5UPuxD-w%40mail.gmail.com.