Exception when i am trying to execute delete @Query

Question

I am trying to execute this @Query in my EmployeeRepository class

    @Modifying
    @Query(value = "DELETE FROM Employee e WHERE e.department.companyBranch.id = :companyBranchId")
    void deleteAllByCompanyBranchId(@Param("companyBranchId") Long companyBranchId);

But I am getting this exception

Column "D1_0.COMPANY_BRANCH_ID" not found; SQL statement:
delete from employee where d1_0.company_branch_id=? [42122-214]] [delete from employee where d1_0.company_branch_id=?]; SQL [delete from employee where d1_0.company_branch_id=?]] with root cause

org.h2.jdbc.JdbcSQLSyntaxErrorException: Столбец "D1_0.COMPANY_BRANCH_ID" не найден
Column "D1_0.COMPANY_BRANCH_ID" not found; SQL statement:
delete from employee where d1_0.company_branch_id=? [42122-214]

I think it is somehow connected to JPQL conventions, but I am not sure. By looking at my exception I can say that the query is trying to find the COMPANY_BRANCH_ID field in EMPLOYEE table, but I am not sure about it.

This is my entity classes:

Employee:

@Entity
@Table(name = "EMPLOYEE")
@Getter
@Setter
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "DEPARTMENT_ID", nullable = false)
    private Department department;
}

Department

@Entity
@Table(name = "DEPARTMENT")
@Getter
@Setter
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "COMPANY_BRANCH_ID", nullable = false)
    private CompanyBranch companyBranch;
}

CompanyBranch

@Entity
@Table(name = "COMPANY_BRANCH")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class CompanyBranch {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

Answer 1

I'm not sure you can use this level of nested queries.

I'd try to go with something like :

@Query(value = "DELETE FROM Employee e WHERE e.department.id IN (SELECT d.id from Departement d where d.companyBranch.id = :companyBranchId"))

That's the kind of query you would normally do in plain sql.