Convert a Postgres query into JPQL

Question

I have the following query in Postgres:

SELECT *
from table1
         LEFT OUTER JOIN table1.table2 ON table1.latest_id = table2.id
WHERE table1.table2.status = 0
  AND table1.id NOT IN (
    (SELECT id from table3 where userId = table2.user))

I do not have the ability to join table1 and table3 and I am stuck writing the subquery in a format JPA will understand - working with a spring boot app. Here is where I got so far in my repository class:

  @Query("SELECT c FROM #{#entityName} c JOIN FETCH c.table2 WHERE c.table2.status = 0")
  fun findByIdAndStatus(id: String): MyEntity

I have attempted at the subquery as follows, but with no joy - there is a clear syntax error I cannot wrap my head around:

    @Query("SELECT c FROM #{#entityName} c JOIN FETCH c.table2 WHERE c.table2.status = 0" AND c.id NOT IN (" +
"SELECT * FROM Table3 WHERE userId = c.table2.user")

Can you help?

Thank you

Adam Gaj · Accepted Answer · 2020-09-09 11:40:14Z

1

Assuming:

your SQL query doesn't utilize any input arguments
table1 is mapped by MyEntity and table3 is mapped by Table3 entity
entity associated with table2 is mapped in MyEntity using latest_id join column
status is mapped as a number (i.e Long / Integer)

You can rewrite the query to following JPQL form:

    @Query("SELECT t1 FROM MyEntity t1 "
            + "JOIN t1.table2 t2 "
            + "WHERE t2.status = 0 AND t1.id NOT IN ("
            + "       SELECT t3.id from Table3 t3 "
            + "       WHERE t3.userId = t2.user "
            + ")")
    List<MyEntity> findMyEntities();

edited Sep 9, 2020 at 11:40

answered Sep 8, 2020 at 16:49

Adam Gaj

3151 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

panza Over a year ago

Thank for your reply. I actually use an input arguments which is t1.id. How would that affect my JPQL query?

Adam Gaj Over a year ago

In such case you just need to add in outer where a condition for t1.id = :identifier and alter method signature by adding Long identifier argument.

Collectives™ on Stack Overflow

Convert a Postgres query into JPQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related