5.1 Basic Usage

Multiple orderBy actions

Java

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .orderBy(table.name().asc())
    .orderBy(table.edition().desc())
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        orderBy(table.name.asc())
        orderBy(table.edition.desc())
        select(table)
    }
    .execute()

orderBy with multiple arguments

There is another way to write the equivalent of the above code

Java

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .orderBy(
        table.name().asc(), 
        table.edition().desc()
    )
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        orderBy(
            table.name.asc(), 
            table.edition.desc()
        )
        select(table)
    }
    .execute()

However, the previous way of writing is more conducive to the organization of the code structure, so the previous way of writing is more recommended.

Sorting based on subqueries

Java

BookTable table = BookTable.$;
AuthorTableEx author = AuthorTableEx.$;

List<Book> books = sqlClient
    .createQuery(table)
    .orderBy(
        sqlClient
            .createSubQuery(author)
            .where(author.books().eq(table))
            .select(Expression.rowCount())
            .desc()
    )
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        orderBy(
            subQuery(Author::class) {
                where(table.books() eq parentTable)
                select(rowCount())
            }
            .desc()
        )
        select(table)
    }
    .execute()