Sorting by relation property

Here’s my working database setup:

@Entity
class Foo {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOO_ID")
    private Set<Bar> bars;

//...

}

@Entity
class Bar {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;

    @Column(name = "STATUS")
    private String status;
    //...
}

FooRepository extends CrudRepository {

@Query("select distinct f from Foo f left join f.bars b where b.status = :status ")
public Page<Bar> findByBarStatus(@Param("status") BarStatus status, Pageable pageable);

}

I’d like to be able to sort this query by Bar.status, here’s how I tried to change the query:

@Query("select distinct f from Foo f left join f.bars b where b.status = :status order by b.status desc")
public Set<Bar> findByBarStatus(@Param("status") BarStatus status);

However that causes sql syntax error:

org.h2.jdbc.JdbcSQLException: Order by expression "BARS1_.STATUS" must be in the result list in this case;

Here you have applied distinct on f and thus you can not have some other column in order by. Actually, order by item must be there in select list.

So, the problem is in query, you can remove distinct if you are sure that f will be unique (but that’s not the case I guess) or you can try with clause,

with temp as  
(select distinct f, b.status 
 from Foo f left join f.bars b 
 where b.status = :status order by b.status desc) 
select f from temp

you have to call b.status in your query select statement like below query.

    select DISTINCT(f), b.status from Foo f
 LEFT JOIN f.bars b where b.status = :status order by b.status desc