Skip to main content ↑ ↓

Show Posts

This section allows you to view all Show Posts made by this member. Note that you can only see Show Posts made in areas you currently have access to.

Messages - kibu

2
Questions / 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