-1

I habe Entity Report with "CollectionTable" ReportUser:

@Entity class Report {

@Column private Short userId;

@Column(name = "reportUserId") @ElementCollection(fetch = FetchType.EAGER) @CollectionTable(name = "ReportUser", joinColumns = { @JoinColumn(name = "reportId", referencedColumnName = "reportId") }) private Set reportUsers = new HashSet<>();

}

I need to write following SQL using CriteraiQuery (basically I need all reports that are either created by user 1111 or userId 1111 is in that collection table:

select * from Report r join ReportUser ru on r.reportId = ru.reportId where r.userId=1111 or ru.reportUserId=1111;

Any idea how to to that?

Thank you and best regards Dalibor

dkalna
  • 55
  • 3

1 Answers1

0

ok, i've found a solution:

CriteriaBuilder cb = persistence.getCriteriaBuilder();
CriteriaQuery<Report> query = cb.createQuery(Report.class);
Root<Report> root = query.from(Report.class);

Predicate userCreated = cb.equal(root.get(Report_.rowInfo).get(RowInfo_.userCreated), getUserId());
Predicate reportUser = cb.isMember(getUserId(), root.get(Report_.reportUsers));
query.where(cb.or(userCreated, reportUser));

return persistence.getCriteriaResults(query);
dkalna
  • 55
  • 3