compute child table values in formula field of hiberntate

Question

I am facing a problem in hibernate property formula field. Not able to combine both table columns.
shift_id column belongs to parent table of job_card and duration column belongs to child table of job_card_idle_time.
but it consider both columns are belongs to parent table of job_card.

     <property
        name="utilization"
        formula="(count(shift_id)*340)-sum(duration)/(count(shift_id)*340)"
        generated="never"
        insert="false"
        update="false"
        type="float">
    </property>

With the resulting query:

    select (count(this_.shift_id)*340)-sum(**this_.duration**) /(count(this_.shift_id)*340) as y0_,
    this_.JOB_CARD_DATE as y1_ 
    from job_card this_ 
    left outer join job_card_idle_time ir1_ on this_.JOB_CARD_ID=ir1_.JOB_CARD_ID 
    where this_.JOB_CARD_DATE between ? and ? 
    group by this_.JOB_CARD_DATE 
    order by this_.JOB_CARD_DATE desc

i want it as,

    select (count(this_.shift_id)*340)-sum(**ir1_.duration**)
    /(count(this_.shift_id)*340) as y0_,
     this_.JOB_CARD_DATE as y1_ 
    from job_card this_ 
    left outer join job_card_idle_time ir1_ on this_.JOB_CARD_ID=ir1_.JOB_CARD_ID 
    where this_.JOB_CARD_DATE between ? and ? 
    group by this_.JOB_CARD_DATE 
    order by this_.JOB_CARD_DATE desc

How do i achieve it?

Answer 1

This type of query is best mapped as, well, query rather than derived property.

Formula can only contain SQL expression based on columns from current table. The only way to use columns from other tables is to write your formula as sub-select:

(SELECT count(card.shift_id) * 340 - sum(idleTime.duration) / (count(card.shift_id) * 340)
   FROM job_card card
   LEFT OUTER JOIN job_card_idle_time idleTime on card.job_card_id = idleTime.job_card_id
  WHERE card.shift_id = shift_id)

When you refer to column without an alias it will be prefixed with an alias of your main entity table during query execution. Note, however, that the above will not do what you want because you can't group within sub-select based on outside criteria. Also, depending on your database left outer join within sub-select may not be supported.