Spring Data: Order by nested property

Question

I'm using JpaRepository and JpaSpecificationExecutor from Spring Data and i'm having a problem to sort the method findAll(specification, pageable, sort)

I want to sort the result of a specification by a nested property from the main repo class. This is my case:

the main class

class Foo {
    //other properties
    @OneToMany(mappedBy="foo")
    private Set<Bar> bars;
}

the ordering class

class Bar {
    @ManyToOne
    @JoinColumn(name="fooId")
    private Foo foo;

    //I WANT TO SORT BY THIS FIELD
    @Column
    private Date date;
}

and this is my repo

interface FooRepo extends JpaRepository<Foo , Long>, 
        JpaSpecificationExecutor<Foo>{
    //just jparepo methods
}

this is how i'm trying order this result

void anymethod(){
    Sort sort = new Sort(Bar_.date.getName());
    PageRequest pr = new PageRequest(anyPage, anyMaxResultsNum, sort);
    repository.findAll(anySpecification, pr);

}

and when i run this i'm getting the "PropertyReferenceException: No property date found for type Foo!"

How can i do this?

score 7 · Answer 1 · answered Apr 17 '15 at 14:13

7

You could use the @javax.persistence.OrderBy annotation:

@OneToMany(mappedBy="foo")
@OrderBy("date")
private Set<Bar> bars;

answered Apr 17 '15 at 14:13

sp00m

47,968
31
142
252

2

it's a good approach but i want to sorting by this field in just one time. If i use this i'm sorting by bars.date in all of repository.findAll calls – pablobaldez Apr 17 '15 at 14:19
@pablobaldez Then you're right, this solution won't suit your needs. – sp00m Apr 17 '15 at 14:29

score 0 · Answer 2 · edited May 23 '17 at 12:33

0

The date field is defined on Bar, not Foo. So define a BarRepo and call findAll() on the BarRepo using the date sort. Since you have a bi-directional association, you can get Foo from each Bar returned by findAll() and filter out any duplicate Foo.

Or you can try using @Query annotation on a FooRepo interface method to define native sql to execute.

edited May 23 '17 at 12:33

Community

1
1

answered Apr 17 '15 at 14:07

Shane Voisard

1,145
8
12

I have many specifications works to Foo, so the FooRepo is excencial. – pablobaldez Apr 17 '15 at 14:13
i want to do some like this: "new Sort (Order("bars.date")) //this working as well" - but using metamodel classes. You know how can i navigate between metamodel properties? – pablobaldez Apr 17 '15 at 14:22
If you want to sort a list of `Foo`, you need to pick one `Bar` for each `Foo` to sort by, right? – Shane Voisard Apr 17 '15 at 14:27
Yes. I had thought the join colunm resolve this for me, and the repo would do this automaticaly – pablobaldez Apr 17 '15 at 14:41
There's no way for JPARepository to infer which `Bar` to pick from list to sort `Foo` by. What should JPARepository do if a `Foo` has empty `Bar` list? – Shane Voisard Apr 17 '15 at 14:45
I understand what you say and don't have many knowledge about jparepository, but with sql this will works – pablobaldez Apr 17 '15 at 14:51
2

"select * from foo inner join bar on foo.id = bar.fooId order by bar.date" - this will works, right? How can i do this with criteria,jpa repo (for foo) and specifications (that returns predicates) – pablobaldez Apr 17 '15 at 14:52

Spring Data: Order by nested property

2 Answers2