Query Predicate in QueryDSL

Question

The environment is Java, Spring-boot, Hibernat, QueryDSL, MySQL.

I have table structure

Episode

+----+-------------+--------
| id | address_id  | eventno
+----+-------------+--------
|  5 |         27  | F123
|  6 |         30  | F456
|  7 |         45  | F789
+----+-------------+--------

@Entity
public class Episode {

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

Episode_Person

+----+--------------+--------------+------------+-----------+
| id | episode_role | primary_flag | episode_id | person_id |
+----+--------------+--------------+------------+-----------+
| 19 | Buyer        |              |          5 |         1 |
| 20 | Subject      |              |          5 |         2 |
| 23 | Witness      |              |          6 |         3 |
| 24 | Child        |              |          6 |         4 |
| 27 | Buyer        |              |          5 |         3 |
| 63 | Investor     |              |          5 |         4 |
| 64 | Subject      |              |          7 |         1 |
| 65 | Subject      |              |          7 |         3 |

@Entity
public class EpisodePerson {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @Valid
    private Person person;

    @ManyToOne
    private Episode episode;

Person

+----+-----------+----------+
| id | firstname | surname  |
+----+-----------+----------+
|  1 | Clint     | eastwood |
|  2 | Angelina  | joilee   |
|  3 | Brad      | pitt     |
|  4 | Jennifer  | aniston  |

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

So each episode has multiple people. And the join table is Episode_Person.

My UI has a datatable with a filter on each column:

The filtering already works on Event and Address. And looks like this predicate in QueryDSL:

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")));
}
where.and(qEpisode.creatingUser.eq(user));
List<Episode> e = episodeRepository.findAll(where);

How would I now add a 3rd predicate for case name where case name is constructed of the first two people returned in the collection of people against a episode?

UPDATE

For clarification the DTO thats backs the UI view contains the "casename" attribute. It is created in the service layer when Domain objects are converted to DTO:

episodeDashboard.setNames(episodePersonList.get(0).getPerson().getSurname().toUpperCase() +" & " +episodePersonList.get(1).getPerson().getSurname().toUpperCase());

Answer 1

Not easily unless you delegate some of the processing to the database.

If we can get the case_name property to be populated at the database tier rather than as a derived property in the application logic then the front-end code becomes trivial.

We can do this by means of a view. The exact definition of this will depend on your database however the output would be something like this:

episode_summary_vw

+------------+-------------------------+
| epsiode_id | case_name               |
+------------+-------------------------+
|  5         |        Eastwood & Joilee| 
|  6         |           Pitt & Aniston| 
|  7         |           Aniston & Pitt| 
+------------+-------------------------+

For Oracle it looks like LISTAGG function is what you would want and for MySQL the GROUP_CONCAT functions. In MySQL then I think this would look something like:

CREATE VIEW episode_summary_vw as
SELECT ep.episode_id, GROUP_CONCAT(p.surname SEPARATOR ' & ')
FROM episode_person ep
INNER JOIN person p on p.id = ep.person_id
GROUP BY ep.episode_id;
-- todo: needs limit to first 2 records

Once we have a view then we can simply map the case_name to the Episode entity using the @SecondaryTable functionality of JPA:

@Entity
@Table(name = "episodes")
@SecondaryTable(name = "episode_summary_vw", primaryKeyJoinColumna = @PrimaryKeyJoinColumn(name="episode_id", reference_column_name="id"))
public class Episode {

    @Column(name ="case_name", table = "episode_summary_vw")
    private String caseName;
}

You then filter and sort on the property as for any other field:

if (pagination.getFilterBy().getMapOfFilters().get("caseName")!=null) {

    where.and(qEpisode.caseName.containsIgnoreCase(pagination.getFilterBy().
       getMapOfFilters().get("caseName")));
}