Selecting a user with getCollectionGraph, extended user and user group membership.

620 Posts

Sean Kimball Reply #1, 12 years, 2 months ago

I'm trying to select a user from a particular group with their extended user data [modUser has been extended] using getCollectionGraph, though it doesn't seem to be working..

Looking at the modx schema, I see the modx_member_groups table is the one I want & it has a forein key for the modUser object:

    <object class="modUserGroupMember" table="member_groups" extends="xPDOSimpleObject">
        <field key="user_group" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="member" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="role" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="1" index="index" />
        <field key="rank" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />

        <index alias="role" name="role" primary="false" unique="false" type="BTREE">
            <column key="role" length="" collation="A" null="false" />
        </index>
        <index alias="rank" name="rank" primary="false" unique="false" type="BTREE">
            <column key="rank" length="" collation="A" null="false" />
        </index>

        <aggregate alias="UserGroupRole" class="modUserGroupRole" local="role" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="UserGroup" class="modUserGroup" local="user_group" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="User" class="modUser" local="member" foreign="id" cardinality="one" owner="foreign" />
    </object>

So I should be able to select the user like this:

$criteria = $this->modx->newQuery('ExtUser');

//$criteria->leftJoin('MemberGroups.member','ExtUser.id');

$criteria->where(array(
	'UserData.market' => $marketid,
	'ExtUser.active' => 1,
	));

$staff = $this->modx->getCollectionGraph('ExtUser', '{"Profile":{}, "UserData":{}, "modUserGroupMember": {}}', $criteria);

However, it's not working, I get an error:

No foreign key definition for parentClass: ExtUser using relation alias: modUserGroupMember

What is wrong here? How can I get a user from a particular group?

*** Not just websites, we also create signage, banners, print, trade show displays and more! ***

Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525

24,544 Posts

BobRay Reply #2, 12 years, 2 months ago

See if this gets you anything:

$criteria->where(array(
    'UserData.market' => $marketid,
    'ExtUser.active' => 1,
    'modUserGroupMember.member' => 'ExtUser.id',
    ));

Another possibility would be:

$criteria->leftJoin('modUserGroupMember.member','ExtUser.id');

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

620 Posts

Sean Kimball Reply #3, 12 years, 2 months ago

no love with either.

I even simplified it right down to:

$criteria = $this->modx->newQuery('modUser');

$staff = $this->modx->getCollectionGraph('modUser', '{"Profile":{}, "modUserGroupMember": {}}', $criteria);

removing all the extended stuff etc - still gives the no foreign key error.

So I gave up & just wrote an sql query and did it manually sad

ho-hum....

thanks anyway Bob.

Quote from: BobRay at Jul 24, 2014, 04:49 PM

See if this gets you anything:

$criteria->where(array(
    'UserData.market' => $marketid,
    'ExtUser.active' => 1,
    'modUserGroupMember.member' => 'ExtUser.id',
    ));

Another possibility would be:

$criteria->leftJoin('modUserGroupMember.member','ExtUser.id');

*** Not just websites, we also create signage, banners, print, trade show displays and more! ***

Sean Kimball CLP, CLS.
Technical Director / Sr. Developer | BigBlock Studios
._______________________________________________.
Bigblock Studios http://www.bigblockstudios.ca Web site design & development.
27-1300 King Street East. Box 167 Oshawa, Ontario L1H8J4 Canada.
phone/fax: 905-426-5525

24,544 Posts

BobRay Reply #4, 12 years, 2 months ago

On second thought, I don't think that was the right approach.

Anyway, whatever works, works. wink

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

128 Posts

Michael Engel Reply #5, 12 years, 2 months ago

MODX says that in the deffinition of ExtUser no composite or aggregate exists naming modUserGroupMember in the modUser Object there is an very similar named relation you probably wanted to join.

        <composite alias="UserGroupMembers" class="modUserGroupMember" local="id" foreign="member" cardinality="many" owner="local" />

so it should be

$staff = $this->modx->getCollectionGraph('modUser', '{"Profile":{}, "UserGroupMembers": {}}', $criteria);

With join and graph functions the alias is the way to go, not the class names. Like you already did with Profile.