CODEWITHSUNDEEP
javasqlspringpostgresqljpql
toto africa
toto africa

Reputation: 1

Can I use nested queries with @Query in JPA?

I have a task entity, which holds all user's tasks in the company. so it is @ManytoOne relationship. (many tasks belongs to specific user)

I want to calculate the sum:

(task_sum_of_user1 - avg)^2 + (task_sum_of_user2 - avg)^2 + .... (task_sum_of_user_N - avg)^2

I tested a query in Postgresql database and this one works:

select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v

But when I run the query in @Query annotation it doesn't seems to work:

@Query("select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v
")

it throws exception "unexpected token: (" What am I missing?

Upvotes: 0

Views: 2380

Answers (1)

Andronicus
Andronicus

Reputation: 26056

You cannot use dbms's specific functions inside @Query, as it takes jpql as value. You can create a custom @Repository with native query, like that:

entityManager
    .createNativeQuery("select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v")
    .getSingleResult();

You need to map the result somehow or provide it as the second parameter of createNativeQuery.

As @Billy Frost noticed, adding nativeQuery flag as true to @Query is also an option.

Upvotes: 1

Related Questions