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!