CODEWITHSUNDEEP
phpmysqlormpropel
azzy81
azzy81

Reputation: 2289

Propel ORM self referencing foreign keys

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>

Upvotes: 4

Views: 2167

Answers (1)

smhg
smhg

Reputation: 2198

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.

Upvotes: 3

Related Questions