2

I have a table that contains parents and children. I want to be able to build the model so that it returns the parents and their children i.e it associates with itself.

ID Name ParentID
1  Parent  0
2  Child1  1
3  Child2  1
4  Parent2 0
5  Child3  4

I use the following SQL

SELECT
 grp2.id,
 grp2.name
FROM wp_bp_groups grp1
LEFT JOIN wp_bp_groups grp2
 ON grp2.parent_id = grp1.id
WHERE grp1.id = '$parent_id'
ORDER BY grp2.name
tereško
  • 58,060
  • 25
  • 98
  • 150
madphp
  • 1,716
  • 5
  • 31
  • 72

2 Answers2

6

You could try something like this:

<?php  
class Group extends AppModel { 

 var $name = 'Group'; 

 var $belongsTo = array( 
        'ParentGroup' => 
            array('className' => 'Group', 
                  'foreignKey' => 'parent_id' 
        ), 
     ); 

 var $hasMany = array( 
    'ChildGroup' => 
            array('className' => 'Group', 
                  'foreignKey' => 'parent_id' 
            ), 
    ); 
} 
?>
thesunneversets
  • 2,560
  • 3
  • 28
  • 46
  • Thank you! What if i want to return the groups in a better structured way, like a tree hierarchy. The answer below mentions tree behavior, but what if i don't want to change the schema. I want to mimic the tree behavior. Right now it returns all groups, including children which have empty child group arrays. I was thinking of possibly rebuilding the array inside the model, but is there a better way to do it? – madphp Mar 15 '11 at 17:28
  • found find('threaded'). That actually makes your solution redundant. – madphp Mar 15 '11 at 17:54
  • Hmm, yes, that might be a more elegant solution to your problem - didn't want to suggest it or Tree behaviour because I haven't really used them myself. Must investigate for myself sometime though! – thesunneversets Mar 15 '11 at 20:38
  • thanks for grt answer – thecodedeveloper.com Mar 07 '14 at 05:44
  • But how to fill select box with all parent_id to select from? i.e. if group gas (a,b,c) then how to make select box like this when adding record `` – RN Kushwaha Oct 26 '14 at 09:46
2

You're looking for Tree behaviour, which allows easy management of hierarchical data.

Stoosh
  • 2,408
  • 2
  • 18
  • 24