We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

PHQL Self join using query builder

Hi there!

So, there is a little problem with model self joining and i can't deal with it :(

I have simple hierarchy menu based on table Menu, having column parent. If current menu has parent menu then this column contains an ID of the high level menu

When i try to fetch rows with builder :

$menus = $this->modelsManager->createBuilder()
                ->columns('Menu.*, ParentMenu.*')
                ->from(array('Menu' => 'Pkcup\Models\System\Menu'))
                ->leftJoin('Pkcup\Models\System\Menu', 'ParentMenu.id = Menu.parent', 'ParentMenu')
                ->where('Menu.parent != 0')
                ->getQuery()
                ->execute();

I can see, that result SQL query doesn't have fields from Menu and ParentMenu: all fields are overwritten by ParentMenu:

SELECT `ParentMenu`.`id` AS `_ParentMenu_id`, `ParentMenu`.`name` AS `_ParentMenu_name`, ......, `ParentMenu`.`on_main` AS `_ParentMenu_on_main`
FROM `menu` AS `Menu` 
LEFT JOIN `menu` AS `ParentMenu` ON `ParentMenu`.`id` = `Menu`.`parent`  WHERE `Menu`.`parent` <> 0

If i do something like this:

->columns('Menu.*, ParentMenu.id, ParentMenu.name')

I get the following...

 SELECT `Menu`.`id` AS `_Menu_id`, `Menu`.`name` AS `_Menu_name`, `Menu`.`pos` AS `_Menu_pos`,.... `ParentMenu`.`id` AS `id`, `ParentMenu`.`name` AS `name` FROM `menu` AS `Menu` LEFT JOIN `menu` AS `ParentMenu` ON `ParentMenu`.`id` = `Menu`.`parent`  WHERE `Menu`.`parent` <> 0

and in that case another one problem: when i iterate through result set like:

foreach ($menus as $menu) {
        $menu->ParentMenu ///<----- it's and object with fields for `Menu`.`id` AS `_Menu_id`, etc
}

What am I doing wrong?...(Of course i can declare "pseudo" model for parent menu and join using it, but it doesn't seem to be a good solution..)

Thanks!



98.9k

If you don't want to get objects just select the columns you need:

->columns('Menu.id as menuId, ParentMenu.id as parentId, Menu.name as menuName, ParentMenu.name as parentName') ;


2.3k

Thanks for the answer! This way is ok but what if i want to get objects? Is it really possible or may be will be possible to do later (i mean self-joining "as is") ? Of course parent one can be loaded by "build in" relationships and lazy loading, but i don't want to do external queries...



98.9k

If you want to get objects, do the query this way:

->columns('Menu.*, ParentMenu.*') ;


2.3k
edited Oct '14

The first post was about it....it doesn't work for me...

When i build the query this way..

$menus = $this->modelsManager->createBuilder()
 ->columns('Menu.*, ParentMenu.*')
 ->from(array('Menu' => 'Pkcup\Models\System\Menu'))
 ->leftJoin('Pkcup\Models\System\Menu', 'ParentMenu.id = Menu.parent', 'ParentMenu')
 ->where('Menu.parent != 0')
 ->getQuery()
 ->execute();

I get incorrect SQL statement (without fields for Menu....only ParentMenu):

SELECT `ParentMenu`.`id` AS `_ParentMenu_id`, `ParentMenu`.`name` AS `_ParentMenu_name`, ......, `ParentMenu`.`on_main` AS `_ParentMenu_on_main`
 FROM `menu` AS `Menu` 
 LEFT JOIN `menu` AS `ParentMenu` ON `ParentMenu`.`id` = `Menu`.`parent` WHERE `Menu`.`parent` <> 0

That was the main problem and reason for opening this discussion...