Self Join for parent_id type relationship... fails?

☆ A M B ☆
2,475 Posts

Everett Reply #1, 15 years, 8 months ago

I have a table that has a parent_id column intended to infer a hierarchy. Here’s my schema:

	<object class="ClientCategories" table="hg_clientcategories" extends="xPDOSimpleObject">
		<field key="parent_id" dbtype="int" precision="11" attributes="unsigned" phptype="integer" null="true" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="seq" dbtype="tinyint" precision="3" phptype="integer" null="true" />
		<field key="grp" dbtype="varchar" precision="16" phptype="string" null="true" />
		
		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
			<column key="id" collation="A" null="false" />
		</index>

		<aggregate alias="Parent" class="ClientCategories" local="parent_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Children" class="ClientCategories" local="id" foreign="parent_id" cardinality="many" owner="local" />
		
	</object>

But the join isn’t happening... from my snippet:

$query = $modx->newQuery('ClientCategories');
$categories = $modx->getCollectionGraph('ClientCategories','{"Children":{}}',$query);
$query->bindGraph('ClientCategories','{"Children":{}}');	
$query->prepare();
return $query->toSQL();

There’s no Join in the resulting SQL query. What am I doing wrong?

I wrote this page, and if my schema isn’t correct for this, I’d like to update the page:
http://rtfm.modx.com/display/xPDO20/More+Examples+of+xPDO+XML+Schema+Files#MoreExamplesofxPDOXMLSchemaFiles-ParentID%3ASelfJoin

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

☆ A M B ☆
3,112 Posts

goldsky Reply #2, 14 years, 3 months ago

Hi everett,
I haven't run the query, but it's just crossed in my mind that what's on doc is not correct.

<object class="Categories" table="categories" extends="xPDOObject">
    <field key="category_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
    <field key="parent_id" dbtype="int" precision="11" phptype="integer" null="true" />
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
    <field key="seq" dbtype="smallint" precision="4" phptype="integer" null="true" />
    <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
        <column key="category_id" collation="A" null="false" />
    </index>
    <aggregate alias="Parent" class="Categories" local="parent_id" foreign="category_id" cardinality="one" owner="foreign" />
    <aggregate alias="Children" class="Categories" local="category_id" foreign="parent_id" cardinality="many" owner="local" />
</object>

I think the relationship supposed to be:

    <composite alias="Parent" class="Categories" local="parent_id" foreign="category_id" cardinality="one" owner="foreign" />
    <aggregate alias="Children" class="Categories" local="category_id" foreign="parent_id" cardinality="many" owner="local" />

So when the parent is deleted, all the children will also be deleted.

What do you think?

Rico
Genius is one percent inspiration and ninety-nine percent perspiration. Thomas A. Edison
MODx is great, but knowing how to use it well makes it perfect!

www.virtudraft.com

Security, security, security! | Indonesian MODx Forum | MODx Revo's cheatsheets | MODx Evo's cheatsheets

Author of Easy 2 Gallery 1.4.x, PHPTidy, spieFeed, FileDownload R, Upload To Users CMP, Inherit Template TV, LexRating, ExerPlan, Lingua, virtuNewsletter, Grid Class Key, SmartTag, prevNext

Maintainter/contributor of Babel

Because it's hard to follow all topics on the forum, PING ME ON TWITTER @_goldsky if you need my help.

☆ A M B ☆
2,475 Posts

Everett Reply #3, 14 years, 3 months ago

Looks plausible, but it's been a year and 5 months, so my recollection is hazy... so it looks like the only thing that changes is the type of relationship for the Parent from aggregate to composite? That sounds right. I updated the docs.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/