SELECT
	cms_nodes.*,
	child_node_parent_node_id,
	alias_ids,
	page_label,
	page_is_published,
	page_is_navi_element,
	page_language_iso
FROM cms_nodes
LEFT JOIN cms_pages ON
	page_node_id = IF(node_original_node_id=0,node_id,node_original_node_id)
	AND page_language_iso = :page_language_iso
LEFT JOIN (SELECT DISTINCT node_parent_node_id AS child_node_parent_node_id FROM cms_nodes WHERE node_type = 'page' AND !node_is_trashed) children
ON child_node_parent_node_id = node_id
LEFT JOIN (
	SELECT node_original_node_id AS alias_node_original_node_id, GROUP_CONCAT(node_id) AS alias_ids
	FROM cms_nodes WHERE node_original_node_id != 0 AND node_type = 'page' AND !node_is_trashed
	GROUP BY alias_node_original_node_id
) aliases
ON alias_node_original_node_id = node_id
WHERE
	node_parent_node_id = :node_parent_node_id
	AND node_type = :node_type
	AND !node_is_trashed
	ORDER BY node_ordering
