I need to return User
records that:
email
strictly andphone
strictlyAll of those are optional. That is, the request may contain only an email, for example.
Here's my attempt.
import com.example.pixel_user_api.data.entity.User;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.RepositoryDefinition;
import org.springframework.data.repository.query.Param;
import java.util.List;
@RepositoryDefinition(domainClass = User.class, idClass = Long.class)
public interface UserRepository {
@Query("""
SELECT User u
JOIN FETCH PhoneDate pd
JOIN EmailData ed
AND (:phone IS NULL OR pd.phone = :phone)
AND (:email IS NULL OR ed.email = :email)
""")
List<User> find(@Param("phone") String phone,
@Param("email") String email);
}
Apparently, two joins in a single JPQL query are illegal (due to the Cartesian product issue, I reckon). My app fails on startup:
Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: At 2:0 and token 'JOIN', mismatched input 'JOIN', expecting one of the following tokens: <EOF>, ',', EXCEPT, FROM, GROUP, HAVING, INTERSECT, ORDER, UNION, WHERE;
So how do I conduct a search involving more than one associated "to-many" entity – given a relational data store?
My entities (simplified):
import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Entity
@Getter
@Setter
@Table(name = "\"user\"")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@OneToOne(mappedBy = "user")
@OneToMany(mappedBy = "user", fetch = FetchType.EAGER,
cascade = CascadeType.ALL, orphanRemoval = true)
private List<EmailData> emailData;
@OneToMany(mappedBy = "user", fetch = FetchType.EAGER,
cascade = CascadeType.ALL, orphanRemoval = true)
private List<PhoneData> phoneData;
}
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;
@Entity
@Getter
@Setter
@Table(name = "phone_data")
public class PhoneData {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
private String phone;
}
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;
@Entity
@Getter
@Setter
@Table(name = "email_data")
public class EmailData {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
private String email;
}
This JPQL will do the trick
@Query("""
SELECT u
FROM User u
WHERE (:email IS NULL OR EXISTS (
SELECT 1 FROM EmailData ed WHERE ed.user = u AND ed.email = :email
))
AND (:phone IS NULL OR EXISTS (
SELECT 1 FROM PhoneData pd WHERE pd.user = u AND pd.phone = :phone
))
""")