I need to join two tables (SQL):
Code:
SELECT d.*
FROM device d
LEFT OUTER JOIN installation_record ir1 ON (d.id = ir1.device)
LEFT OUTER JOIN installation_record ir2 ON (d.id = ir2.device AND ir1.install_date < ir2.install_date)
WHERE ir2.id IS NULL AND ir1.uninstall_date IS NULL;
Based on which a created a criteria query:
Code:
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Device> cq = cb.createQuery(Device.class);
final Root<Device> root = cq.from(Device.class);
final Join<Device, InstallationRecord> join1 = root.join(Device_.installationRecordList, JoinType.LEFT);
final Join<Device, InstallationRecord> join2 = root.join(Device_.installationRecordList, JoinType.LEFT);
join2.on(cb.lessThan(join1.get(InstallationRecord_.installDate), join2.get(InstallationRecord_.installDate)));
cq.select(root);
final List<Predicate> predicates = Lists.newArrayList();
predicates.add(cb.isNull(join2.get(InstallationRecord_.id)));
predicates.add(cb.isNull(join1.get(InstallationRecord_.uninstallDate)));
cq.where(predicates.toArray(new Predicate[] {}));
return em.createQuery(cq).getResultList();
This gives me the following exception:
org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elementsThe HQL that was generated (from error message) seems equivalent to the SQL above:
Code:
SELECT generatedAlias0 FROM Device AS generatedAlias0
LEFT JOIN generatedAlias0.installationRecordList AS generatedAlias1
LEFT JOIN generatedAlias0.installationRecordList AS generatedAlias2
WITH generatedAlias1.installDate<generatedAlias2.installDate
WHERE ( generatedAlias2.id IS NULL ) AND ( generatedAlias1.uninstallDate IS NULL )
Can anyone help me join the tables and get rid of the error?
Thanks in advance, Miha
Login
FAQ
Search