CODEWITHSUNDEEP
javasqlhibernatehql
Peter Jurkovic
Peter Jurkovic

Reputation: 2896

Mapping result of aggregate query based on JOINs, to Hibernate property

I need get result of aggregate function to entity property. I try to use Hibernate's @Formula anotation, but she has obviously problem with JOINs. Is there any other way how to get result of these query into object properity?

Simplified 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...
    }

Values nandoCode.id and auditor.id are properties of this object;

Thanks for advices

Upvotes: 0

Views: 1157

Answers (1)

Maciej Dobrowolski
Maciej Dobrowolski

Reputation: 12122

First of all, there's no such thing as nanoCode.id nor auditor.id in this query scope.

If you are trying to access AuditorNandoCode.auditor.id inside @Formula in AuditorNandoCode's annotation you should just use column name - in this case, probably, auditor_id.

So, try this annotation:

@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)")

Upvotes: 1

Related Questions