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
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/CAM2NkrRnoyEQWFgqziejBv2v-qY%2Bj8-mUGp8afZo2RhnShUp0w%40mail.gmail.com.
