Associations

In last doc we introduced scalar property fetching. In this doc we discuss association fetching.

ORM has two types of association properties:

Reference association: Associated with a single object (or null), the property return type is an entity object, used to express one-to-one and many-to-one associations.

info
This article uses the many-to-one association property Book.store as an example.
Collection association: Associated with multiple objects, the property return type is a list of entities, used to express one-to-many and many-to-many associations.

info
This article uses the many-to-many association property Book.authors as an example.

Fetch associated objects with only id

When fetching associated objects without any parameters, only the id property of the associated objects is returned.

ManyToOne: Book.store

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
                .allScalarFields()
                .store()
        )
    )
    .execute();
System.out.println(toJson(list));

val books = sqlClient
.createQuery(Book::class) {
    where(table.edition.eq(3))
    select(
        table.fetchBy {
            allScalarFields()
            store()
        }
    )
}
.execute()

Here, store() indicates fetching the associated object. We did not specify any parameters for store(), which means only the id property of the associated object is fetched.

The generated SQL is:

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

Since the many-to-one association Book.store is based on a real foreign key, the foreign key STORE_ID of the current table BOOK is the id of the parent object.

Because store() only fetches the id property of the associated object, no additional SQL query is required. The parent object with only the id property can be directly constructed from the foreign key in the current data.

The returned value is:

[
    {
        "id": 3,
        "name": "Learning GraphQL",
        "edition": 3,
        "price": 51.00,
        "store": {
            "id": 1
        }
    },
    ...other objects omitted...
]

ManyToMany: Book.authors

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER.
                .allScalarFields()
                .authors()
        )
    )
    .execute();

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
                authors()
            }
        )
    }
    .execute()

Here, authors() indicates fetching the associated objects. We did not specify any parameters for authors(), which means only the id property of the associated objects is fetched.

Two SQLs are generated:

Query for the Book objects themselves

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

Based on the Book.authors association, query for the Author associated objects with only id for all root objects queried in the previous step

select 
    tb_1_.BOOK_ID, /* batch-map key */
    tb_1_.AUTHOR_ID /* batch-map value */
from BOOK_AUTHOR_MAPPING as tb_1_ 
    where tb_1_.BOOK_ID in (?, ?, ?, ?)

This example demonstrates:

The query only needs the id of the associated objects, and no filters are used (filters will be explained later).

Jimmer optimizes this case by only querying the BOOK_AUTHOR_MAPPING table instead of the AUTHOR table, because the middle table already contains the id of the associated objects.
where tb_1_.BOOK_ID in (?, ?, ?, ?) is a batch query, because the first query returns 4 root objects.

Jimmer uses batch queries to solve the N+1 problem, similar to DataLoader in GraphQL.

When a batch becomes too large, jimmer-sql will split it into smaller batches. This will be explained in the BatchSize section.
Jimmer queries the associated objects via separate SQL instead of using LEFT JOIN in the main query SQL to fetch associated objects.

This is designed to avoid duplicate results when joining collection associations, which can be devastating for paged queries on aggregate roots.

The printed result is (formatted for readability, original output is compact):

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,
        "authors":[
            {"id":1},
            {"id":2}
        ]
    },
    ...other objects omitted...
]

Fetch complex associated objects

When fetching associated objects, parameters can be specified to get associated objects with more information.

ManyToOne: Book.store

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
                .allScalarFields()
                .store(
                    Fetchers.BOOK_STORE_FETCHER
                        .allScalarFields()
                )
        )
    )
    .execute();
System.out.println(toJson(list));

val books = sqlClient
.createQuery(Book::class) {
    where(table.edition.eq(3))
    select(
        table.fetchBy {
            allScalarFields()
            store {
                allScalarFields()
            }
        }
    )
}
.execute()

Here, store(...) indicates fetching the associated object. We specify parameters for store(...) to fetch information other than id of the associated object.

Two SQLs are generated:

Query for Book objects

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

Based on the Book.store association, query for relatively complete BookStore associated objects for all root objects queried in the previous step
```
select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.WEBSITE
from BOOK_STORE tb_1_
where tb_1_.ID in (?, ?)
```

where tb_1_.ID in (?, ?) is a batch query. The first query returns 4 root objects, but the foreign key values are deduplicated to only 2 values.

The returned value is:

[
    {
        "id": 3,
        "name": "Learning GraphQL",
        "edition": 3,
        "price": 51.00,
        "store": {
            "id": 1,
            "name": "O'REILLY",
            "website": null
        }
    },
    ...other objects omitted...  
]

ManyToMany: Book.authors

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER.
                .allScalarFields()
                .authors(
                    Fetchers.AUTHOR_FETCHER
                        .allScalarFields()
                )
        )
    )
    .execute();

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
                authors {
                    allScalarFields()
                }
            }
        )
    }
    .execute()

Here, authors(...) indicates fetching the associated objects. We specify parameters for authors(...) to fetch information other than id of the associated objects.

Two SQLs are generated:

Query for the Book objects themselves

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

Based on the Book.authors association, query for relatively complete Author associated objects for all root objects queried in previous step

select
    /* batch-map key */
    tb_2_.BOOK_ID,

    /* batch-map value */
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME,
    tb_1_.GENDER

from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_
    on tb_1_.ID = tb_2_.AUTHOR_ID
where tb_2_.BOOK_ID in (?, ?, ?, ?)

This example shows:

Information other than id is required for the associated objects, so both the BOOK_AUTHOR_MAPPING table and the AUTHOR table are queried, besides just the middle table.
where tb_2_.BOOK_ID in (?, ?, ?, ?) is a batch query, because the first query returns 4 root objects.

Jimmer uses batch queries to solve the N+1 problem, similar to DataLoader in GraphQL.
Jimmer queries the associated objects via separate SQL instead of using LEFT JOIN in the main query SQL.

This is designed to avoid duplicate results when joining collection associations, which can be devastating for paged queries on aggregate roots.

The printed result is (formatted for readability):

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,
        "authors":[
            {
                "id": 1,
                "firstName": "Eve",
                "lastName": "Procello",
                "gender": "FEMALE"
            },
            {
                "id": 2,
                "firstName": "Alex",
                "lastName": "Banks",
                "gender": "MALE"
            }
        ]
    },
    ...other objects omitted...
]

Fetching all table fields

In some cases, all fields defined in the table are needed. allTableFields can be used for this.

allTableFields fetches all properties defined in the table, including all scalar properties (same as allScalarFields), one-to-one/many-to-one associations based on foreign keys. It does not include one-to-many, many-to-many associations, one-to-one/many-to-one associations based on middle tables, calculated properties or view properties.

info

allTableFields includes all one-to-one/many-to-one associations based on foreign keys, in addition to allScalarFields. The fetched associated objects only have the id property.

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
                .allTableFields()
        )
    )
    .execute();
System.out.println(toJson(list));

val bookAllTableFields = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allTableFields()
            }
        )
    }
    .execute()

The result is:

[
    {
        "id": 3,
        "name": "Learning GraphQL",
        "edition": 3,
        "price": 51.00,
        "store": {
            "id": 1
        }
    }
    ...other objects omitted
]

info

In some cases, select(table.fetch(Fetchers.XXX_FETCHER.allTableFields())) is equivalent to select(table). The latter can be considered as shorthand for the former.

The equivalence holds when associated objects are not affected by

The simple equivalent shorthand is:

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(book)  
    .execute();
System.out.println(toJson(list));

val bookAllScalarFields = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(table)
    }
    .execute()

Special Configurations for Associations

BatchSize

For one-to-many/many-to-many associations, a large number of associated objects may impact application performance. The batchSize configuration is provided for management.

The following example does not configure batchSize:

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(
        book.fetch(
                Fetchers.BOOK_FETCHER.allScalarFields()
                .authors(Fetchers.AUTHOR_FETCHER.allScalarFields())
        )
    )
    .execute();
System.out.println(toJson(list));

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allTableFields()
                authors {
                    allScalarFields()
                }
            }
        )
    }
.execute()

The generated SQL is:

# 1. Query book table to get all BOOK_ID
select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE
from BOOK tb_1_
where tb_1_.EDITION = ?

# 2. Use BOOK_ID from previous step to join query author table
select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME,
    tb_1_.GENDER
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_ on tb_1_.ID = tb_2_.AUTHOR_ID
where tb_2_.BOOK_ID in (?, ?, ?, ?)

In this example, no batchSize is configured so the default will be used. All BOOK_ID will be directly used in the in(...) list of the second query.

The output is:

[
    {
        "id": 3,
        "name": "Learning GraphQL",
        "edition": 3,
        "price": 51.00,
        "authors": [
            {
                "id": 1,
                "firstName": "Eve",
                "lastName": "Procello",
                "gender": "FEMALE"
            },
            {
                "id": 2,
                "firstName": "Alex",
                "lastName": "Banks",
                "gender": "MALE"
            }
        ]
    },
    ...omit other objects
]

In the example above, we see the query:

select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME,
    tb_1_.GENDER
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_ on tb_1_.ID = tb_2_.AUTHOR_ID  
where tb_2_.BOOK_ID in (?, ?, ?, ?)

Here, the in expression implements batch query to solve N+1 problem.

If a batch is too large, it will be split into multiple batches based on batchSize configuration, such as:

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;
List<Book> list = sqlClient.createQuery(book)
    .where(book.edition().eq(3))
    .select(
        book.fetch(
                Fetchers.BOOK_FETCHER.allScalarFields()
                .authors(Fetchers.AUTHOR_FETCHER.allScalarFields(), it -> it.batch(2))
        )
    )
    .execute();

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
                authors({
                    batch(2)
                }) {}
            }
        )
    }
    .execute()

danger

Here the batchSize for authors is set to 2. Such a small value would cause poor performance, it is only for demo purposes. Please do not use such small values in real projects.

This would cause the in(?, ?, ?, ?) to be split into two in(?, ?), and the SQL to fetch associated objects would be split into two.

select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME, 
    tb_1_.GENDER
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_ on tb_1_.ID = tb_2_.AUTHOR_ID
where tb_2_.BOOK_ID in (?, ?) 

select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME,
    tb_1_.GENDER
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_ on tb_1_.ID = tb_2_.AUTHOR_ID
where tb_2_.BOOK_ID in (?, ?)

In real projects, in most cases batchSize would not be configured like this at fetcher level. Instead, use the global configurations in SqlClient:

JSqlClient.getDefaultBatchSize(): Default batchSize for one-to-one and many-to-one associations, default 128
JSqlClient.getDefaultListBatchSize(): Default batchSize for one-to-many and many-to-many associations, default 16

When creating SqlClient, the global configs can be changed:

Using spring boot

Add configuration into application.yml or application.properties
```
jimmer:
    default-batch-size: 256
    default-list-batch-size: 32
```

Using low-level API

Java
Kotlin

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setDefaultBatchSize(256) 
    .setDefaultListBatchSize(32)
    ....
    build();

val sqlClient = newKSqlClient {
    setDefaultBatchSize(256)
    setDefaultListBatchSize(32)
    ....
    }

caution

No matter association-level batchSize or global batchSize, do not set above 1000 because Oracle database allows max 1000 values in in(...).

Pagination at Association Level

For collection associations, limit(limit, offset) can be specified during fetching to do pagination at association level.

caution

Association-level pagination and batch loading cannot coexist. Association-level pagination necessarily causes N+1 problem. Please use this feature cautiously!

If association-level pagination is used, batchSize must be set to 1, otherwise exceptions would be thrown. This is designed to make it clear to developers and readers that the code has N+1 performance risk.

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
            .allScalarFields()
            .authors(
                Fetchers.AUTHOR_FETCHER.allScalarFields(),
                it -> it.batch(1).limit(/*limit*/ 10, /*offset*/ 90)
            )
        )
    )
    .execute();

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
                authors({
                    batch(1)
                    limit(limit = 90, offset = 10)
                    }) {
                    allScalarFields()
                }
            }
        )
    }
    .execute()

Since association pagination cannot solve N+1 problem, multiple SQLs are generated
For simplicity let's assume H2Dialect is used so different databases have the same paging SQL

Query current Book objects

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

Paginated query on authors collection of the 1st Book object

select
    tb_1_.AUTHOR_ID,
    tb_3_.FIRST_NAME,
    tb_3_.LAST_NAME, 
    tb_3_.GENDER
from BOOK_AUTHOR_MAPPING as tb_1_  
inner join AUTHOR as tb_3_ on tb_1_.AUTHOR_ID = tb_3_.ID
where tb_1_.BOOK_ID = ?
/* highlight-next-line */
limit ? offset ?  

Paginated query on authors collection of the 2nd Book object

Same as above, omitted.
Paginated query on authors collection of the 3rd Book object

Same as above, omitted.
Paginated query on authors collection of the 4th Book object

Same as above, omitted.

Property-level Filters

When fetching associations, filters can be specified to filter associated objects.

Here for comparison, the query selects two columns, both are Book type.

The Book.authors of the first column uses filter
The Book.authors of the second column does not use filter

Java
Kotlin

BookTable book = Tables.BOOK_TABLE;

List<Tuple2<Book, Book>> books = sqlClient
    .createQuery(book)
    .select(
        // First column  
        book.fetch(
            Fetchers.BOOK_FETCHER
            .allScalarFields()
            .authors(
                Fetchers.AUTHOR_FETCHER.allScalarFields(),

                // Use filter
                it -> it.filter(args -> {
                    args.where(args.getTable().firstName().ilike("a"));
                })
            )
        ),

        // Second column
        book.fetch(
            Fetchers.BOOK_FETCHER
                .allScalarFields()
                .authors(
                    Fetchers.AUTHOR_FETCHER.allScalarFields()
                // No filter used
                )
        )
    )
    .execute();

val tuples: List<Tuple2<Book, Book>> = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(

            // First column
            table.fetchBy {
                allScalarFields()
                authors({
                    // Use filter
                    filter {
                    where(table.firstName ilike "a")
                    }
                }) {
                    allScalarFields()
                }
            },

            // Second column
            table.fetchBy {
                allScalarFields()
                authors { // No filter used  
                    allScalarFields()
                }
            }
        )
    }
.execute()

Three SQLs are generated:

Query the two Book objects needed for the tuple

select

/* For tuple._1 */  
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,

/* For tuple._2 */
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION, 
    tb_1_.PRICE

from BOOK as tb_1_ 
where tb_1_.EDITION = ?

For the authors association of the 4 Book objects in column 1, use filter

select

    tb_1_.BOOK_ID,
    tb_1_.AUTHOR_ID,
    tb_3_.FIRST_NAME,
    tb_3_.LAST_NAME,
    tb_3_.GENDER
from BOOK_AUTHOR_MAPPING as tb_1_
inner join AUTHOR as tb_3_ on tb_1_.AUTHOR_ID = tb_3_.ID 
where
    tb_1_.BOOK_ID in (?, ?, ?, ?)  
and
    /* Use filter here */
    /* highlight-next-line */
    lower(tb_3_.FIRST_NAME) like ?

For the authors association of the 4 Book objects in column 2, no filter

select

    tb_1_.BOOK_ID,
    tb_1_.AUTHOR_ID,
    tb_3_.FIRST_NAME,
    tb_3_.LAST_NAME,
    tb_3_.GENDER
from BOOK_AUTHOR_MAPPING as tb_1_
inner join AUTHOR as tb_3_ on tb_1_.AUTHOR_ID = tb_3_.ID
where
    tb_1_.BOOK_ID in (?, ?, ?, ?)
/* No filter here */ 

The printed result is (formatted for readability):

Tuple2{
    _1={
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,

        // Filter used, incomplete collection
        "authors":[
            {
                "id":2,
                "firstName":"Alex",
                "lastName":"Banks",
                "gender":"MALE" 
            }
        ]
    },
    _2={
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,

        // No filter, complete collection 
        "authors":[
            {
                "id":2,
                "firstName":"Alex",
                "lastName":"Banks",
                "gender":"MALE"
            },{
                "id":"fd6bb6cf-336d-416c-8005-1ae11a6694b5",
                "firstName":"Eve",
                "lastName":"Procello",
                "gender":"MALE"
            }
        ]
    }
}

note

Filters can not only filter associated objects, but also sort them. The principle is similar so not demonstrated here.

caution

For associations satisfying both below conditions, applying filters would cause exceptions:
- Many-to-one
- Not null
After field filters are used, association cache for that field will be ignored.

To use the association cache, global filters supporting multi-view cache can be used.
A common mistake in real development (Java for example) is:

filter(it -> args.getTable().firstName().ilike("a"))

This creates the condition expression but does not call args.where. Filter code not calling either args.where or args.orderBy is meaningless.

The correct code is:

filter(it -> args.where(args.getTable().firstName().ilike("a")))

Fetch associated objects with only id​

ManyToOne: Book.store​

ManyToMany: Book.authors​

Fetch complex associated objects​

ManyToOne: Book.store​

ManyToMany: Book.authors​

Fetching all table fields​

Special Configurations for Associations​

BatchSize​

Pagination at Association Level​

Property-level Filters​

Fetch associated objects with only id

ManyToOne: Book.store

ManyToMany: Book.authors

Fetch complex associated objects

ManyToOne: Book.store

ManyToMany: Book.authors

Fetching all table fields

Special Configurations for Associations

BatchSize

Pagination at Association Level

Property-level Filters