Complex Query

[Al Grant]

Hello,

I would like assistance constructing a query. I have Entities:

@Entity
public class Episode {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   @NotEmpty
   private String eventno;
   @ManyToOne(cascade = CascadeType.ALL)
   private Address address;

@Entity
public class EpisodePerson {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   
   @ManyToOne
   @Valid
   private Person person;

   @ManyToOne
   private Episode episode;

@Entity
@Table(uniqueConstraints = @UniqueConstraint(columnNames = {"nia"}))
public class Person {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
   private String surname;
   private String firstname;
   private String gender;

The query needs to support the column filtering I have put in place in this UI:

In the screenshot above I am already filtering on Episode.event and Episode.address.formattedAddress with

BooleanBuilder where = new BooleanBuilder();
if (pagination.getFilterBy().getMapOfFilters().get("eventno")!=null) {
   where.and(qEpisode.eventno.containsIgnoreCase(pagination.getFilterBy().getMapOfFilters().get("eventno")));
}
if (pagination.getFilterBy().getMapOfFilters().get("address")!=null) {
   where.and(qEpisode.address.formattedAddress.containsIgnoreCase(pagination.getFilterBy().getMapOfFilters().get("address")));
}

But now I need to add the predicate which will allow the "Case Name" (ie Surname1 + Surname2) to be searched.

Each Episode has a collection of People access through Episode > EpisodePerson > List of People contained in Episode.

Episode > EpisodePerson I assume would be a join?

Any guidance on this greatly appreciated. I am very new to QueryDSL so a detailed step by step answer for a newbie would be greatly appreciated.

Thanks

-Al