CODEWITHSUNDEEP
postgresqljpahqljpql
Ridham Tarpara
Ridham Tarpara

Reputation: 6160

How to write jpql query for subquery with multiple conditions and other relationships

I want to fire following query through JPQL.I am new to JPA and learning JPQL but stuck at this query.

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

Here gender and name parameters are passed dynamically but here i kept it static.

My Classes Snippets are: This is my user class
User 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 class has OneToOne relationship with User class and join column is userid.

Applicant Class:

@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;
}

Upvotes: 1

Views: 1653

Answers (2)

Sergiy Rezvan
Sergiy Rezvan

Reputation: 1

JPQL query for that SQL 

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

will be the next:

String jpql = "SELECT us FROM User us WHERE us.userId NOT IN (SELECT appl.user.userId FROM Applicant appl) AND us.gender = ?1 AND (us.firstName LIKE ?2 OR us.lastName LIKE ?3)";
Query query = entityManager.createQuery(jqpl);
query.setParameter(1, "MALE");
query.setParameter(2, "%R%");
query.setParameter(3, "%S%");
query.getResultList();

Upvotes: 0

Anthony Drogon
Anthony Drogon

Reputation: 1864

Isn't what you want to achieve achievable with a 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%');

Or a NOT IN:

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

If yes, you should probably find your answer starting from there.

Upvotes: 2

Related Questions