Merge Conflict Joins

Usage

, we listed two scenarios. This article discusses how to resolve the problems in these two scenarios.

note

There are two ways to solve this problem. This article demonstrates the first solution.

Solve the problem in scenario 1:

Java
Kotlin

List<Book> findBooks(
        @Nullable String name,
        @Nullable String storeName,
        @Nullable String storeWebsite
) {
    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .whereIf(
            name != null,
            () -> book.name().like(name)
        )
        .whereIf(
            storeName != null, 
            () -> table
                .store() ❶
                .name()
                .like(storeName)
        )
        .whereIf(
            storeWebsite != null,
            () -> table
                .store() ❷
                .website()
                .like(storeWebsite)
        )
        .select(book)
        .execute();
}

fun findBooks(
    name: String? = null,
    storeName: String? = null, 
    storeWebsite: String? = null
): List<Book> = 
    sqlClient
        .createQuery(Book::class) {
            name?.let {
                where(table.name like it) 
            }
            storeName?.let {
                where(
                    table
                        .store ❶
                        .name like it
                )
            }
            storeWebsite?.let {
                where(
                    table
                        .store ❷
                        .website like it
                )
            }
            select(table)
        }
        .execute()

This is a typical dynamic query where all three query parameters are nullable.

Specify name but storeName and storeWebsite are still null.

In this case, the code at ❶ and ❷ will not execute. The final generated SQL does not contain any joins.

select 
    tb_1_.ID, 
    tb_1_.NAME,  
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_ 
    where tb_1_.NAME = ?

Specify name and storeName, but storeWebsite is still null.

In this case, the join at ❶ takes effect but the code at ❷ does not execute. The final generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_
/* highlight-start */ 
inner join BOOK_STORE as tb_2_
    on tb_1_.STORE_ID = tb_2_.ID 
/* highlight-end */
where
    tb_1_.NAME = ?
and 
    tb_2_.NAME = ? 

Specify all parameters, name, storeName and storeWebsite are all non-null.

In this case, both joins at ❶ and ❷ take effect. This situation is called join conflict.

tip

This conflict does not cause any problems. Conflicting joins are merged into one join.

So in the final generated SQL, there will be no duplicate join operations.

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION, 
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_
    /* highlight-start */
    inner join BOOK_STORE as tb_2_ on
        tb_1_.STORE_ID = tb_2_.ID
    /* highlight-end */
where
    tb_1_.NAME = ?
and
    tb_2_.NAME = ?
and
    tb_2_.WEBSITE = ? 

Solve the problem in scenario 2:

Java
Kotlin

List<Long> findDistinctIds(
    @Nullable Long aId,
    @Nullable Long bId,
    @Nullable Long cId,
    @Nullable Long dId,
    @Nullable Long eId
) {
    ATable table = Tables.A_TABLE;

    return sqlClient
        .createQuery(table)
        .whereIf(
            aId != null,
            () -> table.id().like(aId)
        )
        .whereIf(
            bId != null,
            () -> table.asTableEx().bs().id().like(bId)
        )
        .whereIf(
            cId != null,
            () -> table.asTableEx().bs().cs().id().like(cId)
        )
        .whereIf(
            dId != null,
            () -> table.asTableEx().bs().cs().ds().id().like(dId)
        )
        .whereIf(
            eId != null,
            () -> table.asTableEx().bs().cs().ds().es().id().like(eId)  
        )
        .select(book.id())
        .distinct()
        .execute();
}

fun findDistinctIds(
    aId: Long? = null,
    bId: Long? = null,
    cId: Long? = null,
    dId: Long? = null,
    eId: Long? = null
): List<Long> =
    sqlClient
        .createQuery(A::class) {
            aId?.let {
                where(table.id eq it)
            }
            bId?.let {
                where(table.asTableEx().bs.id eq it)
            }
            cId?.let {
                where(table.asTableEx().bs.cs.id eq it)
            }
            dId?.let {
                where(table.asTableEx().bs.cs.ds.id eq it)
            }
            eId?.let {
                where(table.asTableEx().bs.cs.ds.es.id eq it)
            }
            select(table.id)
        }
        .distinct()
        .execute()

info

Given the basics above, no need to list the generated SQL for different parameter combinations here. Just understand that the final SQL will not contain duplicate joins.
asTableEx here is a concept to be introduced in later docs Pagination Safety. Please ignore it for now.

Merge Rules

Assume there are three join paths:

a -> b -> c -> d -> e -> f -> g
a -> b -> c -> h -> i -> j
a -> x -> y -> z -> a -> b -> c -> d

To eliminate conflicts, Jimmer first merges these paths into a tree:

-+-a
 |
 +----+-b
 |    |
 |    \----+-c
 |         | 
 |         +----+-d
 |         |    |
 |         |    \----+-e
 |         |         |
 |         |         \----+-f
 |         |              |
 |         |              \------g
 |         | 
 |         \----+-h
 |              |
 |              \----+-i
 |                   |
 |                   \------j
 |
 \----+-x
      |
      \----+-y
           |
           \----+-z
                |
                \----+-a
                     |
                     \----+-b
                          |
                          \----+-c
                               |
                               \------d

Then generate the join clause in the final SQL based on this tree.

Another rule to note is join type. The method to create join objects takes a parameter to specify join type, e.g. left join:

Java
Kotlin

book.store(JoinType.LEFT) 

book.`store?`

The merge rules for join types:

If join types of all conflicting join nodes are the same, the merge preserves that join type.
Otherwise, the merge result is always inner join.

Usage​

Merge Rules​

Usage

Merge Rules