0%
August 12, 2024

Useful Query in JOOQ

jooq

kotlin

Contents

Subqueries as JSON field in JOOQ

What do we have in Kysely?

In Kysely we have scripts like the following to mimic a json_agg like statement (a simple reference for plain SQL):

  • To do a subquery of single object and embed it into a key called voice: 
    .select(eb => [
    jsonObjectFrom(eb.selectFrom("Voice")
        .select(["Voice.transcription", "Voice.frontendStartTime", "Voice.frontendEndTime", "Voice.jsonUrl"])
        .whereRef("Voice.messageId", "=", "Message.id")
    ).as("voice")
])
  • To do a subquery of a list of objects and embed it into a key called images 
    .select(eb => [
    jsonArrayFrom(eb.selectFrom("Image")
        .select([
            "Image.id",
            "Image.url"
        ])
        .leftJoin("MessagesSession", "MessagesSession.id", "Image.messagesSessionId")
        .whereRef("Image.messagesSessionId", "=", "LLMSummary.messagesSessionId")
        .where("Image.isDeleted", "!=", true)
    ).as("images")
])

in the next section we demonstrate the analogs in JOOQ.

Subquery a list of objects in JOOQ

Let db be an injected DSLContext object.

db
    .select(
        TEACHER.NAME.`as`("teacherName"),
        multiset(
            select(
                COURSE.NAME.`as`("courseName"),
                COURSE.CATEGORY.`as`("courseCategory")
            )
                .from(COURSE)
                .where(COURSE.TEACHERID.eq(TEACHER.ID))
        ).`as`("courses").convertFrom { it.into(CourseDetail::class.java) }
    )
    .from(TEACHER)
    .where(TEACHER.ID.eq(teacherId))
    .fetchOneInto(TeacherDetail::class.java)

Subquery an object in JOOQ

We keep using multiset, with an distinction that we convert the list into an object by firstOrNull():

1db.select(
2    QUOTA_SEAT.asterisk(),
3    multiset(
4        select(counterTable.asterisk())
5            .from(counterTable)
6            .where(
7                counterTable.SEATID.eq(seatTable.ID)
8                    .and(counterTable.ACTIVE.eq(true))
9            )
10    ).`as`("activeCounters").convertFrom { it.into(QuotaUsagecounter::class.java) },
11    multiset(select(QUOTA_PERSONALSEAT.asterisk())
12                    .from(QUOTA_PERSONALSEAT)
13                    .where(QUOTA_PERSONALSEAT.SEATID.eq(seatTable.ID))
14    ).`as`("personalSeatData").convertFrom { result -> result.map { 
15        it.into(QuotaPersonalseat::class.java) }.firstOrNull()
16    },
17    multiset(select(QUOTA_TEAMSEAT.asterisk())
18                    .from(QUOTA_TEAMSEAT)
19                    .where(QUOTA_TEAMSEAT.SEATID.eq(seatTable.ID))
20    ).`as`("teamSeatData").convertFrom { result -> result.map { 
21        it.into(QuotaTeamseat::class.java) }.firstOrNull() 
22    }
23)
24    .from(seatTable)
25    .where(...)

Sample Result:

Batch Insert

Let QUOTA be an exported value in db.tables.references.* (in jooq sense) and db a DSLContext object:

val record = db.newRecord(QUOTA).apply {
    this.audiolimit = 10
    this.owneremail = "machingclee@gmail.com"
}

db.batchInsert(List(100) { record }).execute()

Conditionally Ignored SQL Statement

import org.jooq.impl.DSL.*

db.select(
    seatTable.asterisk(),
    multiset(
        select(counterTable.asterisk())
            .from(counterTable)
            .where(
                counterTable.SEATID.eq(seatTable.ID)
                    .and(counterTable.ACTIVE.eq(true))
            )
    ).`as`("activeCounters").convertFrom { it.into(QuotaUsagecounter::class.java) }
)
    .from(seatTable)
    .where(
        seatTable.TYPE.eq(seattype)
            .and(seatTable.ACTIVE.eq(true))
            .and(seatTable.OWNEREMAIL.eq(planOwnerEmail))
            .and(if (targetEmail != null) seatTable.ASSIGNTARGETEMAIL.eq(targetEmail) else trueCondition())
            .and(seatTable.ISTRIAL.eq(isTrial))
    )
    .fetch()
    .into(SeatWithCounters::class.java)

Where Clause with a Column Inside a Tuple

.and(QUOTA_SEAT.TYPE.`in`(listOf(QuotaSeattype.PERSONAL_POWERFUL_BILLIE,
                                 QuotaSeattype.PERSONAL_HANDY_BILLIE)))