how to join in jpql? - org.hibernate.query.SyntaxException

Question

Having these entities:

User.java:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;
    private String username;
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER)
    private List<Nickname> nicknames;
}

Nickname.java:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "nickname")
public class Nickname {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String value;
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    @Override
    public String toString() {
        return "Nickname{" +
                "id=" + id +
                ", value='" + value + '\'' +
                '}';
    }
}

and nickname repository:

public interface NicknameRepository extends JpaRepository<Nickname, Long> {
    @Transactional
    @Modifying
//    @Query("DELETE FROM Nickname n WHERE n.value = :value AND n.user.username = :username")
    @Query("DELETE FROM Nickname n JOIN n.user u where n.value = :value AND u.username = :username")
    void deleteNicknameByValue(@Param("username") String username, @Param("value") String value);
}

I am trying to join these two entities as I need username from User and value from Nickname. I get

java.lang.IllegalArgumentException: org.hibernate.query.SyntaxException: At 1:23 and token 'JOIN', mismatched input 'JOIN', expecting one of the following tokens: <EOF>, WHERE [DELETE FROM Nickname n JOIN n.user u where n.value = :value AND u.username = :username]

whats wrong with that join?

Answer 1

From the documentation

The structure of DELETE queries is very simple relative to the structure of SELECT queries. DELETE queries cannot include multiple variables and JOIN, and cannot include the GROUP BY, HAVING and ORDER BY clauses.

IN clause can be used here to delete Nicknames.

@Query("DELETE FROM Nickname n where n.value = :value AND n.id IN (SELECT n2.id FROM Nickname n2 JOIN n2.user u WHERE u.username = :username)")
void deleteNicknameByValue(@Param("username") String username, @Param("value") String value);