Witajcie,
Napisałem takie zapytanie do bazy MySQL
SELECT users_sessions.session_id, users_sessions.series
FROM users_sessions
WHERE users_sessions.user_id = 8
AND users_sessions.session_id IN (
SELECT session_id
FROM sessions_history
GROUP BY sessions_history.session_id
HAVING COUNT(CASE WHEN sessions_history.action = 2 THEN 1 END) = 0
);
Staram się je napisać w JPA. Kod wygląda tak:
CriteriaBuilder criteriaBuilder = this.em.getCriteriaBuilder();
CriteriaQuery<Session> criteriaQuery = criteriaBuilder.createQuery(Session.class);
Root<Session> from = criteriaQuery.from(Session.class);
CriteriaQuery<Session> select = criteriaQuery.select(from);
Subquery<SessionHistory> subquery = criteriaQuery.subquery(SessionHistory.class);
Root<SessionHistory> fromSub = subquery.from(SessionHistory.class);
subquery.select(fromSub.get("parent"));
subquery.groupBy(fromSub.get("parent"));
Expression<Integer> exp = criteriaBuilder.<Integer>selectCase()
.when(criteriaBuilder.equal(fromSub.get("action"), action), 1)
.otherwise(0);
subquery.having(criteriaBuilder.lessThanOrEqualTo(criteriaBuilder.count(exp), 0l));
select.where(criteriaBuilder.equal(from.get("owner"), user));
select.where(criteriaBuilder.in(from.get("id")).value(subquery));
TypedQuery<Session> typedQuery = this.em.createQuery(select);
List<Session> result = typedQuery.getResultList();
Niestety w wyniku wykonania tego kodu otrzymuję pustą listę wyników. Dodam że po wykonaniu zapytania SQL na tej samej bazie otrzymuję poprawny wynik.
Encje:
Session:
@Entity
@Table(name = "users_sessions")
public class Session {
@Id
@GeneratedValue
@Column(name = "session_id")
private Integer id;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "user_id")
private User owner;
@Column(length = 64, nullable = false)
private String series;
@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name = "session_id")
private List<SessionHistory> history;
/* Gettery i settery */
}
SessionHistory:
@Entity
@Table(name = "sessions_history")
public class SessionHistory {
@Id
@GeneratedValue
@Column(name = "history_id")
private Integer id;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "session_id")
private Session parent;
@Column(name = "action")
private Integer action;
@Column(length = 64, nullable = false)
private String token;
private Date date;
/* Gettery i settery */
}
Spring 
SQL 
karolinaa