Need Help To Convert A SQL Query In Jooq

[Debapriya Patra]

Hi Lukas,

I am trying to convert the below SQL query in Jooq so that I can make use of this query in my application. I am kind of stuck in the join part but it would be really great if can help me converting this query in Jooq.

with dic as (select deck_id, image_count from deck_image_count where deck_id = '66bebe66-a0c4-45f0-8205-a80ef8f41f07')

select 

"public"."deck"."id" as "id", 

"public"."deck"."deck_id" as "deck_id", 

"public"."deck"."title" as "title", 

"public"."deck"."deck_type" as "deck_type", 

"public"."deck"."created" as "created", 

"public"."deck"."updated" as "updated", 

"public"."deck"."original_created" as "original_created", 

"public"."deck"."original_updated" as "original_updated", 

"public"."deck"."confidential" as "confidential", 

"public"."deck"."source" as "source", 

"public"."deck"."certified" as "certified", 

"public"."card_deck"."id" as "cards_id", 

"public"."card"."foreign_id" as "cards_foreign_id", 

"public"."card_deck"."card_id" as "cards_card_id", 

"public"."deck"."id" as "cards_deck_id", 

"public"."card"."card_content" as "cards_card_content", 

"public"."card"."created" as "cards_created", 

"public"."card"."updated" as "cards_updated", 

"public"."card"."original_created" as "cards_original_created", 

"public"."card"."original_updated" as "cards_original_updated", 

"public"."card_deck"."position" as "cards_position",

(select count(*) from "public"."card_deck" where "public"."card_deck"."deck_id" = "public"."deck"."id") as "num_cards", 

dic.image_count as "num_images", 

(select "public"."deck_edition"."edition" from "public"."deck_edition" where "public"."deck_edition"."deck_id" = "public"."deck"."id") as "edition" 

from "public"."deck" left outer join dic on dic.deck_id = "public"."deck".id left outer join "public"."card_deck" on "public"."card_deck"."deck_id" = "public"."deck"."id" left outer join "public"."card" on "public"."card"."id" = "public"."card_deck"."card_id" 

where "public"."deck"."id" = '66bebe66-a0c4-45f0-8205-a80ef8f41f07' 

order by "public"."card_deck"."position" limit 150;

I have tried but could not proceed further at the point where I have the join conditions. Can you please help me.

This is what I have started with.


 UUID deckId = UUID.fromString("66bebe66-a0c4-45f0-8205-a80ef8f41f07");
int limit = 150;

CommonTableExpression<Record2<UUID, Long>> dec =
name("dec").fields("deck_id", "image_count").as(dsl.select(DECK_IMAGE_COUNT.DECK_ID, DECK_IMAGE_COUNT.IMAGE_COUNT).from(DECK_IMAGE_COUNT).where(DECK_IMAGE_COUNT.DECK_ID.eq(deckId)));


dsl.with(dec).select(JooqUtil.fieldsWithAliases(DECK_FIELDS, Deck.getAliases()))
.select(JooqUtil.fieldsWithPrefixedAliazes(CARD_DECK_FIELDS, Card.getAliases(), Deck.CARDS_FIELD))
.select(dsl.selectCount().from(CARD_DECK).where(CARD_DECK.DECK_ID.eq(DECK.ID)).asField(Deck.NUM_CARDS_FIELD))
.select(dsl.select(DECK_EDITION.EDITION).from(DECK_EDITION).where(DECK_EDITION.DECK_ID.eq(DECK.ID)).asField(Deck.VERSION_FIELD))
// How to convert [dic.image_count as "num_images",] from the above query in jooq
// How to convert "public"."deck" left outer join dic on dic.deck_id = "public"."deck".id left in jooq
.from(DECK).leftOuterJoin(dec).on(dec.field.eq(DECK.ID)) // This is what I was trying but could not make is happen
.leftOuterJoin(CARD_DECK).on(CARD_DECK.DECK_ID.eq(DECK.ID))
.leftOuterJoin(CARD).on(CARD.ID.eq(CARD_DECK.CARD_ID))
.where(DECK.ID.eq(deckId))
.orderBy(CARD_DECK.POSITION)
.limit(limit);

Thanks a lot in advance.

Thanks,

Deba

[Lukas Eder]

Hi Deba,

Why are you making this harder than it needs to be with a CTE? Specifically, because that CTE filters by the same deck_id value that you're using as a join predicate, so I don't see the point of that CTE. Just inline it.

Other than that, since you're not really doing anything in the CTE, you can re-use your generated code to dereference columns from it using  Table.field(Field):

// How to convert [dic.image_count as "num_images",] from the above query in jooq

dec.field(DECK_IMAGE_COUNT.IMAGE_COUNT).as("num_images")

 

// How to convert "public"."deck" left outer join dic on dic.deck_id = "public"."deck".id left in jooq

.from(DECK).leftOuterJoin(dec).on(dec.field.eq(DECK.ID)) // This is what I was trying but could not make is happen  

.from(DECK).leftOuterJoin(dec).on(dec.field(DECK_IMAGE_COUNT.DECK_ID).eq(DECK.ID))

Hope this helps,

Lukas

--
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/35fb5a78-f9f9-4a33-af0b-6b902037f10dn%40googlegroups.com.

[Debapriya Patra]

Hi Lukas,

There is a performance issue when I am trying to pull the image count and I am trying to optimize that query. While optimization, when I tried to put DECK_IMAGE_COUNT, it's not giving me any performance rather its adding more time to execute. So with CTE, I am getting better performance which is close to 10 times less than what I have now.

But after reading your reply, I tried to do little more change by adding new VIEW, I came up with another query but again I am stuck while trying to convert it to JOOQ. Can you please help me here converting ym query in JOOQ way ?

VIEWs USED : public.DECK, public.CARD_DECK, public.DECK_EDITION, public.DECK_IMAGE_CNT

DCK_IMAGE_CNT

--------------------
- deck_id
- image_count

My Query: 
-----------

with nc as (select deck_id as id, count(*) n from "public"."card_deck" where deck_id = '66bebe66-a0c4-45f0-8205-a80ef8f41f07' group by deck_id)

    select 
"public"."deck"."id" as "id",
            "public"."deck"."deck_id" as "deck_id",
            "public"."deck"."title" as "title",
            "public"."deck"."deck_type" as "deck_type",
            "public"."deck"."created" as "created",
            "public"."deck"."updated" as "updated",
            "public"."deck"."original_created" as "original_created",
            "public"."deck"."original_updated" as "original_updated",
            "public"."deck"."confidential" as "confidential",
            "public"."deck"."source" as "source",
            "public"."deck"."certified" as "certified",
            "public"."card_deck"."id" as "cards_id",
            "public"."card"."foreign_id" as "cards_foreign_id",
            "public"."card_deck"."card_id" as "cards_card_id",
            "public"."deck"."id" as "cards_deck_id",
            "public"."card"."card_content" as "cards_card_content",
            "public"."card"."created" as "cards_created",
            "public"."card"."updated" as "cards_updated",
            "public"."card"."original_created" as "cards_original_created",
            "public"."card"."original_updated" as "cards_original_updated",
            "public"."card_deck"."position" as "cards_position",

    nc.n as "num_cards",

    dic.image_count as "num_images",
            (select "public"."deck_edition"."edition" from "public"."deck_edition" where "public"."deck_edition"."deck_id" = "public"."deck"."id") as "edition"

    from "public"."deck"

    join nc on nc.id = "public"."deck"."id"
    join deck_image_count2 dic on dic.deck_id = "public"."deck".id

    left outer join "public"."card_deck" on "public"."card_deck"."deck_id" = "public"."deck"."id"
    left outer join "public"."card" on "public"."card"."id" = "public"."card_deck"."card_id"

    where "public"."deck"."id" = '66bebe66-a0c4-45f0-8205-a80ef8f41f07'
    order by "public"."card_deck"."position"

    limit 150;

My Jooq Conversion of above query:

----------------------------------------

CommonTableExpression<Record2<UUID, Integer>> cardCount =
        name("cardCount").fields("deck_id", "num_cards").as(dsl.select(CARD_DECK.DECK_ID,
                count()).from(CARD_DECK).where(CARD_DECK.DECK_ID.eq(deckId)).groupBy(CARD_DECK.DECK_ID));

SelectConditionStep<Record> selectConditionStep = dsl.with(cardCount).select(JooqUtil.fieldsWithAliases(DECK_FIELDS, Deck.getAliases()))

        .select(JooqUtil.fieldsWithPrefixedAliazes(CARD_DECK_FIELDS, Card.getAliases(), Deck.CARDS_FIELD))
        .select(dsl.selectCount().from(CARD_DECK).where(CARD_DECK.DECK_ID.eq(DECK.ID)).asField(Deck.NUM_CARDS_FIELD))
        .select(dsl.select(DECK_EDITION.EDITION).from(DECK_EDITION).where(DECK_EDITION.DECK_ID.eq(DECK.ID)).asField(Deck.VERSION_FIELD))

        .select(cardCount.field("num_cards").as(Deck.NUM_CARDS_FIELD))
        .select(DECK_IMAGE_CNT.IMAGE_COUNT)
        .from(DECK)
        .join(cardCount).on(cardCount.field(CARD_DECK.DECK_ID).eq(DECK.ID))
        .join(DECK_IMAGE_CNT).on(DECK_IMAGE_CNT.DECK_ID).eq(DECK.ID)
        .leftOuterJoin(CARD_DECK).on(CARD_DECK.DECK_ID.eq(DECK.ID))
        .leftOuterJoin(CARD).on(CARD.ID.eq(CARD_DECK.CARD_ID))
        .where(DECK.ID.eq(deckId)).and(CARD_DECK.POSITION.greaterThan(offset.floatValue())).orderBy(CARD_DECK.POSITION)

        .limit(limit)

        .fetchResultSet();

But I am not sure whether it's correct or wrong because I am getting following error when I build my code:

-----------------------------------------------------------------------------------

/Users/dpatra/Downloads/FTGitLab/DeckService/deck-service/src/main/java/com/chegg/deck/service/dao/DeckRepository.java:157: error: no suitable method found for on(TableField<DeckImageCntRecord,UUID>)
                .join(DECK_IMAGE_CNT).on(DECK_IMAGE_CNT.DECK_ID).eq(DECK.ID)
                                     ^
    method SelectOnStep.on(Condition) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to Condition)
    method SelectOnStep.on(Condition...) is not applicable
      (varargs mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to Condition)
    method SelectOnStep.on(Field<Boolean>) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to Field<Boolean>)
    method SelectOnStep.on(Boolean) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to Boolean)
    method SelectOnStep.on(SQL) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to SQL)
    method SelectOnStep.on(String) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to String)
    method SelectOnStep.on(String,Object...) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to String)
    method SelectOnStep.on(String,QueryPart...) is not applicable
      (argument mismatch; TableField<DeckImageCntRecord,UUID> cannot be converted to String)

  

Please help me convert this query in Jooq.

Cheers,

Debapriya Patra
650.933.6852

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

[Debapriya Patra]

Hi Lukas,

Please ignore my previous email because I did not put the right SQL query. I am basically trying to convert the below sql query in JOOQ:

    with nc as (select deck_id as id, count(*) n from "public"."card_deck" where deck_id = '66bebe66-a0c4-45f0-8205-a80ef8f41f07' group by deck_id)

    select 
"public"."deck"."id" as "id",
            "public"."deck"."deck_id" as "deck_id",
            "public"."deck"."title" as "title",
            "public"."deck"."deck_type" as "deck_type",
            "public"."deck"."created" as "created",
            "public"."deck"."updated" as "updated",
            "public"."deck"."original_created" as "original_created",
            "public"."deck"."original_updated" as "original_updated",
            "public"."deck"."confidential" as "confidential",
            "public"."deck"."source" as "source",
            "public"."deck"."certified" as "certified",
            "public"."card_deck"."id" as "cards_id",
            "public"."card"."foreign_id" as "cards_foreign_id",
            "public"."card_deck"."card_id" as "cards_card_id",
            "public"."deck"."id" as "cards_deck_id",
            "public"."card"."card_content" as "cards_card_content",
            "public"."card"."created" as "cards_created",
            "public"."card"."updated" as "cards_updated",
            "public"."card"."original_created" as "cards_original_created",
            "public"."card"."original_updated" as "cards_original_updated",
            "public"."card_deck"."position" as "cards_position",

    nc.n as "num_cards",

    dic.image_count as "num_images",
            (select "public"."deck_edition"."edition" from "public"."deck_edition" where "public"."deck_edition"."deck_id" = "public"."deck"."id") as "edition"

    from "public"."deck"

    join nc on nc.id = "public"."deck"."id"
    join deck_image_cnt dic on dic.deck_id = "public"."deck".id

    left outer join "public"."card_deck" on "public"."card_deck"."deck_id" = "public"."deck"."id"
    left outer join "public"."card" on "public"."card"."id" = "public"."card_deck"."card_id"

    where "public"."deck"."id" = '66bebe66-a0c4-45f0-8205-a80ef8f41f07'
    order by "public"."card_deck"."position"

    limit 150;

JOOQ Conversion Query:

CommonTableExpression<Record2<UUID, Integer>> cardCount =
        name("cardCount").fields("deck_id", "num_cards").as(dsl.select(CARD_DECK.DECK_ID,
                count()).from(CARD_DECK).where(CARD_DECK.DECK_ID.eq(deckId)).groupBy(CARD_DECK.DECK_ID));

SelectConditionStep<Record> selectConditionStep =
        dsl.with(cardCount)
        .select(JooqUtil.fieldsWithAliases(DECK_FIELDS, Deck.getAliases()))
        .select(JooqUtil.fieldsWithPrefixedAliazes(CARD_DECK_FIELDS, Card.getAliases(), Deck.CARDS_FIELD))

        .select(dsl.select(DECK_EDITION.EDITION).from(DECK_EDITION).where(DECK_EDITION.DECK_ID.eq(DECK.ID)).asField(Deck.VERSION_FIELD))
        .select(cardCount.field("num_cards").as(Deck.NUM_CARDS_FIELD))
        .select(DECK_IMAGE_CNT.IMAGE_COUNT).asField("num_images")

        .from(DECK)
        .join(cardCount).on(cardCount.field(CARD_DECK.DECK_ID).eq(DECK.ID))
        .join(DECK_IMAGE_CNT).on(DECK_IMAGE_CNT.DECK_ID).eq(DECK.ID)
        .leftOuterJoin(CARD_DECK).on(CARD_DECK.DECK_ID.eq(DECK.ID))
        .leftOuterJoin(CARD).on(CARD.ID.eq(CARD_DECK.CARD_ID))
        .where(DECK.ID.eq(deckId))
        .and(CARD_DECK.POSITION.greaterThan(offset.floatValue()))
        .orderBy(CARD_DECK.POSITION)
        .limit(limit)
        .fetchResultSet();

But I am getting the following error when compile my code:
/Users/dpatra/Downloads/FTGitLab/DeckService/deck-service/src/main/java/com/chegg/deck/service/dao/DeckRepository.java:157: error: incompatible types: Deck cannot be converted to Record
                .from(DECK)
                      ^

Please help me understand what is going wrong while I am converting to JOOQ.

Thanks a lot for helping :) 

Cheers,

Debapriya Patra
650.933.6852

On Fri, Dec 18, 2020 at 12:12 AM Lukas Eder <lukas...@gmail.com> wrote:

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

[Lukas Eder]

Looks like your parentheses aren't correct.

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