CakePHP 2.x Persistent virtual fields problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

CakePHP 2.x Persistent virtual fields problem

Sam Clauw-2
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.