grails - 使用子句使HQL失效

由于我之前的问题没有答案，所以我想再问一遍。

高品质

    from Agenda agr
    inner join agr.agenda a with a.employee.id = :employeeId
    left join agr.employer wgr
    left join wgr.contracts cnt with agr.date >= cnt.validFrom and agr.date < cnt.ValidUntil

我收到消息

with-clause referenced two different from-clause elements

本机SQL

left join contract cnt
    on agr.wgv_id = cnt.wgv_id
       and agr.date >= cnt.validFrom
       and agr.date < cnt.validTill

在上一个问题中，建议是使用HQL和“with”子句。这可能是一种方法还是另一种方法？有什么建议吗？

最佳答案

{已替换}

如果您希望捕获空记录，则只需要左联接，否则只需使用联接

from Agenda agr
    inner join agr.agenda a with a.employee.id = :employeeId
    left join agr.employer wgr
    left join wgr.contracts cnt
where agr.date >= :start
    and agr.date <= :end
    and ((agr.date >= cnt.validFrom and agr.date < cnt.validTill) or agr.employer is Null)

通缉

from Agenda agr
        inner join agr.agenda a with a.employee.id = :employeeId
        left join agr.employer wgr
        left join wgr.contracts cnt with agr.date >=cnt.validFrom and agr.date < cnt.valifTill
    where agr.date >= :start
        and agr.date <= :end