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: http://stackoverflow.com/questions/14630819/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 (menu_page_links) 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 :) -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscr...@googlegroups.com. To post to this group, send email to cake-php@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php. For more options, visit https://groups.google.com/d/optout.