0

I´m working on a Spring Boot App and struggling on a SQL-Query in my repository. The query should select all IDs from employees which never postet in my Webapp.

In MySQL Workbench following statement ist working propper and I achive the result like expected.

SELECT e.id FROM employees e LEFT JOIN posts p ON p.owner_id = e.id WHERE p.owner_id IS NULL

But I cant find out how to write it in my PostRepository which extends a JpaRepository from Spring. This is the error message

o.h.hql.internal.ast.ErrorCounter : Invalid path: 'p.owner_id

This is the statment I use in my repository.

@Query("SELECT e FROM employees e LEFT JOIN p.posts p ON p.owner_id = e.id WHERE p.owner_id IS NULL")
List<Employee> getEmployeeIdsNeverVoted();

I don´t know how to use the "ON" and define the path for it.

Wildchild
  • 13
  • 1
  • 1
  • 3
  • You have a typo in the statement in your respository. `SELECT e FROM employees e` -> `SELECT e.id FROM employees e` – Ferhat Sayan Mar 01 '16 at 13:16
  • Possible duplicate of [Joining two table entities in Spring Data JPA](http://stackoverflow.com/questions/19977130/joining-two-table-entities-in-spring-data-jpa) – Christiaan Janssen Mar 01 '16 at 13:31
  • Finally I found a solution. According to my DB I had to change also my DB-Model. Because hibernate created also always a table which was connected but not used. – Wildchild Mar 02 '16 at 15:22

1 Answers1

0

Your Query and your string does not look the same on the table on which you want to join. You have 'posts p' in your query, but p.posts p in your string query and e.id in your query, but e in your string query:

SELECT e.id FROM employees e LEFT JOIN posts p ON p.owner_id = e.id WHERE p.owner_id IS NULL
@Query("SELECT e FROM employees e LEFT JOIN p.posts p ON p.owner_id = e.id WHERE p.owner_id IS NULL")
Appleman
  • 126
  • 7