Propel ORM self referencing foreign keys

Question

Im using Propel ORM and very new to Propel. I need some help selecting data from a table with a query I cant get right. Ive got a table like this (note: not actual table but same principal):

+---------------------+
| 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 propel I had a class with a function 'loadPages' which would nest inner pages on an array called $nested in the Pages class which looked like this (note: not actual function just a close representation):

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 parent value of 0 quite easily like so:

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

but I need to recursivly nest the inner pages to the object it returns and my efforts havnt come to much even with all the good documentation on the Propel website.

With my old Pages class if I print_r the $data I would get somethign like this (heres 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 ve got this to work but Im not sure its th best way of doing it.

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 schema did not have a self relation setups so Ive added a foreign key to the same table just now like this and re-run propel to recreate classes. Im still not sure how to write the propel query out (Ive removed several columns form the schema just to save space). Sorry the post 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>

I'm not really understand what result do you want. Can you paste the result content you would like regarding the content table example you gave us? — j0k
Hi thanks for looking. Ive added an example array to the bottom of the question which shows what my old class would produce. Its not the whole talb ejust 1 example. — azzy81
Can you paste your schema? I just wanted to know how did you define the self relation. Somethink like PagesParent? — j0k
i expect that might be my problem I dont think Ive got a self relation in my schema. You've got me thinking now. I'll go have a look and let you know. You might have just solved the problem ^^ — azzy81
Ive added the pages part of the schema to my question and yes it didnt have a self relationship so Ive added a foreign key to itself as you'll see. Just above the scema above you will see Ive added a function which Ive just created which seems to work but Im not sure thats the correct way to do it. — azzy81

smhg smhg · Accepted Answer · 2013-03-25T21:09:02

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

As you mention in a comment on the question: nested-set is the answer.

At first this may sound like a way around the problem, but nested-set is the result of others having similar issues. It is well researched and a popular way to represent trees. Its main advantage is exactly the ability to manage variable-depth trees.

You mention that you are new to Propel and that switching to nested-set would be quite a bit of work so I took the liberty to assume that you didn't use Propel's behaviors before. If that is the case, definitely read through the documentation section about Behaviors.

If you like Propel now, you will love it afterwards!

To solve your question: look at NestedSet Behavior. As with all other behaviors, it is easy to add it to your schema. Regenerate your models and you will only need to do minor refactoring to your code (the examples will show you how).

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

An example to display an entire tree is available in the Using a RecursiveIterator section.

Propel ORM self referencing foreign keys

1 Answers