I have test this MySQL query and it worked well:
SELECT * FROM
(SELECT `category_tb`.`category_id` AS `category_id`, `category_tb`.`category_name` AS `category_name`, `category_tb`.`category_level` AS `category_level`, `category_tb`.`category_parent` AS `category_parent`
FROM `category_tb`
WHERE `category_tb`.`category_level` = 2) AS `category_level_2`
LEFT JOIN
(SELECT `category_tb`.`category_id` AS `category_parent_id`, `category_tb`.`category_name` AS `category_parent_name` FROM `category_tb`
WHERE `category_tb`.`category_level` = 1) AS category_level_1
ON `category_level_2`.`category_parent` = `category_level_1`.`category_parent_id`
And I tried to write it as PHQL
$categoryPHQL = "
SELECT * FROM
(SELECT Categories.category_id AS category_id,
Categories.category_name AS category_name,
Categories.category_level AS category_level,
Categories.category_parent AS category_parent
FROM Categories
WHERE Categories.category_level = 2) AS category_level_2
LEFT JOIN
(SELECT Categories.category_id AS category_parent_id,
Categories.category_name AS category_parent_name FROM Categories
WHERE Categories.category_level = 1) AS category_level_1
ON category_level_2.category_parent = category_level_1.category_parent_id";
But Phalcon thrown error:
Syntax error, unexpected token (, near to 'SELECT Categories.category_id AS category_id...
So I think I cannot write
SELECT * FROM
(SELECT Categories.category_id AS category_id,...
But I have no idea to fix this. Please help me to resolve this case.