Given Entities:
public class Collection {
@GeneratedValue(strategy = GenerationType.IDENTITY)
private String createdBy;
@OneToMany(mappedBy = "collection")
private List<CollectionAccess> collectionAccesses;
public class CollectionAccess {
@GeneratedValue(strategy = GenerationType.IDENTITY)
value = ConstraintMode.PROVIDER_DEFAULT,
name = "fk_collectionaccess_collection_collections_id"))
private Collection collection;
private Integer accessType;
private LocalDateTime expirationAtUtc;
<code>@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;
}
</code>
@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:
<code>select id, created_by from collection;
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 |
+----+-------------+----------------------------+---------------+
<code>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 |
+----+-------------+----------------------------+---------------+
</code>
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 has id value is 40 and join fetch
its CollectionAccess
with a condition: if any CollectionAccess
has accessType = 1
AND expirationAtUtc <= now
(JOIN FETCH prevents N + 1 queries issue):
"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))",
.setParameter(2, "DUMMY DATA")
<code>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();
</code>
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:
<code>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))",
.setParameter(2, "ABC123") //NOW I SET CORRECT DATA TO ?2
<code>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();
</code>
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
Then I decided to write nesting select
and hope that it will work:
"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 ",
.setParameter(2, "ABC123")
<code>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();
</code>
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:
<code>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]
<code>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]
</code>
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 has id value is 40 and join fetch
its CollectionAccess
with a condition: if any CollectionAccess
has accessType = 1
AND expirationAtUtc <= now
.
I expect that after executing this query, I will get a Collection
have id 40 and its TWO CollectionAccess
Please help, thank you so much