4.5 Implicit Subquery

Implicit subqueries can only be used for collection associations. This article discusses implicit subqueries through the many-to-many Book.authors.

Usage

Java

@Nullable String authorName = ...Omitted...;
@Nullable Gender authorGender = ...Omitted...;

BookTable table = BookTable.$;
List<Book> books = sqlClient
    .createQuery(table)
    .where(
        table.authors(author -> ❶
            Predicate.or(
                author.firstName().ilikeIf(authorName),
                author.lastName().ilikeIf(authorName)
            )
        ) 
    ) 
    .where(
        table.authors(author -> ❷
            author.gender().eqIf(authorGender)
        )
    ) 
    .select(table)
    .execute();

Kotlin

val authorName: String? = ...Omitted...
val authorGender: Gender? = ...Omitted...

val books = sqlClient
    .createQuery(Book::class) {
        where += table.authors { ❶
            or(
                firstName `ilike?` authorName,
                lastName `ilike?` authorName
            )
        }
        where += table.authors { ❷
            gender `eq?` authorGender
        }
        select(table)
    }
    .execute()

In fact, the two implicit subqueries in the code above can be merged into one, but in order to better demonstrate in the following text, they are deliberately written as two.

Various Situations

All subqueries are invalid

If both authorName and authorGender are null, it will cause the subqueries created at ❶ and ❷ to be invalid, and no actual SQL subqueries will be rendered.

At this time, the following SQL is generated:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_

Some subqueries take effect

If authorName is specified as non-null while authorGender remains null, the subquery at ❶ takes effect, while the subquery at ❷ is ignored.

At this time, the following SQL is generated:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_
where
    exists(
        select
            1
        from AUTHOR tb_2_
        inner join BOOK_AUTHOR_MAPPING tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
                tb_3_.BOOK_ID = tb_1_.ID
            and
                (
                    lower(tb_2_.FIRST_NAME) like ? /* %a% */
                or
                    lower(tb_2_.LAST_NAME) like ? /* %a% */
                )
    )

All subqueries take effect

If both authorName and authorGender are specified as non-null, the implicit subqueries created at ❶ and ❷ will both take effect.

tip

Jimmer can automatically merge conflicting implicit subqueries. The two implicit subqueries will be merged into one implicit subquery. Ultimately, only one SQL subquery will be rendered.

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_
where
    exists(
        select
            1
        from AUTHOR tb_2_
        inner join BOOK_AUTHOR_MAPPING tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
                tb_3_.BOOK_ID = tb_1_.ID
            and
                (
                    lower(tb_2_.FIRST_NAME) like ? /* %a% */
                or
                    lower(tb_2_.LAST_NAME) like ? /* %a% */
                )
            and
                tb_2_.GENDER = ? /* M */
    )