leftJoin

183 Posts

mikrobi Reply #1, 16 years ago

Hey!

I’ve got a question regarding a xPDO query:

I’ve got a custom entity (code is simplified)

<model baseClass="xPDOObject">
	<object class="MyEntity" table="my_entity"
		extends="xPDOSimpleObject">
		<field key="user" dbtype="int" precision="11" phptype="integer"
			null="false" default="0" index="index" />
		<field key="createdOn" dbtype="datetime" phptype="datetime"
			null="false" />
		<aggregate alias="User" class="modUser" local="user"
			foreign="id" cardinality="one" owner="foreign" />
	</object>
</model>

I’d like to query all myEntity joined with the User AND the UserProfile.

I tried

$c = $modx->newQuery('MyEntity');
$c->leftJoin('modUser','User');
$c->leftJoin('modUserProfile','Profile');

but this seems not to work because there is no "Profile" aggregation defined in my schema.

I refered to http://api.modxcms.com/xpdo/om/xPDOQuery.html#leftJoin and http://api.modxcms.com/xpdo/om/xPDOQuery.html#leftJoin. But I didn’t get it to work.

Any hints?

Add-On to easily manage your multilingual sites: Babel

4,149 Posts

splittingred Reply #2, 16 years ago

Add to your myEntity schema:

<aggregate alias="Profile" class="modUserProfile" local="user" foreign="internalKey" cardinality="one" owner="foreign" />

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

183 Posts

mikrobi Reply #3, 16 years ago

Ok, so there’s no possibility to perform this join without adding a profile column to my custom table instead of joining the Profile via the user table?

Add-On to easily manage your multilingual sites: Babel

4,149 Posts

splittingred Reply #4, 16 years ago

You can, but you have to specify the join criteria:

$c->leftJoin('modUserProfile','Profile','User.id = Profile.internalKey');

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

MODX Staff
10,725 Posts

opengeek Reply #5, 16 years ago

In order to follow the existing relations automatically, you would need to use a graph, e.g.

<?php
$c = $modx->newQuery('MyEntity');
$c->bindGraph('{"User":{"Profile":{}}}');
?>

This way you don’t need to define relations to both classes or manually define the joins, because you reuse modUser’s existing relation to Profile.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

183 Posts

mikrobi Reply #6, 16 years ago

Thanks a lot!

Now I’ve got what I was looking for!

Add-On to easily manage your multilingual sites: Babel