Databases
StackOverflow2013
Postsdbo
POPropel ORM self referencing foreign keys
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPropel ORM self referencing foreign keys
    text
    copied!<p>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):</p> <pre><code>+---------------------+ | 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 | +---------------------+ </code></pre> <p>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):</p> <pre><code>function loadPages($parent=0, $data){ $sql = "sql query here to select pages where parent = $parent"; while($results){ $pages = new Pages(); $pages-&gt;setId(blah blah); $pages-&gt;setPage(blah blah); $pages-&gt;setParent(blah blah); $innerPages = new Pages(); /* load innerpages into the nested array */ $innerPages-&gt;loadPages($pages-&gt;getId(), $pages-&gt;nested); array_push($data, $pages); return true; } } </code></pre> <p>Basically how can I do this with Propel? I can pull out pages with parent value of 0 quite easily like so:</p> <pre><code>$pages = PagesQuery::create() -&gt;filterByParent(0) -&gt;find(); </code></pre> <p>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.</p> <p>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.):</p> <pre><code>Array( [0] =&gt; Pages Object ( [id] =&gt; 2 [page] =&gt; B [parent] =&gt; 0 [nested] = Array( [0] =&gt; Pages Object ( [id] =&gt; 3 [page] =&gt; C [parent] =&gt; 2 ) ) ) </code></pre> <p>I ve got this to work but Im not sure its th best way of doing it. </p> <pre><code>function loadPages($parent=0, $siteId, &amp;$arr){ $arr = PagesQuery::create() -&gt;filterBySiteId($siteId) -&gt;filterByParentId($parent) -&gt;find(); foreach ($arr as $i =&gt; $v) { $arr[$i]-&gt;nested = ''; loadPages($v-&gt;getId(), $siteId, $arr[$i]-&gt;nested); } } $site-&gt;pages = ''; loadPages(0, $site-&gt;getId(), $site-&gt;pages); </code></pre> <p>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.</p> <pre><code>&lt;table name="pages" phpName="Pages"&gt; &lt;column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/&gt; &lt;column name="userId" type="integer" required="false"/&gt; &lt;column name="siteId" type="integer" required="false"/&gt; &lt;column name="parentId" type="integer" required="false"/&gt; &lt;foreign-key foreignTable="users" phpName="Users" refPhpName="Pages"&gt; &lt;reference local="userId" foreign="id"/&gt; &lt;/foreign-key&gt; &lt;foreign-key foreignTable="sites"&gt; &lt;reference local="siteId" foreign="id"/&gt; &lt;/foreign-key&gt; &lt;foreign-key foreignTable="pages"&gt; &lt;reference local="parentId" foreign="id"/&gt; &lt;/foreign-key&gt; &lt;/table&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload