JPQL how to JOIN FETCH a List with anymatch condition against the List

Question

Given Entities:

@Entity
@Data
public class Collection {

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

  private String createdBy;

  @OneToMany(mappedBy = "collection")
  private List<CollectionAccess> collectionAccesses;

}

@Entity
@Data
public class CollectionAccess {

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

  @ManyToOne
  @JoinColumn(
      foreignKey =
          @ForeignKey(
              value = ConstraintMode.PROVIDER_DEFAULT,
              name = "fk_collectionaccess_collection_collections_id"))
  private Collection collection;

  private Integer accessType;
  private LocalDateTime expirationAtUtc;
}

Given database:

select id, created_by from collection;
+----+------------+
| id | created_by |
+----+------------+
| 40 |   ABC123   |
+----+------------+

select * from collection_access;
+----+-------------+----------------------------+---------------+
| id | access_type | expiration_at_utc          | collection_id |
+----+-------------+----------------------------+---------------+
|  2 |           0 | 2011-12-03 03:15:30.000000 |            40 |
|  3 |           1 | 2011-12-03 03:15:30.000000 |            40 |
+----+-------------+----------------------------+---------------+

As you guys can see, the collection 40 has 2 collection_access 2 and 3.

I want to do JPQL select a Collection that:

1. Has id value is 40 AND
2. join fetch its CollectionAccess with a condition: if (createdBy match) OR (any CollectionAccess has accessType = 1 AND expirationAtUtc <= now). Why join fetch? Because of N + 1
3. The conditions against CollectionAccess is for matching Collection, NOT for filtering out any CollectionAccess inside Collection

(JOIN FETCH prevents N + 1 queries issue):

createQuery(
      "SELECT c FROM Collection c "
          + "LEFT JOIN FETCH c.collectionAccesses ca WHERE c.id = ?1 "
          + "AND (c.createdBy = ?2 OR (ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))",
      Collection.class)
  .setParameter(1, id)
  .setParameter(2, "DUMMY DATA")
  .setParameter(3, 1)
  .setParameter(4, now)
  getSingleResult();

I expect that after executing this query, I will get a Collection have id 40 and its TWO CollectionAccess, but it give me ONLY ONE CollectionAccess which has id 3. PLEASE NOTE THAT I SET A DUMMY/WRONG DATA TO ?2

I have done some research and found this Stackoverflow topic (main idea is we will have both JOIN and JOIN FETCH).

Based on that, I changed the query to:

createQuery("SELECT c FROM Collection c LEFT JOIN c.collectionAccesses ca "
          + "LEFT JOIN FETCH c.collectionAccesses WHERE c.id = ?1 "
          + "AND (c.createdBy = ?2 OR (ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))",
      Collection.class)
  .setParameter(1, id)
  .setParameter(2, "ABC123")  //NOW I SET CORRECT DATA TO ?2
  .setParameter(3, 1)
  .setParameter(4, now)
  getSingleResult();

Now it return to me DUPLICATED that seems to be wrong!!

Then I decided to write nesting select and hope that it will work:

createQuery(
      "SELECT t FROM (SELECT c FROM Collection c "
              + "LEFT JOIN c.collectionAccesses ca WHERE c.id = ?1 "
              + "AND (c.createdBy = ?2 OR (ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))) t "
              + "JOIN FETCH t.collectionAccesses ",
      Collection.class)
  .setParameter(1, id)
  .setParameter(2, "ABC123")
  .setParameter(3, 1)
  .setParameter(4, now)
  getSingleResult();

BUT NO LUCK:

java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:143) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]

How can i archive this goal?

I want to do JPQL select a Collection that:

1. Has id value is 40 AND
2. join fetch its CollectionAccess with a condition: if (createdBy match) OR (any CollectionAccess has accessType = 1 AND expirationAtUtc <= now)
3. The conditions against CollectionAccess is for matching Collection, NOT for filtering out any CollectionAccess inside Collection

I expect that after executing this query, I will get a Collection have id 40 and its TWO CollectionAccess

Is it possible via JPQL? If not, could you suggest a solution?

Please help, thank you so much!!

========================================================
UPDATE: Use Exists like Olivier suggested works, but 2 queries generated. Could it cause performace issue?

Answer 1

You can use an EXISTS subquery to test if at least one collection access fulfills the criteria:

List<Collection> list = entityManager.createQuery("SELECT DISTINCT c FROM Collection c "
        + "LEFT JOIN FETCH c.collectionAccesses "
        + "WHERE c.id = ?1 "
        + "AND (c.createdBy = ?2 OR EXISTS (SELECT 1 FROM CollectionAccess ca WHERE ca.collection.id = c.id AND ca.accessType = ?3 AND ca.expirationAtUtc <= ?4))",
        Collection.class)
    .setParameter(1, id)
    .setParameter(2, "DUMMY DATA")
    .setParameter(3, 1)
    .setParameter(4, now)
    .getResultList();