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

classic Classic list List threaded Threaded
4 messages Options
DJ
Reply | Threaded
Open this post in threaded view
|

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

DJ
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
Reply | Threaded
Open this post in threaded view
|

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

André Luis
I didnt really understand what you really want, but it seems that it´s a work for TreeBehavior

In TreeBehavior you have id, parent_id, lft and rght fields... lft and rght are automatically filled by the behavior... The parent_id is the ID of the parent object, them you can use $this->Model->find('threaded'); will return the structure with it´s children.

array(
    [0]=>array(
        'Model'=>array(
            [id]=>5,
            [parent_id]=>1,
            [something]=>'somevalue',
            [children]=>array(
                [0]=>array(
                    'Model'=>array(
                        [id]=>6,
                        [parent_id]=>5,
                        [something]=>'somevalue'
                    )
                )
            )
        )
    )
)

Em quinta-feira, 23 de maio de 2013 02h54min48s UTC-3, DJ escreveu:
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
DJ
Reply | Threaded
Open this post in threaded view
|

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

DJ
Thanks Andre

Unfortunately tree behaviour doesn't really work for my situation as I need the ability for a node/resource/object to have multiple parents.

I believe the problem is related to using the one model. For eample if I set up a data model like Country->State->City->Suburb->Street with join tables the recursion works fine.

On Saturday, May 25, 2013 5:52:30 AM UTC+10, André Luis wrote:
I didnt really understand what you really want, but it seems that it´s a work for TreeBehavior

In TreeBehavior you have id, parent_id, lft and rght fields... lft and rght are automatically filled by the behavior... The parent_id is the ID of the parent object, them you can use $this->Model->find('threaded'); will return the structure with it´s children.

array(
    [0]=>array(
        'Model'=>array(
            [id]=>5,
            [parent_id]=>1,
            [something]=>'somevalue',
            [children]=>array(
                [0]=>array(
                    'Model'=>array(
                        [id]=>6,
                        [parent_id]=>5,
                        [something]=>'somevalue'
                    )
                )
            )
        )
    )
)

Em quinta-feira, 23 de maio de 2013 02h54min48s UTC-3, DJ escreveu:
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
Reply | Threaded
Open this post in threaded view
|

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

André Luis
In reply to this post by DJ
Maybe, what you want is to use the TreeBehavior with hasAndBelongsToMany, isnt it?

Em quinta-feira, 23 de maio de 2013 02h54min48s UTC-3, DJ escreveu:
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.