I have a parent Node ID and i want to get all its children and few properties of child nodes, this query is not performing for me
can someone review it and tell me there is a better way to do the same thing or can be this be improved so that it performs
WITH nodes as (
SELECT
childNode.id as id,
childNode.uuid AS uuid
FROM alf_child_assoc assoc
JOIN alf_node parentNode ON (parentNode.id = assoc.parent_node_id)
JOIN alf_node childNode ON (childNode.id = assoc.child_node_id)
WHERE parentNode.id = 13232985),
properties as (
select nodes.id, nodes.uuid, anp.string_value, anp.long_value, aq.local_name
from nodes nodes
join alf_node_properties anp on (anp.node_id = nodes.id)
join alf_qname aq on (aq.id = anp.qname_id)
where aq.local_name in ('status', 'assignedTo', 'assignedBY', 'notes')),
p_status as (
select p.id, p.string_value as status
from properties p
where p.local_name in ('status')),
p_assignedTo as (
select p.id, p.string_value as assignedTo
from properties p
where p.local_name = 'assignedTo'),
p_assignedBy as (
select p.id, p.string_value as assignedBy
from properties p
where p.local_name = 'assignedBY') ,
p_notes as (
select p.id, p.string_value as notes
from properties p
where p.local_name = 'notes')
select
status.status,
assignedTo.assignedTo,
assignedBy.assignedBY,
notes.notes
from prop_status status
join p_assignedTo assignedTo on (assignedTo.id = status.id)
join p_assignedBy assignedBy on (assignedBy.id = assignedTo.id)
join p_notes notes on (notes.id = status.id);
