[Solved] nested table active record

i’m new in yii.

it’s a great framework.

but i still confused with arcive record.

if i have one table like this:




CREATE TABLE category(

category_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(20) NOT NULL,

parent INT DEFAULT NULL);




INSERT INTO category

VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),

(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),

(7,'MP3 PLAYERS',6),(8,'FLASH',7),

(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);



in row, it would be

±------------±---------------------±-------+

| category_id | name | parent |

±------------±---------------------±-------+

| 1 | ELECTRONICS | NULL |

| 2 | TELEVISIONS | 1 |

| 3 | TUBE | 2 |

| 4 | LCD | 2 |

| 5 | PLASMA | 2 |

| 6 | PORTABLE ELECTRONICS | 1 |

| 7 | MP3 PLAYERS | 6 |

| 8 | FLASH | 7 |

| 9 | CD PLAYERS | 6 |

| 10 | 2 WAY RADIOS | 6 |

±------------±---------------------±-------+

with mysql, i can retrieving a full three with this query




SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4

FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

LEFT JOIN category AS t4 ON t4.parent = t3.category_id

WHERE t1.name = 'ELECTRONICS';



the result is

±------------±---------------------±-------------±------+

| lev1 | lev2 | lev3 | lev4 |

±------------±---------------------±-------------±------+

| ELECTRONICS | TELEVISIONS | TUBE | NULL |

| ELECTRONICS | TELEVISIONS | LCD | NULL |

| ELECTRONICS | TELEVISIONS | PLASMA | NULL |

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |

| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |

±------------±---------------------±-------------±------+

with active record, how can i create a query to retrieving a full of three?

please advice.

thanks

You can use width.

You should create a foreign key for the parent field, then if you generate the code of the model with Gii you will get a relation.

Using this relation you can use with, for request to select the related table too:


category::model()->findAll(array('with'=>array('parent0')))

In case that your relation is named parent0.

Now, in parent0 you can also specify an array of criteria, so you can continue like that:


category::model()->findAll(array('with'=>array('parent0'=>array('with'=>...))))

This is the most standard approach.

Anyway, of you want you can simply write your own joint statment:


category::model()->findAll(array(

      'select'=>'t.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 '

      'join'=>'

LEFT JOIN category AS t2 ON t2.parent = t.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

LEFT JOIN category AS t4 ON t4.parent = t3.category_id'


))

In you use this approach, you should add the property lev1, lev2,lev3 e lev4 to the model.

For further information about how to configure your find() statement, check CDbCriteria.

thanks for your advice…

i really appreciated.

for this case, i implemented from hierarchical Structure

Nice wiki you found, thank for sharing.