Skip to main content

Join Table Filter

Two Scenarios​

Jimmer supports join table filters, with two usage scenarios:

1. Multiple associations between same entity types​

@Entity
public interface Shop {

    @Id
    long id();

    String name();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        readonly = true
    )
    List<Customer> customers();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "VIP"
        )
    )
    List<Customer> vipCustomers();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "ORDINARY"
        )
    )
    List<Customer> ordinaryCustomers();
}

In this example, Shop.customers, Shop.vipCustomers, and Shop.ordinaryCustomers share the same join table

create table SHOP_CUSTOMER_MAPPING(
    SHOP_ID bigint not null,
    CUSTOMER_ID bigint not null,
    MAPPING_TYPE varchar(8) not null
);

// Whether the primary key of the join table contains 
// the filter column depends on the specific case.  
// In this example, a mapping between a `Shop` and `Customer`
// cannot be both "VIP" and "ORDINARY" at the same time.
// So the primary key does not contain `MAPPING_TYPE`.
alter table SHOP_CUSTOMER_MAPPING
    add constraint pk_SHOP_CUSTOMER_MAPPING
        primary key(SHOP_ID, CUSTOMER_ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__SHOP
        foreign key(SHOP_ID)
            references SHOP(ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__CUSTOMER
        foreign key(CUSTOMER_ID)
            references CUSTOMER(ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__MAPPING_TYPE
        check(MAPPING_TYPE in ('VIP', 'ORDINARY'));

The table stores data like below:

SHOP_IDCUSTOMER_IDMAPPING_TYPE
213176VIP
211087ORDINARY
219155ORDINARY
408347VIP
404365ORDINARY
403478ORDINARY

Where Shop.vipCustomers corresponds to two rows, Shop.ordinaryCustomers to four rows, and Shop.customers can see all rows:

Shop.customers = Shop.vipCustomers + Shop.ordinaryCustomers

We can see that Shop.customers is not a base association, but the union of other associations. Let's call it a union association.

caution

Union associations cannot be directly modified, so readonly = true must be declared.

Actually, the union association Shop.customers has an equivalent alternative syntax:

@ManyToMany
@JoinTable(
    ...other params omitted..., 
    filter = @JoinTable.JoinTableFilter(
        columnName = "MAPPING_TYPE",
        type = String.class, 
        values = {"VIP", "ORDINARY"}
    )
)
List<Customer> customers();

Finally, let's look at the difference in JOIN behavior between these three associations:

  • Shop.customers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient
        .create(table) 
        .where(
            table
                .asTableEx()
                .customers()
                .name().eq("Smith")
        )
        .select(table.id())
        .distinct()
        .execute();

    The generated SQL is:

    select distinct tb_1_.ID, tb_1_.NAME 
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_
        /* highlight-next-line */
        on tb_1_.ID = tb_2_.SHOP_ID 
    inner join CUSTOMER tb_3_
        on tb_2_.CUSTOMER_ID = tb_3_.ID 
    where tb_3_.NAME = ? /* Smith */

  • Shop.vipCustomers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient
        .create(table)
        .where(
            table
                .asTableEx() 
                .vipCustomers()
                .name().eq("Smith")
        )
        .select(table.id())
        .distinct() 
        .execute();

    The generated SQL is:

    select distinct tb_1_.ID, tb_1_.NAME
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_
        /* highlight-next-line */  
        on tb_1_.ID = tb_2_.SHOP_ID
        /* highlight-next-line */
        and tb_2_.MAPPING_TYPE = ? /* VIP */ 
    and
        tb_2_.type = ?
    inner join CUSTOMER tb_3_
        on tb_2_.CUSTOMER_ID = tb_3_.ID 
    where tb_3_.NAME = ? /* Smith */

  • Shop.ordinaryCustomers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient 
        .create(table)
        .where(
            table
                .asTableEx()
                .ordinaryCustomers()
                .name().eq("Smith")
        ) 
        .select(table.id())
        .distinct()
        .execute();

    The generated SQL is:

    select distinct tb_1_.ID, tb_1_.NAME
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_
        /* highlight-next-line */
        on tb_1_.ID = tb_2_.SHOP_ID 
        /* highlight-next-line */  
        and tb_2_.MAPPING_TYPE = ? /* ORDINARY */
    and
        tb_2_.type = ?
    inner join CUSTOMER tb_3_
        on tb_2_.CUSTOMER_ID = tb_3_.ID
    where tb_3_.NAME = ? /* Smith */

2. Multiple associations between different entity types​

A common case is when a project has many kinds of data, and each kind of data can be tagged with multiple Tags.

  • Tag

    @Entity
    public interface Tag {

        @Id  
        long id();

        String name();

        ...other properties omitted...
    }

  • BookStore

    @Entity
    public interface BookStore {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING", 
            joinColumns = @joinColumn(
                name = "SOURCE_ID",
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = @JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "BOOK_STORE", 
            )
        )
        List<Tag> tags();
        
        ...other properties omitted...
    }

  • Book

    @Entity
    public interface Book {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING",
            joinColumns = @joinColumn(
                name = "SOURCE_ID",  
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = @JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "BOOK",
            )
        ) 
        List<Tag> tags();
        
        ...other properties omitted...
    }

  • Author

    @Entity
    public interface Author {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING",
            joinColumns = @joinColumn(
                name = "SOURCE_ID",  
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = @JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "AUTHOR", 
            )
        )
        List<Tag> tags();
        
        ...other properties omitted...
    }

For the join table SOURCE_TAG_MAPPING, although the foreign key TAG_ID references the TAG table, the other foreign key SOURCE_ID does not have a fixed target - it could reference BOOK_STORE, BOOK or AUTHOR.

Therefore we cannot add a foreign key constraint on SOURCE_ID.

create table SOURCE_TAG_MAPPING(
    SOURCE_ID bigint not null,
    TAG_ID bigint not null,
    SOURCE_TYPE varchar(10) not null  
);

// Whether the primary key contains the filter column 
// depends on the specific case.
// In this example, `SOURCE_ID` of different types could be equal, 
// so the primary key constraint needs to contain `SOURCE_TYPE`.
alter table SOURCE_TAG_MAPPING
    add constraint pk_SHOP_CUSTOMER_MAPPING
        primary key(SOURCE_ID, TAG_ID, SOURCE_TYPE);  

// highlight-next-line
// `SOURCE_ID` could reference `BOOK_STORE`, `BOOK` or `AUTHOR`,
// highlight-next-line 
// so we cannot add a foreign key constraint

alter table SOURCE_TAG_MAPPING
    add constraint fk_SOURCE_TAG_MAPPING__TAG
        primary key(TAG_ID)
            references TAG(ID);

alter table SOURCE_TAG_MAPPING
    add constraint ck_SOURCE_TAG_MAPPING__SOURCE_TYPE
        check(SOURCE_TYPE in ('BOOK_STORE', 'BOOK', 'AUTHOR'));

Since SOURCE_ID cannot have a foreign key constraint, it is a fake foreign key. That's why in the code above:

@JoinTable(
    joinColumns = @joinColumn(
        name = "SOURCE_ID",
        foreignKeyType = ForeignKeyType.FAKE
    ), 
    ...other params omitted...
)

Sample data could look like:

SOURCE_IDTARGET_IDSOURCE_TYPE
92BOOK_STORE
94BOOK_STORE
863BOOK
864BOOK
7811AUTHOR
7813AUTHOR

Mixing One-to-Many and Many-to-Many​

Both one-to-many and many-to-many can use join tables, so we can merge their join tables into one table. But please note:

caution

It is strongly recommended to only use this pattern when the database supports conditional indexes.

That is, do not use this pattern on databases without conditional index support like MySQL.

Define Associations​

@Entity
public interface Book {

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID", 
        readonly = true
    )
    List<Author> authors();

    @Nullable
    @ManyToOne
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING", 
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "PRIMARY"
        )
    )  
    Author primaryAuthor();

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class, 
            values = "SECONDARY"
        )
    )
    List<Author> secondaryAuthors();

    ...other properties omitted...  
}

Clearly:

authors (many-to-many) = primaryAuthor (many-to-one) + secondaryAuthors (many-to-many)

Conditional Indexes​

create table BOOK_AUTHOR_MAPPING(
    BOOK_ID bigint not null,
    AUTHOR_ID bigint not null,
    MAPPING_TYPE varchar(9) not null  
);

// Whether the primary key contains the filter column depends on the specific case.
// In this example, a mapping between an `Author` and a `Book`  
// cannot be both "PRIMARY" and "SECONDARY" at the same time.
// So the primary key constraint does not contain `MAPPING_TYPE`.
alter table BOOK_AUTHOR_MAPPING
    add constraint pk_BOOK_AUTHOR_MAPPING
        primary key(BOOK_ID, AUTHOR_ID);  

alter table BOOK_AUTHOR_MAPPING
    add constraint fk_BOOK_AUTHOR_MAPPING__BOOK
        foreign key(BOOK_ID)
            references BOOK(ID);

alter table BOOK_AUTHOR_MAPPING
    add constraint fk_BOOK_AUTHOR_MAPPING__BOOK
        foreign key(AUTHOR_ID)
            references AUTHOR(ID); 

alter table BOOK_AUTHOR_MAPPING
    add constraint ck_BOOK_AUTHOR_MAPPING__MAPPING_TYPE
        check(MAPPING_TYPE in ('PRIMARY', 'SECONDARY'));  

// highlight-start
// Syntax for conditional indexes may differ slightly between databases,  
// here is an example in Postgres
create unique index BOOK_AUTHOR_MAPPING__PRIMARY_INDEX
    on BOOK_AUTHOR_MAPPING(AUTHOR_ID) 
    when MAPPING_TYPE = 'PRIMARY'; 
// highlight-end

The when clause is very important, indicating this is a conditional index.

Syntax for conditional indexes may differ slightly between databases, here is an example in Postgres. See https://www.postgresql.org/docs/current/indexes-partial.html for more details.

  • When MAPPING_TYPE is "PRIMARY", a unique index is added on the AUTHOR_ID foreign key to ensure each Book has maximum one primary Author.
    That is, to ensure Book.primaryAuthor is a many-to-one association.

  • Otherwise, there is no uniqueness constraint, so each Book can have any number of secondary Authors.
    That is, Book.secondaryAuthors remains a many-to-many association.