javahibernatejpaspring-data

Fetching records matching multiple joined attributes


I need to return User records that:

  1. Have an email matching email strictly and
  2. Have a phone matching phone strictly

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

Solution

  • 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
            ))
            """)