将基于join的聚合查询的结果映射到Hibernate属性

Question

我需要得到聚合函数到实体属性的结果。我尝试使用Hibernate的@Formula注释，但是她在join方面有明显的问题。是否有其他方式如何获得这些查询的结果到对象属性?

简化datamodel

    @Entity 
    @Table(name = "quasar_auditor")
    class Auditor(){
        @Id
        private Long id;
        // ...
    }
    @Entity 
    @Table(name = "quasar_nando_code")
    class NandoCode{
        @Id
        private Long id;
        @ManyToOne
        @JoinColumn(name = "parent_id") 
        private NandoCode parent;
        @OneToMany(mappedBy = "parent")
        private Set<NandoCode> children;
        // ...
    }
    @Entity
    @Table(name = "quasar_auditor_has_nando_code")
    class AuditorNandoCode{
        @Id
        private Long id;
        private Auditor auditor;
        @ManyToOne(cascade = CascadeType.DETACH)
        @JoinColumn(name = "nando_code_id")
        private NandoCode nandoCode;
        private int categorySpecificTraining;
        //  ERROR: missing FROM-clause entry for table "nandocode"
        @Formula(value = "(select COALESCE(sum(anc.category_specific_training),0) from quasar_auditor_has_nando_code anc "+
                         "inner join quasar_nando_code nc ON anc.nando_code_id=nc.id "+
                         "where nc.parent_id = nandoCode.id and anc.auditor_id = auditor.id)")
        private int childrenCategorySpecificTraining;

        // getter/setters...
    }

值nandoCode.id和auditor.id为该对象的属性;

谢谢你的建议

Answer 1

首先，在这个查询范围内没有nanoCode.id和auditor.id。

如果你想在AuditorNandoCode的注释中访问@Formula中的AuditorNandoCode.auditor.id，你应该只使用列名-在这种情况下，可能是auditor_id。

那么，试试这个注释:

@Formula(value = "(select COALESCE(sum(anc.category_specific_training),0) from quasar_auditor_has_nando_code anc "+
                     "inner join quasar_nando_code nc ON anc.nando_code_id=nc.id "+
                     "where nc.parent_id = nandoCode_id and anc.auditor_id = auditor_id)")