ColdFusion ORM, Hibernate - getting the most recent entry for the one-to-many field

Question

I have a custom property in constant cfc that looks like this:

property    name="last_live_request" 
        fieldtype="one-to-many" 
        cfc="Accreditation" 
        fkcolumn="pers_ky" 
        setter="false" 
        orderby="ACCR_KY desc" 
        where="status_doma_ky in (27,28) and rownum = 1"
;

The goal is to join one-to-many accreditation records and get only the very latest. The problem is that it does not work.

As in regular PL_SQL, rownum is evaluated before sorting, so I don't get the most recent record.

The solution for this in regular PL-SQL is to make a similar selection, to get the records first, and then select the top record:

    select *
    from (
        select *
        from JOU_V_REV_PEACC 
        where status_doma_ky in (27,28)
        and pers_ky = [nnn]
        order by ACCR_KY desc
    )
    where rownum = 1

So my question is: how do I achieve this result in my cfc property?

Answer 1

:

// Get the max id using the formula attribute (note, requires SQL, not HQL)
property name="LAST_LIVE_ACCR_KY" setter="false" formula="
    select max(peac.accr_ky)
    from JOU_V_REV_PEACC peac
    where peac.status_doma_ky in (27,28)
    and peac.pers_ky = PERS_KY
";

// Set up the property
property name="last_live_request" persistent="false" default="";

// Load the required Accreditation using a custom function
function getlast_live_request() {
    return entityLoadByPK("Accreditation", this.attr('LAST_LIVE_ACCR_KY'));
}

, .