basically I need to write a JPA specification that would return all Parent entities ordered by Child's parameter (LocalDate date).

@Entity
public class Parent {
    @Id
    private UUID id;
    @OneToMany(mappedBy = "parent", cascade = CascadeType.MERGE)
    private List<Child> children = new ArrayList<>();
}


@Entity
public class Child {
    @Id
    private UUID id;
    @ManyToOne
    private Parent parent;
    private LocalDate date;
}

I also don't understand why this approach wouldn't work:

return (root, query, builder) -> {
    Join<Parent, Children> childrenJoin = root.join("children", JoinType.LEFT);

    query.orderBy(sortOrder.equals("asc") ? 
        builder.asc(builder.max(childrenJoin.get("date"))) : 
        builder.desc(builder.max(childrenJoin.get("date"))));

    return builder.conjunction();
};

Using SQL the query needs to look like this:

SELECT p.id, max(c.date) FROM schema.parent p
LEFT JOIN schema.child c ON c.parent_id = p.id
GROUP BY p.id
ORDER BY MAX(c.date) DESC;

Thanks in advance!

EDIT: The SQL query sorts all the records in the correct order (by date ascending or descending). Using the provided specification, repository.findAll(specification) returns records in a different (not correct) order than the SQL query.

I guess what I'm asking is if it is the correct way to implement such query using JPA specifications or is there something that I'm missing. I'm sure there is because of the difference between SQL query result and the JPA specification result.