I am attempting to make a query through the User table with a standard CrudRepository that returns each ChessGame associated with the Players associated with the User.
User entity has a @OneToMany private final List<Player> players;
Player entity has a @OneToOne private final ChessGame game;
So I need to select one query from the 1st table, which will be associated with a list of items from the 2nd table, and then the desired result will come from a 3rd table via the 1v1 relationships with the 2nd table. Aside from the layers, the query is straightforward. After some research, this was my first attempt -
@Query("SELECT p.game FROM Player p WHERE p IN (SELECT u.players FROM User u WHERE u.name = :name)")
List<ChessGame> findUserGames(@Param("name") String name);
(name is a unique Id)
The app compiles but when I attempt to use this query, it returns -
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. from user user2_ cross join player players3_ where user2_.name=players3_.user_' at line 1
That seems to suggest that the query is attempting to compare equality of a name to a user but the error message inside the single quotes must be cut off because I am also utilizing a query that returns all the Players associated with the User, ie the inner SELECT and it works fine on its own.
Anyways, I have tried multiple other derivations but they return the same error
@Query("SELECT g FROM ChessGame g WHERE g IN (SELECT p.game FROM Player p WHERE p IN (SELECT u.players FROM User u WHERE u.name = :name) )")
The easiest way would be with a named query but the compiler won't accept that.
@Query("SELECT p.game FROM (SELECT u.players FROM User u WHERE u.name = :name) p")
I'm stuck. What am I missing?
Edit- I am attempting to do this in the most efficient way possible. I can easily accomplish this programmatically but I know that will never be as fast as a standard database query. But if I am using Joins, I am losing some, if not all, of that peak efficiency. While the query does traverse three separate database tables, I do not need to combine columns at any point. Is it possible to accomplish this query without a Join?