CODEWITHSUNDEEP
javasqlspringjpaspring-data-jpa
Ben
Ben

Reputation: 4297

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;

Upvotes: 1

Views: 578

Answers (2)

CIPHER007
CIPHER007

Reputation: 376

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

Upvotes: 0

Akash Thakare
Akash Thakare

Reputation: 23012

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

Upvotes: 2

Related Questions