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.

Thanks,

Deba

On Thursday, August 8, 2019 at 5:22:11 PM UTC-7, Debapriya Patra wrote:

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;