Wednesday, May 22, 2013

How to do recursion more than 2 levels deep using the one table?

Hi

I am trying to set up a generic data structure that we can use to map objects, attributes and relationships. As such I have one table with a id and name, and a join table with a "parent" id and a "child" id.

Data...

mysql> select * from resources;
+----+-------+
| id | name  |
+----+-------+
|  1 | top   |
|  2 | 2nd_1 |
|  3 | 3rd_1 |
|  4 | 2nd_2 |
|  5 | 3rd_2 |
|  6 | 4th   |
|  7 | 5th   |
+----+-------+

mysql> select * from resources_resources;
+----+---------------+---------------+
| id | a_resource_id | b_resource_id |
+----+---------------+---------------+
|  1 | 1             | 2             |
|  2 | 2             | 3             |
|  3 | 1             | 4             |
|  4 | 4             | 5             |
|  5 | 3             | 6             |
|  6 | 5             | 6             |
|  7 | 6             | 7             |
+----+---------------+---------------+


Below is my model definition.

// Model/Resource.php

        public $hasAndBelongsToMany = array(
                'Children' => array(
                        'className' => 'Resource',
                        'joinTable' => 'resources_resources',
                        'foreignKey' => 'a_resource_id',
                        'associationForeignKey' => 'b_resource_id',
                        'unique' => 'keepExisting',
                        'conditions' => '',
                        'fields' => '',
                        'order' => '',
                        'limit' => '',
                        'offset' => '',
                        'finderQuery' => '',
                        'deleteQuery' => '',
                        'insertQuery' => ''
                ),
        );

Controller

// Controller/ResourceController.php

        public function view($id = null) {
                if (!$this->Resource->exists($id)) {
                        throw new NotFoundException(__('Invalid resource'));
                }
                $options = array('conditions' => array('Resource.' . $this->Resource->primaryKey => $id),'recursive' => 5);
                $data = $this->Resource->find('first', $options);
                debug($data);

This outputs as below...

array(  	'Resource' => array(  		'id' => '1',  		'name' => 'top'  	),  	'Children' => array(  		(int) 0 => array(  			'id' => '2',  			'name' => '2nd_1',  			(int) 0 => array(  				'id' => '3',  				'name' => '3rd_1',  				'ResourcesResource' => array(  					'id' => '2',  					'a_resource_id' => '2',  					'b_resource_id' => '3'  				)  			),  			'ResourcesResource' => array(  				'id' => '1',  				'a_resource_id' => '1',  				'b_resource_id' => '2'  			)  		),  		(int) 1 => array(  			'id' => '4',  			'name' => '2nd_2',  			(int) 0 => array(  				'id' => '5',  				'name' => '3rd_2',  				'ResourcesResource' => array(  					'id' => '4',  					'a_resource_id' => '4',  					'b_resource_id' => '5'  				)  			),  			'ResourcesResource' => array(  				'id' => '3',  				'a_resource_id' => '1',  				'b_resource_id' => '4'  			)  		)  	)  )

Is it because I'm using the one table? (I'm about to prove that for myself). I have tried things like using contain and specifying multiple levels as below with same result as below.

$options = array('conditions' => array('Resource.' . $this->Resource->primaryKey => $id),'contain' => array( 'Children' => array( 'Children' => array ( 'Children'))));

Is this 2 levels deep recursion by design? Can anybody suggest a workaround?

Thanks!

--
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+unsubscribe@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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

No comments: