postgresql - 多条件等关系的子查询如何写jpql查询

Question

我想通过 JPQL 触发以下查询。我是 JPA 的新手，正在学习 JPQL，但遇到了这个查询。

SELECT * FROM users
WHERE userid NOT IN (SELECT userid FROM applicants)
AND gender = 'MALE' AND (firstname like '%R%' OR lastname like '%S%');

这里性别和名字参数是动态传递的，但这里我保持静态。

我的类(class)片段是:这是我的用户类
用户类别:

@Table(name = "users")
public class User{

@Id
@GeneratedValue
private long userId;

@Column(unique = true, nullable = false)
private String email;

private String firstName;

private String middleName;

private String lastName;

private String gender;

private Timestamp appliedOn;
}

Applicant类和User类是一对一的关系，join列是userid。

申请人类别:

@Entity
@Table(name = "applicants")
public class Applicant{

@Id
private long applicantId;

@OneToOne
@JoinColumn(name = "userId")
private User user;

@ManyToOne
@JoinColumn(name = "subCourseId")
private SubCourse course;

@ManyToOne
@JoinColumn(name = "collegeId")
private College college;
}

Answer 1

你想通过 LEFT JOIN 实现的不是吗:

SELECT * FROM users
LEFT JOIN applicants ON users.userid = applicants.userid
WHERE applicants.userid IS NULL
AND gender = 'MALE' AND (firstname like '%R%' OR lastname like '%S%');

或者一个不在:

SELECT * FROM users
WHERE userid NOT IN (SELECT userid FROM applicants)
AND gender = 'MALE' AND (firstname like '%R%' OR lastname like '%S%');

如果是，您可能应该从那里开始找到答案。