Implicit Subquery

Collection (one-to-many or many-to-many) associations often lead to the frequent use of subqueries in queries, and implicit subqueries greatly simplify such subqueries.

For complete normal subqueries, see Normal Subqueries.

DSL Code generated by Jimmer

Taking the many-to-many association Book.authors as an example, after compilation, Jimmer will generate the following code:

Java

BookTable.java

@GeneratedBy(type = Book.class)
public class BookTable extends AbstractTypedTable<Book> implements BookProps {

    @Override
    public Predicate authors(Function<AuthorTableEx, Predicate> block) {
        ...implementation logic omitted...  
    }
}

Kotlin

fun KProps<Book>.authors(
    block: KNonNullTableEx<Author>.() -> KNonNullExpression<Boolean>?  
): KNonNullExpression<Boolean>? = ...implementation logic omitted...

Users can build implicit subqueries based on this authors method.

The parameter of this method is a lambda expression whose parameter is the table object of the associated object. Users can return an SQL condition to filter the associated object.

Demo

Java

BookTable table = Tables.BOOK_TABLE;

public List<Book> findBooks(@Nullable String authorName) {
    return sqlClient
        .createQuery(table)
        .whereIf(
                authorName != null && !authorName.isEmpty(),
                table.authors(author -> {
                    return Predicate.or(
                            author.firstName().ilike(authorName),
                            author.lastName().ilike(authorName)
                    );
                })
        )
        .select(table)
        .execute();
}

Kotlin

fun findBooks(authorName: String?): List<BookStore> =
    sqlClient.createQuery(Book::class) {
        authorName?.takeIf { it.isNotEmpty() }?.let {
            where += table.authors {
                or(
                    firstName ilike it,
                    lastName ilike it
                )
            }
        }
    }.execute() 

When calling the above method and specifying a non-null parameter, for example findBooks("alex"), the generated SQL is:

select
    tb_1_.ID,
    tb_1_.CREATED_TIME,  
    tb_1_.MODIFIED_TIME,
    tb_1_.TENANT,
    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 ? /* %alex% */ ➌
                or
                    lower(tb_2_.LAST_NAME) like ? /* %alex% */ ➍
                )
    )  

• ➊ Implicit subqueries always use exists

• ➋ The SQL condition auto-generated by Jimmer for associating parent-child queries

• ➌ ➍ Conditions specified by users to filter associated objects

tip

The association condition between parent-child queries is auto-generated. Users only need to specify the filtering condition for the associated object. This is the fundamental difference between implicit subqueries and normal subqueries.

Automatic Merge

Similar to the automatic merge of dynamic JOINs, multiple implicit subqueries for the same association can also be automatically merged.

caution

The merge rules for implicit subqueries are not as universal as dynamic JOINs. They are limited to within the same and, or, or not.

For example:

Java

BookTable table = Tables.BOOK_TABLE;  

public List<Book> findBooks(
    @Nullable String authorName,
    @Nullable Gender authorGender  
) {
    return sqlClient
        .createQuery(table)
        .whereIf(
            authorName != null && !authorName.isEmpty(),
            table.authors(author -> {
                return Predicate.or(  
                        author.firstName().ilike(authorName),
                        author.lastName().ilike(authorName)
                );
            })  
        )  
        .whereIf(
            authorGender != null,
            table.authors(author -> author.gender().eq(authorGender))
        )  
        .select(table)
        .execute();  
} 

Kotlin

fun findBooks(authorName: String?): List<BookStore> =  
    sqlClient.createQuery(Book::class) {  
        authorName?.takeIf { it.isNotEmpty() }?.let {  
            where += table.authors {  
                or(  
                    firstName ilike it,  
                    lastName ilike it
                )  
            }  
        }
        authorGender?.let {  
            where += table.authors {
                gender like it  
            }
        }  
    }.execute()  

The above example uses two implicit subqueries.

However, when we specify both parameters as non-null, for example findBooks("alex", Gender.MALE), there will only be one subquery in the final SQL, as follows:

select
    tb_1_.ID, 
    tb_1_.CREATED_TIME,
    tb_1_.MODIFIED_TIME,
    tb_1_.TENANT,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID  
from BOOK tb_1_  
where
    // Merge two implicit subqueries to one real sub query
    // highlight-next-line  
    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 ? /* %alex% */
                or
                    lower(tb_2_.LAST_NAME) like ? /* %alex% */
                )
            and  
                tb_2_.GENDER = ? /* M */  
    )  

This is because within the same and, or or not, multiple implicit subqueries for the same association are automatically merged.