I have a question about using subqueries.

[LEEJaeHyeok97]

In the code below, I want to exclude deleted cafe information from the query.
I'm trying to use a subquery to filter out the deleted cafe information in my query, and I need some assistance with this.
Have a great day!

override fun findAllCafesById(lastCafeId: UUID?, limit: Int): CafePage {
        val query = jpql {
            select<Tuple>(entity(CafeEntity::class), entity(TagEntity::class))
                .from(
                    entity(CafeEntity::class),
                    leftFetchJoin(CafeTagEntity::class)
                        .on(entity(CafeEntity::class).eq(path(CafeTagEntity::cafe))),
                    leftFetchJoin(TagEntity::class)
                        .on(entity(TagEntity::class).eq(path(CafeTagEntity::tags))),
                )
                .whereAnd(
                    lastCafeId?.let {
                        path(CafeEntity::id).greaterThan(it)
                    },
                    path(CafeEntity::deletedAt).isNull()
                )
                .orderBy(path(CafeEntity::id).asc())
        }

        val resultList = entityManager
            .createQuery(query, jpqlRenderContext)
            .resultList

        val cafesWithTags = resultList.groupBy(
            { tuple -> tuple.get(0, CafeEntity::class.java) },
            { tuple -> tuple.get(1, TagEntity::class.java) }
        ).map { (cafeEntity, tagEntities) ->
            val cafe = cafeConverter.toDomain(cafeEntity)
            val tags = tagEntities.filterNotNull()
                .map { tagConverter.toDomain(it) }

            CafeInfoWithTags.of(cafe, tags)
        }.take(limit)


        val hasNext = resultList.size > limit
        return CafePage.from(SliceImpl(
            cafesWithTags,
            Pageable.unpaged(), hasNext
        ))
    }

[shouwn]

Hi @LEEJaeHyeok97. How can we help?

Do you want to know how to use subquery?

Without telling me how you want to write your query in JPQL or SQL, I'm afraid the only help I can give you is a link to the subquery documentation.

https://kotlin-jdsl.gitbook.io/docs/jpql-with-kotlin-jdsl/subqueries

[LEEJaeHyeok97]

@shouwn
Thank you for your response! I would like to understand how to use subqueries in a practical context. Specifically, I’m working with JPQL/SQL and would appreciate examples or guidance tailored to these languages. This would sufficiently address my question.

[shouwn]

I'm not sure I understand the requirements exactly, but here's how I would write it if I were you.

Firstly, I would split the query into 2 parts.

The first query is the one that extracts the ID of the target to be looked up.
The second query is the query to look up the CafeEntity.
The third query will be to look up the TagEntity.

The reason for doing this is to prevent LazyJoin causing unexpected performance issues if CafeEntity and TagEntity have a relationship such as OneToOne.

And I wouldn't use subqueries, because I think subqueries should really be a last resort.
I would rather have the application send multiple queries and combine the data in the application, even if it means a little bit more network cost, to reduce the DB load.

And I would add a limit when I call the DB query. If I had a fetch join, I wouldn't be able to add a limit because I would be doing the join in memory. But if we split the query, we can add a limit.

Here's how I reorganised your query. It won't compile.

    override fun findAllCafesById(lastCafeId: UUID?, limit: Int): CafePage {
        val cafeIdsQuery = jpql {
            select<UUID>(
                path(CafeEntity::id),
            ).from(
                entity(CafeEntity::class),
            ).whereAnd(
                path(CafeEntity::deletedAt).isNull(),
                lastCafeId?.let { path(CafeEntity::id).greaterThan(it) },
            ).orderBy(
                path(CafeEntity::id).asc()
            )
        }

        val cafeIds: List<UUID> = entityManager
            .createQuery(cafeIdsQuery, renderContext)
            .apply {
                maxResults = limit + 1
            }.resultList

        val cafes: Map<UUID, CafeEntity> = TODO("load cafes from cafeIds")
        val tags: Map<UUID, List<TagEntity>> = TODO("load tags from cafeIds")

        val cafesWithTags = cafeIds.map {
            val cafe = cafes[it]
            val tags = tags[it]
            
            CafeInfoWithTags.of(cafe, tags)
        }

        val hasNext = cafeIds.size > limit
        return CafePage.from(
            SliceImpl(
                cafesWithTags,
                Pageable.unpaged(), hasNext,
            ),
        )
    }