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.
