jOOQ fetchGroups does not return an empty collection for a one to many relationship

Question

I have the following query for a one to many relationship.

return create.select(Parent.asterisk(), Child.asterisk())
    .from(PARENT)
    .leftJoin(CHILD)
    .onKey()
    .where(myCondition)
    .fetchGroups(ParentRecord.class, ChildRecord.class);

When there are no child records I do not get an empty list. There is always one child record with all its fields set to null.

[Child(id=null, name=null)]

What is the best way to prevent these null records from being returned?

Answer 1

This is a popular use-case, so I've blogged about this part of jOOQ's API here.

You could use your own collectors:

Map<ParentRecord, List<ChildRecord>> result =
create.select(PARENT.asterisk(), CHILD.asterisk())
    .from(PARENT)
    .leftJoin(CHILD).onKey()
    .where(myCondition)
    .collect(groupingBy(
        r -> r.into(PARENT), filtering(
            r -> r.get(CHILD.ID) != null, mapping(
                r -> r.into(CHILD), toList()
            )
        )
    ));

I'm assuming these static imports:

import static java.util.stream.Collectors.*;
import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;

This is a frequent question. It probably makes sense to improve the current API: https://github.com/jOOQ/jOOQ/issues/11888