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 <[email protected]> wrote:
> 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 <http://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
>
>
> On Fri, Dec 18, 2020 at 1:36 AM Debapriya Patra <[email protected]>
> wrote:
>
>> 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
>>
>> --
>> 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 [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/35fb5a78-f9f9-4a33-af0b-6b902037f10dn%40googlegroups.com
>> <https://groups.google.com/d/msgid/jooq-user/35fb5a78-f9f9-4a33-af0b-6b902037f10dn%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/CAB4ELO6LuSFR9QVSpuTgHgT9RNjLTUxOyO7yjR7dE5CMbxgedQ%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6LuSFR9QVSpuTgHgT9RNjLTUxOyO7yjR7dE5CMbxgedQ%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAM2NkrR9gaD3UkzUhhXQBjNO1U9x6%2Bep2SH0KrMUSQ7LPSmGXg%40mail.gmail.com.
