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.
