Spring-Data-Jpa: INNER JOIN with Subquery

Question

I have the following problem. I want to execute this query in my spring boot project. I tried to do this with the query annotation in the JPA repository interface. But it says "unexpected SELECT" at the inner join. When I execute this query directly on my mySQL database, it will work.

Do anyone have a solution for this case?

This is my query:

SELECT t1.*
FROM az_manager t1
INNER JOIN
(
    SELECT maID, MAX(datum) AS max_date
    FROM az_manager
    WHERE maID IN (7243, 1)
    GROUP BY maID
) t2
   ON t1.maID = t2.maID AND t1.datum = t2.max_date
WHERE
    t1.maID IN (7243, 1);

This is my class:

@Entity
@Table(name = "az_manager")
@IdClass(TnsWorkingHoursManagerId.class)
@Getter
@Setter
public class TnsWorkingHoursManager extends TnsObject{

    @Id
    @Column(name = "datum")
    private long date;

    @Id
    @Column(name = "maid")
    private int employeeId;

    @Column(name = "typid")
    private int typeId;

    @Column(name = "bemerkung")
    private String comment;

    @Column(name = "host")
    private String host;

    @Column(name = "modus")
    private byte mode;

    public TnsWorkingHoursManager() {
    }

}

Here is my try with the JPA repository:

@Query(value = "SELECT azm1 FROM az_manager azm1 INNER JOIN (SELECT maID, MAX(datum) AS max_date FROM az_manager WHERE maID IN(:userIds) GROUP BY maID) azm2 ON azm1.maID = azm2.maID AND azm1.datum = azm2.max_date WHERE azm1.maID IN (:userIds)")
    List<TnsWorkingHoursManager> getLastEntries(@Param("userIds") ArrayList<Integer> userIds);

At the second select it says "'SELECT' unexpected"

I tried to do this with the query annotation in the JPA. Then show it — Nikolai Shevchenko

Dimitar Spasovski Dimitar Spasovski · Accepted Answer · 2018-10-24T20:36:34

For anyone else that might stumble upon this question:

If you don't add the nativeQuery = true parameter to the @Query annotation in a Spring Repository, the query will be considered as written in JPQL.

From the JPQL docs:

Subqueries may be used in the WHERE or HAVING clause.

Based on the quote above, the JPQL (Java Persistence Query Language) does not support subqueries in the FROM clause and that is why OP had to make the query native in order for it to work.

Spring-Data-Jpa: INNER JOIN with Subquery

2 Answers