2.4 Associated Id

Unlike the previous example, here we only need to fetch the associated object id, not the entire associated object.

Query Dynamic Entities

If there is no IdView property

Java

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("Learning GraphQL"))
    .orderBy(table.edition().desc())
    .select(
        table.fetch(
            BookFetcher.$
                .allScalarFields()
                .authors()
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.name  eq "Learning GraphQL")
        orderBy(table.edition.desc())
        select(
            table.fetchBy {
                allScalarFields()
                authors()
            }
        )
    }
    .execute()

The result is

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51,
        "authors":[
            { "id":2 },
            { "id":1 }
        ]
    },
    {
        "id":2,
        "name":"Learning GraphQL",
        "edition":2,
        "price":55,
        "authors":[
            { "id":2 },
            { "id":1 }
        ]
    },
    {
        "id":1,
        "name":"Learning GraphQL",
        "edition":1,
        "price":50,
        "authors":[
            { "id":2 }, 
            { "id":1 }
        ]
    }
]

This will lead to large numbers of objects that have only the id property (JPA users can understand them as entity proxies), although correct, this may not be the format preferred by developers. In the next section we discuss how to solve this problem.

If there is IdView property

In order to solve the above problem, we can add the @IdView property for entity interface, namely the associated id property.

Java

@Entity
public interface Book {

    @ManyToMany
    List<Author> authors();

    @IdView("authors")
    List<Long> authorIds();

    ....省略其他属性...
}

Kotlin

@Entity
interface Book {

    @ManyToMany
    val authors: List<Author>

    @IdView("authors")
    val authorIds: List<Long>

    ....省略其他属性...
}

Here, the authorIds property is a view of the authors property, and they share data. So, it does not change the entity format, it just diversifies the expression.

info

This is just a case study, no in-depth discussion of the @IdView property. Please refer here for more details

Java

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("Learning GraphQL"))
    .orderBy(table.edition().desc())
    .select(
        table.fetch(
            BookFetcher.$
                .allScalarFields()
                .authorIds()
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.name  eq "Learning GraphQL")
        orderBy(table.edition.desc())
        select(
            table.fetchBy {
                allScalarFields()
                authorIds()
            }
        )
    }
    .execute()

The result is

[
    {
        "id":1,
        "name":"Learning GraphQL",
        "edition":1,
        "price":50,
        "authorIds":[2, 1]
    },
    {
        "id":2,
        "name":"Learning GraphQL",
        "edition":2,
        "price":55,
        "authorIds":[2, 1]
    },
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51,
        "authorIds":[2, 1]
    }
]

Query Static DTO

No need to define @IdView attributes in entities, create any file with the dto extension under the src/main/dto folder, and edit the code as follows:

export com.yourcompany.yourproject.model.Book
    -> package com.yourcompany.yourproject.model.dto

BookView {
    #allScalars
    id(authors) as authorIds
}

Compile the project, a java/kotlin class named BookView will be automatically generated.

Java

BookTable table = BookTable.$;

List<BookView> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("Learning GraphQL"))
    .orderBy(table.edition().desc())
    .select(
        table.fetch(BookView.class)
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.name  eq "Learning GraphQL")
        orderBy(table.edition.desc())
        select(
            table.fetch(BookView::class)
        )
    }
    .execute()

The result is

[
    BookView(
        id=3, 
        name=Learning GraphQL, 
        edition=3, 
        price=51.00, 
        authorIds=[2, 1]
    ), 
    BookView(
        id=2, 
        name=Learning GraphQL, 
        edition=2, 
        price=55.00, 
        authorIds=[2, 1]
    ), 
    BookView(
        id=1, 
        name=Learning GraphQL, 
        edition=1, 
        price=50.00, 
        authorIds=[2, 1]
    )
]