incorrect @query in spring boot-CodePudding

I am trying to use this query in springboot so I can display the results in a webpage. I know that this query works because I tested it in postgresql and it gave me the right results.
But JPA is telling me that the '(' after the first FROM is an unexpected token and the query was therefore viewed as invalid.
This is my query:

@Query(
"SELECT com.example.imse22.model.TrvlA_Cust_Dto(books_query.name, count(travelA_query.customer_id)) "  
            "FROM (SELECT DISTINCT customer_servant.employee_id, books.customer_id FROM customer_servant "  
            "INNER JOIN books ON customer_servant.employee_id = books.customer_servant_id) AS travelA_query "  
            "INNER JOIN "  
            "(SELECT travel_agency.id, travel_agency.name, employee.employee_id FROM travel_agency "  
            "INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) AS books_query "  
            "ON travelA_query.employee_id = books_query.employee_id "  
            "GROUOP BY travelA_query.name")

can somebody help me out how I could rewrite the query so that JPA approves it?

CodePudding user response：

Your query is native so you should declare it in that way:

@Query(
 value = "SELECT com.example.imse22.model.TrvlA_Cust_Dto(books_query.name, count(travelA_query.customer_id)) "  
        "FROM (SELECT DISTINCT customer_servant.employee_id, books.customer_id FROM customer_servant "  
        "INNER JOIN books ON customer_servant.employee_id = books.customer_servant_id) AS travelA_query "  
        "INNER JOIN "  
        "(SELECT travel_agency.id, travel_agency.name, employee.employee_id FROM travel_agency "  
        "INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) AS books_query "  
        "ON travelA_query.employee_id = books_query.employee_id "  
        "GROUOP BY travelA_query.name", nativeQuery = true)

link point 2.2: https://www.baeldung.com/spring-data-jpa-query

CodePudding user response：

Ok so this is how I solved it:
I changed my query into a native one just like @notAPPP pointed out and then I only had to add an alias for the LEFT JOIN (also I changed INNER JOIN to LEFT JOIN).
here the code example:

@Query(value = "SELECT combined.name as name, count(combined.customer_id) as id "  
       "FROM ("  
       "(SELECT travel_agency.name, travel_agency.id, employee.employee_id "  
       "FROM travel_agency INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) as trvlEmp "  
       "LEFT JOIN "  
       "(SELECT books.customer_id, books.customer_servant_id, customer_servant.employee_id "  
       "FROM books INNER JOIN customer_servant ON books.customer_servant_id = customer_servant.employee_id) as custBooks "  
       "ON trvlEmp.employee_id = custBooks.employee_id) as combined "   // this "AS combined" got added
       "GROUP BY combined.name", nativeQuery = true)

This makes sense, because after a FROM clause one should wirte the name of a table or a result table (e.g. from two joined queries like in my case). As I didnt specify an alias for the LEFT JOIN of my two subqueries, JPA obviously didnt know how to handle the result of those subqueries. Therefore always name your subqueries if they are not used in a WHERE clause, but rather with a FROM clause, like in my case. E.g. the name I gave my LEFT JOIN is "combined" as seen in the code example above.

Also I changed my INNER JOIN to a LEFT JOIN to get the value 0 of the elements that have 0 counts of what I wanted to count in the table.

If you want to know how to handle the result which such a query returns follow this link.thorben-janssen.com/spring-data-jpa-dto-native-queries