Weak Join

The Problem to Solve

All the joins we have discussed so far rely on the association properties between entities, which means table joins are always related to primary and foreign keys.

However, sometimes we need to join two tables based on some business properties unrelated to primary and foreign keys. Although not frequently used, such relatively free table joins can be very useful when needed.

For this scenario, you have two options:

• Use @JoinSql to declare a ManyToMany association unrelated to primary and foreign keys, then use the association property to perform the join

  info

  This approach is suitable when the corresponding join operation is needed by multiple business scenarios and has high reusability.

• Directly use the weak join explained in this article, without declaring any association properties between entities

  info

  This approach is suitable when the corresponding join operation is only needed by some individual business scenarios and you don't want to define a @JoinSql based association property in entities just for this purpose.

Usage

Define Join Condition

To use weak join:

• For Java, first implement the WeakJoin interface

• For Kotlin, implement the KWeakJoin abstract class

Java

WeakJoin.java

package org.babyfish.jimmer.sql.ast.table;

import org.babyfish.jimmer.sql.ast.Predicate;

public interface WeakJoin<ST extends Table<?>, TT extends Table<?>> {

    Predicate on(ST source, TT target);
}

Kotlin

KWeakJoin.kt

package org.babyfish.jimmer.sql.kt.ast.table

abstract class KWeakJoin<S: Any, T: Any> /* omit super types */ {

    abstract fun on(
        source: KNonNullTable<S>,
        target: KNonNullTable<T>
    ): KNonNullExpression<Boolean>

    ...other code omitted...
}

• For Java, the generic type parameters of the WeakJoin interface should be the Table classes generated by the preprocessor for the current entity and target entity, e.g.

  WeakJoin<BookTable, AuthorTable>

• For Kotlin, the generic type parameters of the KWeakJoin abstract class should be the current entity and target entity, e.g.

  KWeakJoin<Book, Author>

Developers need to customize a class to implement the custom table join condition:

Java

private static class BookAuthorJoin implements WeakJoin<BookTable, AuthorTable> {

    @Override
    public Predicate on(BookTable source, AuthorTable target) {
        return Predicate.and(
            source.businessProp1().eq(target.businessPropA()),
            source.businessProp2().eq(target.businessPropB())
        );
    }
}

Kotlin

private class BookAuthorJoin : KWeakJoin<Book, Author> {

    override fun on(
        source: KNonNullTable<Book>,
        target: KNonNullTable<Author>
    ): KNonNullExpression<Boolean> =
        and(
            source.businessProp1 eq target.businessPropA,
            source.businessProp2 eq target.businessPropB
        )
}

caution

• WeakJoin/WeakJoin is an interface, but implementation must be a class rather than a lambda expression.

• Do not use anonymous classes for implementation. Although anonymous classes can work, they violate the design intent here.

  If the class has no reuse value at all, it is recommended to define it as a private static nested class.

• The class itself does not have generic type parameters, but the generic parameters of the super interface/class must be explicitly specified.

The reasons for the first two points will be explained later.

Developers can even use NativeSQL expressions to build more arbitrary join conditions, e.g. (assuming MySQL is used here, calling the SQL function substr):

Java

private static class BookAuthorJoin implements WeakJoin<BookTable, AuthorTable> {

    @Override
    public Predicate on(BookTable source, AuthorTable target) {
        return Predicate.sql(
            "substr(%e, 1, 5) = substr(%e, 1, 5)",
            new Expression[] {
                source.code(),
                target.code()
            }
        );
    }
}

Kotlin

private class BookAuthorJoin : KWeakJoin<Book, Author> {

    override fun on(
        source: KNonNullTable<Book>,
        target: KNonNullTable<Author>
    ): KNonNullExpression<Boolean> =
        sql(Boolean::class, "substr(%e, 1, 5) = substr(%e, 1, 5)") {
            expression(source.code)
            expression(target.code)
        }
}

info

Here, Predicate.sql in Java code and sql in Kotlin code are methods to mix native SQL snippets into the strongly typed Jimmer SQL DSL.

We have never introduced these before, please refer to NativeSQL expressions.

Use the Join Condition

Java

BookTable table = Tables.BOOK_TABLE;

List<Long> bookIds = sqlClient
    .createQuery(table)
    .where(
        table
            .asTableEx() ❶
            .weakJoin(BookAuthorJoin.class) ❷
            .firstName().eq("Alex")
    )
    .select(table.id())
    .distinct()
    .execute(); 

Kotlin

val bookIds = sqlClient
    .createQuery(Book::class) {
        where(
            table
                .asTableEx() ❶
                .weakJoin(BookAuthorJoin::class) ❷
                .firstName eq "Alex"
        )
        select(table.id)
    }
    .distinct()
    .execute();

• ❶ Converts Table to TableEx using asTableEx

  caution

  The subsequent weakJoin is only supported by TableEx, not by Table.

• ❷ Calls weakJoin with the BookAuthorJoin class defined before as the join condition, joining to the author table

  • Here, the return type of weakJoin is AuthorTable/KNonNullTable<Author>

  • The following methods can be used to support outer join:

    • Java: weakJoin(BookAuthorJoin.class, JoinType.LEFT)

    • Kotlin: weakOuterJoin(BookAuthorJoin::class)

info

We have introduced a feature called Merge Conflict Join before. It is important that weakJoin is compatible with it.

For the same join source, if weakJoin is called multiple times with the same WeakJoin class (the BookAuthorJoin class here), they can be merged into one join operation to avoid duplicate joins in the final SQL.

This is why the BookAuthorJoin class cannot be implemented using lambda or anonymous classes, as mentioned earlier.