2.5 Property Filter

Jimmer supports property-level filters, allowing you to set where filtering conditions and orderBy sorting for associated objects (rather than the current objects targeted by the main query statement).

Querying Dynamic Entities

Java

BookTable table = Tables.BOOK_TABLE;
List<Book> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("GraphQL in Action"))
    .select(
        table.fetch(
            Fetchers.BOOK_FETCHER
                .allScalarFields()
                .authors(
                    Fetchers.AUTHOR_FETCHER
                        .allScalarFields(),
                    cfg -> cfg.filter(args -> {
                        AuthorTable author = args.getTable();
                        args.where(
                            Predicate.or(
                                author.firstName().ilike("a"),
                                author.lastName().ilike("a")
                            )
                        );
                        args.orderBy(
                            author.firstName(),
                            author.lastName()
                        );
                    })
                )
        )
    )
    .execute();

Kotlin

val books = sqlClient.createQuery(Book::class) {
    where(table.name eq "GraphQL in Action")
    select(
        table.fetchBy {
            allScalarFields()
            authors({
                filter {
                    where(
                        or(
                            table.firstName ilike "a",
                            table.lastName ilike "a"
                        )
                    )
                    orderBy(
                        table.firstName,
                        table.lastName
                    )
                }
            }) {
                allScalarFields()
            }
        }
    )
}

For each returned Book object, its associated collection Book.authors may not contain all associated objects from the database, because property-level filtering has been applied to that association collection.

Querying Static DTOs

Create a file with a .dto extension in the src/main/dto folder and edit the code as follows:

Book.dto

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

BookDetailView {

    #allScalars

    !where(firstName ilike '%a%' or lastName ilike '%a%')
    !orderBy(firstName asc, lastName asc)
    authors {
        #allScalars
    }
}

Compile the project to automatically generate the Java/Kotlin type BookDetailView

Java

BookTable table = Tables.BOOK_TABLE;
List<BookDetailView> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("GraphQL in Action"))
    .select(
        table.fetch(BookDetailView.class)
    )
    .execute();

Kotlin

val books = sqlClient.createQuery(Book::class) {
    where(table.name eq "GraphQL in Action")
    select(
        table.fetch(BookDetailView::class)
    )
}

For each returned Book object, its associated collection Book.authors may not contain all associated objects from the database, because property-level filtering has been applied to that association collection.