Getting the top row only from a one-to-many
14 views
Skip to first unread message
Michael Casey
Jul 4, 2016, 5:02:11 PM7/4/16
to cf-orm-dev
Hi all
I have a custom property in a persistent 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 intention is to join to the Accreditation records, which are one-to-many, and retrieve only the most recent one. The problem is that it doesn't work.
Like in ordinary PL_SQL, the rownum is being evaluated before the sort, therefore I do not get the most recent record.
The solution for this in PL-SQL is to do a sub-select like this, so that we 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?
I have a custom property in a persistent 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 intention is to join to the Accreditation records, which are one-to-many, and retrieve only the most recent one. The problem is that it doesn't work.
Like in ordinary PL_SQL, the rownum is being evaluated before the sort, therefore I do not get the most recent record.
The solution for this in PL-SQL is to do a sub-select like this, so that we 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?
Michael Casey
Jul 5, 2016, 9:14:47 AM7/5/16
to cf-orm-dev
I found a workaround for this:
// 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'));
}
Not so pretty, but effective.
Cameron Childress
Jul 5, 2016, 9:23:56 AM7/5/16
to cf-or...@googlegroups.com
You could also just do something like this:
// Load the most recent Accreditation using a custom function
public function Accreditation getLastAccreditation() {
if ( arrayLen(getlast_live_request()) ) {
return getlast_live_request()[1];
}
// don't forget to handle empty array situation below
return ????;
}
-Cameron
--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at https://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages