[FIXED] Spring Data JPA Query: MYSQL TO @QUERY

Issue

Can someone please translate me the following mysql query to @Query?

Mysql-query:

SELECT * 
FROM game JOIN game_teams ON game.id = game_teams.game_id
    JOIN team_members ON game_teams.teams_id = team_members.team_id
WHERE start_date IS NOT NULL AND end_date IS NOT NULL AND members_username = 'user2'

This query retrieves more columns than columns in game table. Maybe I have to select just the columns that are in the game table?

I need something like this:

@Query("???")
Collection<Game> findUserPlayedGames(User user);

Solution

@Query(value = "SELECT * "
        + " FROM game g JOIN game_teams gt ON g.id = gt.game_id "
        + "     JOIN team_members tm ON gt.teams_id = tm.team_id "
        + " WHERE g.start_date IS NOT NULL AND g.end_date IS NOT NULL AND tm.members_username = 'user2'",
        nativeQuery = true)
Collection<Game> findUserPlayedGames(User user);

Answered By - Created