I have a brainteaser about virtual fields on associated models. I know it has it's limitations as you can read here: http://book.cakephp.org/2.0/en/models/virtual-fields.html#limitations-of-virtualfields. But in my specific case, I can't apply the theory of passing the virtualField property from one model to another (even when I read this topic: Using virtual fields in cakePHP 2.x).
I'll try to explane my case as clear as possible.
Database info
I have 3 tables to make a navigation on my website:
menus
- id
- name
pages
- id
- title
- content
menu_page_links
- id
- menu_id
- page_id
- title
- lft
- rgt
- plugin
- controller
- action
One page can be added to multiple menus, that's why I've made a link table (MenuPageLink) between menus and pages.
Application info
Menu model
class Menu extends AppModel
{
public $hasMany = array(
'MenuPageLink' => array(
'className' => 'MenuPageLink',
'foreignKey' => 'menu_id'
)
);
}
Page model
class Page extends AppModel
{
public $hasMany = array(
'MenuPageLink' => array(
'className' => 'MenuPageLink',
'foreignKey' => 'page_id'
)
);
}
MenuPageLink model
class MenuPageLink extends AppModel
{
public $belongsTo = array(
'Menu',
'Page'
);
// to calculate the depth of every menu page link
public $virtualFields = array(
'depth' => 'COUNT(MenuPageLink.title) - 1'
);
}
AppController
Now in my application, I want to load a specific menu on every page, e.g. the "Primary menu" from the "menus" table.
So in AppController.php, I referred to the tree needed models ('Menu', 'Page' and 'MenuPageLink') as following:
public $uses = array(
'Menu',
'Page',
'MenuPageLink'
);
Then I get my menu pages that belongs to my specific menu with id "2":
$menu = $this->MenuPageLink->find('all', array(
'fields' => array(
'MenuPageLink.id',
'MenuPageLink.title',
'MenuPageLink.plugin',
'MenuPageLink.controller',
'MenuPageLink.action',
'MenuPageLink.depth'
),
'joins' => array(
array(
'table' => $this->MenuPageLink->table,
'alias' => 'Parent',
'type' => 'LEFT',
'conditions' => array(
'MenuPageLink.lft BETWEEN Parent.lft AND Parent.rgt',
'MenuPageLink.menu_id' => 1
)
)
),
'conditions' => array(
'MenuPageLink.menu_id' => 2,
'MenuPageLink.lft >' => 1,
'MenuPageLink.deleted' => null
),
'group' => 'MenuPageLink.id',
'order' => array(
'MenuPageLink.lft ASC'
)
));
This is my result when I debug my $menu variable:
array(
(int) 0 => array( 'MenuPageLink' => array( 'id' => '36', 'title' => 'Home', 'plugin' => '', 'controller' => 'home', 'action' => 'index', 'depth' => '1' ) ),
(int) 1 => array( 'MenuPageLink' => array( 'id' => '39', 'title' => 'News', 'plugin' => '', 'controller' => 'news_articles', 'action' => 'index', 'depth' => '1' ) ),
(int) 2 => array( 'MenuPageLink' => array( 'id' => '37', 'title' => 'About the park', 'plugin' => '', 'controller' => 'pages', 'action' => 'view', 'depth' => '1' ) ),
(int) 3 => array( 'MenuPageLink' => array( 'id' => '41', 'title' => 'Attractions', 'plugin' => '', 'controller' => 'attractions', 'action' => 'index', 'depth' => '2' ) ),
(int) 4 => array( 'MenuPageLink' => array( 'id' => '42', 'title' => 'Animals', 'plugin' => '', 'controller' => 'animals', 'action' => 'index', 'depth' => '2' ) ),
(int) 5 => array( 'MenuPageLink' => array( 'id' => '43', 'title' => 'Events', 'plugin' => '', 'controller' => 'events', 'action' => 'index', 'depth' => '2' ) ),
(int) 6 => array( 'MenuPageLink' => array( 'id' => '44', 'title' => 'Shows', 'plugin' => '', 'controller' => 'shows', 'action' => 'index', 'depth' => '2' ) ),
(int) 7 => array( 'MenuPageLink' => array( 'id' => '45', 'title' => 'History', 'plugin' => '', 'controller' => 'pages', 'action' => 'history', 'depth' => '2' ) ),
(int) 8 => array( 'MenuPageLink' => array( 'id' => '38', 'title' => 'Info', 'plugin' => '', 'controller' => 'pages', 'action' => 'view2', 'depth' => '1' ) ),
(int) 9 => array( 'MenuPageLink' => array( 'id' => '40', 'title' => 'Media', 'plugin' => '', 'controller' => 'pages', 'action' => 'media', 'depth' => '1' ) )
)
So far so good!
What I'm trying
And here comes my problem. I only want to select the pages where depth = 1. I tried to make a new 'conditions' parameter:
$menu = $this->MenuPageLink->find('all', array(
'fields' => array(
'MenuPageLink.id',
'MenuPageLink.title',
'MenuPageLink.plugin',
'MenuPageLink.controller',
'MenuPageLink.action',
'MenuPageLink.depth'
),
'joins' => array(
array(
'table' => $this->MenuPageLink->table,
'alias' => 'Parent',
'type' => 'LEFT',
'conditions' => array(
'MenuPageLink.lft BETWEEN Parent.lft AND Parent.rgt',
'MenuPageLink.menu_id' => 1
)
)
),
'conditions' => array(
'MenuPageLink.menu_id' => 2,
'MenuPageLink.lft >' => 1,
'MenuPageLink.deleted' => null,
'MenuPageLink.depth' => 1 // <-- new rule in my query
),
'group' => 'MenuPageLink.id',
'order' => array(
'MenuPageLink.lft ASC'
)
));
and after a lot of headaches to make this work, I even tried to use the Model::query() method:
$this->MenuPageLink->query(
"SELECT
`MenuPageLink`.`id`
, `MenuPageLink`.`title`
, `MenuPageLink`.`lft`
, `MenuPageLink`.`rgt`
, `MenuPageLink`.`show`
, `MenuPageLink`.`deleted`
, (COUNT(`MenuPageLink`.`title`) - 1) AS `MenuPageLink`.`depth`
FROM
`blwfun`.`menu_page_links` AS `MenuPageLink`
LEFT JOIN
`blwfun`.`menus` AS `Menu`
ON
(`MenuPageLink`.`menu_id` = `Menu`.`id`)
LEFT JOIN
`blwfun`.`pages` AS `Page`
ON
(`MenuPageLink`.`page_id` = `Page`.`id`)
LEFT JOIN
`blwfun`.`menu_page_links` AS `Parent`
ON
(`MenuPageLink`.`lft` BETWEEN `Parent`.`lft` AND `Parent`.`rgt` AND `MenuPageLink`.`menu_id` = 1)
WHERE
`Parent`.`menu_id` = 1 AND `MenuPageLink`.`lft` > 1 AND `MenuPageLink`.`deleted` IS NULL AND `MenuPageLink`.`depth` = 1
GROUP BY
`MenuPageLink`.`id`
ORDER BY
`MenuPageLink`.`lft` ASC"
));
I keep getting the sql error, but I don't know how to solve it. As I said, I don't know how to apply the theory in de CakePHP docs (http://book.cakephp.org/2.0/en/models/virtual-fields.html#limitations-of-virtualfields) to my specific case. Is there anybody who can help me out with this please?
You would be a hero to me :)
