Skip to main content ↑ ↓
Topic: complex/subquery not working (Read 1179 times) previous topic - next topic

complex/subquery not working

Hi there! I was trying phprad classic edition (2.7.3) and come to an issue with the complex query.

use case: I have a pages table with id, title, and parent_id (for parent-child relation) columns. In the add page, I want to put a select field for the parent_id column with custom SQL query (see bellow) that return id as value and path (page 1 / page 2) as label.

Code: [Select]
WITH RECURSIVE pages_hierarchy AS (
	SELECT
		id,
		title,
		parent_id,
		title AS path
	FROM
		pages
	WHERE
		parent_id IS NULL
		OR parent_id = 0
	UNION ALL
	SELECT
		pages.id,
		pages.title,
		pages.parent_id,
		CONCAT(pages_hierarchy.path,
			' / ',
			pages.title)
	FROM
		pages,
		pages_hierarchy
	WHERE
		pages.parent_id = pages_hierarchy.id
)
SELECT
	id as value,
	path as label
FROM
	pages_hierarchy;

problem: after saving, the custom SQL goes to the quick list (set field Datasource window) automatically and the script broke.

the SQL working fine on PHPMyAdmin (image attached).

can anyone help me? is complex query like this not supported yet?

Thanks
Kibu