Search code examples
springjpajpql

JQPL Custom Query

I have this Spring JPA application I am working on in class and I'm trying to figure out how to get a custom query to work. I have 3 tables setup with relationships( UserAccess -> Profiles -> Projects ). I have this code that does work, BUT it gives me all of the projects in the table. Not a specific users which I am trying to do by their userId. I know normal SQL works in this matter but JPQL does not apparently. Any help appreciated!

@Repository
public interface ProjectRepository extends CrudRepository<Project,Integer>{

/*

select * from Projects where profID = 
(select profID from Profiles where userAccessID = 1);    

*/
@Query("SELECT p FROM Project AS p WHERE p.profID = (SELECT p.profID FROM Profile AS f WHERE f.userAccessId = (SELECT u.userAccessId FROM UserAccess AS u WHERE u.userAccessId = :userID))")
public abstract List<Project> find(@Param("userID") int userID);

}

Solution

  • Should the last bit of the query be SELECT u.userAccessId FROM UserAccess AS u WHERE u.userId = :userID?