Propel ORM independently references foreign keys

Question

Im using Propel ORM and very new to Propel. I need help selecting data from a table with a query that I cannot receive. Ive got a table like this (note: not the actual table, but the same main one):

+---------------------+ | ID | Page | Parent | +---------------------+ | 1 | A | 0 | | 2 | B | 0 | | 3 | C | 2 | | 4 | D | 3 | | 5 | E | 1 | | 6 | F | 0 | | 7 | G | 3 | | 8 | H | 4 | | 9 | I | 6 | | 10 | J | 5 | +---------------------+ 

This table gives me a tree-like structure when loading pages. Before using the advance, I had a class with the function "loadPages", which would nest internal pages on an array called $ nested in a class of pages that looked like this (note: not an actual function, just a close view):

 function loadPages($parent=0, $data){ $sql = "sql query here to select pages where parent = $parent"; while($results){ $pages = new Pages(); $pages->setId(blah blah); $pages->setPage(blah blah); $pages->setParent(blah blah); $innerPages = new Pages(); /* load innerpages into the nested array */ $innerPages->loadPages($pages->getId(), $pages->nested); array_push($data, $pages); return true; } } 

Basically, how can I do this with Propel? I can pull out pages with a parent value of 0 quite easily:

 $pages = PagesQuery::create() ->filterByParent(0) ->find(); 

but I need to recursively embed the internal pages in the object that it returns, and my havnt efforts are very much dependent on all the good documentation on the Propel site.

With my old Pages class, if I print r, $ data, I would get something like this (just one example using the table above.):

 Array( [0] => Pages Object ( [id] => 2 [page] => B [parent] => 0 [nested] = Array( [0] => Pages Object ( [id] => 3 [page] => C [parent] => 2 ) ) ) 

I did it, but I'm not sure if this is the best way to do this.

 function loadPages($parent=0, $siteId, &$arr){ $arr = PagesQuery::create() ->filterBySiteId($siteId) ->filterByParentId($parent) ->find(); foreach ($arr as $i => $v) { $arr[$i]->nested = ''; loadPages($v->getId(), $siteId, $arr[$i]->nested); } } $site->pages = ''; loadPages(0, $site->getId(), $site->pages); 

My scheme does not have self-determination settings, so Ive added a foreign key to the same table as it is now and ran propel again to recreate the classes. I'm still not sure how to write a propel request (Ive removed some columns from the schema to save space). Sorry the message is getting hugh now.

 <table name="pages" phpName="Pages"> <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/> <column name="userId" type="integer" required="false"/> <column name="siteId" type="integer" required="false"/> <column name="parentId" type="integer" required="false"/> <foreign-key foreignTable="users" phpName="Users" refPhpName="Pages"> <reference local="userId" foreign="id"/> </foreign-key> <foreign-key foreignTable="sites"> <reference local="siteId" foreign="id"/> </foreign-key> <foreign-key foreignTable="pages"> <reference local="parentId" foreign="id"/> </foreign-key> </table> 

Answer 1

This is not a direct answer to your question, but it is a solution nonetheless.

As you mentioned in the commentary on the question: a nested set is the answer.

At first, this may sound like a way to solve a problem, but a nested set is the result of others having similar problems. It has been well studied and is a popular way of representing trees. Its main advantage is precisely the ability to control trees with variable depth.

You note that you are new to Propel, and that switching to a nested set will be quite a bit of work, so I suggested that you have not used Propel behavior before. If so, be sure to read the Behaviors documentation section.

If you like Propel now, you'll love it later!

To solve your question: see NestedSet Behavior . As with all other behaviors, it is easy to add to your pattern. Restore your models and you will need to do a little refactoring for your code (examples will show you how).

(Note: if you have an existing database, you will need to transfer this parent link to the nested set structure. But this is basically the only problem you should have.)

An example of displaying the entire tree is available in the Using the RecursiveIterator section .