At the moment I can see two problems with the current implementation:
1. Function call with a package_id which is mounted on more than one site node will fail.
2. Function call with a package_key which is mounted more than once bellow the parrent node will fail.
To solve the problem I would suggest the following two changes:
1. Change query:
Current query
<fullquery name="site_node_apm_integration::get_child_package_id.select_child_package_id">
<querytext>
select sn1.object_id
from site_nodes sn1,
apm_packages
where sn1.parent_id = (select sn2.node_id
from site_nodes sn2
where sn2.object_id = :package_id)
and sn1.object_id = apm_packages.package_id
and apm_packages.package_key = :package_key
</querytext>
</fullquery>
Suggested query
<fullquery name="site_node_apm_integration::get_child_package_id.select_child_package_id">
<querytext>
select DISTINCT sn1.object_id
from site_nodes sn1,
apm_packages
where sn1.parent_id IN (select sn2.node_id
from site_nodes sn2
where sn2.object_id = :package_id)
and sn1.object_id = apm_packages.package_id
and apm_packages.package_key = :package_key
</querytext>
</fullquery>
2. Change function body:
Replace
return [db_string select_child_package_id {} -default ""]
with
return [db_list select_child_package_id {}]
As the current implementation did not work for multiple result entries, the db_list shouldn't effect existing function calls off this function.
Many thanks
Patrick
