getCollectionGraph nested related objects only returning single row

[davidpede]

I'm trying access nested related objects using getCollectionGraph().

My schema:

<object class="TestClass" table="test_class" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
    <field key="description" dbtype="mediumtext" phptype="string" null="true" />

    <composite alias="actions" class="MyActions" local="id" foreign="test_id" cardinality="many" owner="local" />
</object>

<object class="MyActions" table="actions_table" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
    <field key="description" dbtype="mediumtext" phptype="string" null="true" />
    <field key="test_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" index="index" />

    <composite alias="activities" class="MyActivities" local="id" foreign="action_id" cardinality="many" owner="local" />
</object>

<object class="MyActivities" table="activities_table" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
    <field key="description" dbtype="mediumtext" phptype="string" null="true" />
    <field key="action_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" index="index" />
</object>

Code:

$collection = $this->modx->getCollectionGraph('TestClass','{"actions":{"activities":{}}}',array('id' => 5));

foreach ($collection as $row) {
    foreach ($row->actions as $action) {
        //do something
        foreach ($action->activities as $activity) {
            //do something
        }
    }
}

There are multiple related actions and they are all fetched correctly. However with the nested related activities, only one row is returned even though there are multiple rows per action. The row with the highest id is returned only.

getMany() works as expected with multiple activities returned:

$collection = $this->modx->getCollectionGraph('TestClass','{"actions":{}}',array('id' => 5));

foreach ($collection as $row) {
    foreach ($row->actions as $action) {
        //do something
        $activities = $action->getMany('activities');
        foreach ($activities as $activity) {
            //do something
        }
    }
}

Is this a bug anyone else can replicate or am I making a mistake somewhere?

Thanks

[davidpede]

I am using MODX 2.8.1-pl and xPDO 2.8.1-pl

[wshawn]

This isn't a bug.

I have done many of these. Of note: I typically do both sides of the relationship (i.e. aggregate with owner foreign) . Also, it does not know WHICH id=5 you are discussing. Look at the format for $xpdo->newQuery. You might need to use 'TestClass.id' => 5

[davidpede]

@wshawn thanks, I've adjusted my schema to add the aggregate:

<object class="MyActivities" table="activities_table" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="255" phptype="string" null="true" />
    <field key="description" dbtype="mediumtext" phptype="string" null="true" />
    <field key="action_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" index="index" />

    <aggregate alias="action" class="MyActions" local="action_id" foreign="id" cardinality="one" owner="foreign" />
</object>

And the query like:

$collection = $this->modx->getCollectionGraph('TestClass','{"actions":{"activities":{}}}',array('TestClass.id' => 5));

foreach ($collection as $row) {
    foreach ($row->actions as $action) {
        //do something
        $activities = $action->getMany('activities');
        foreach ($activities as $activity) {
            //do something
        }
    }
}

But still only get one activity per action fetched. When you say it's not a bug, is this correct behaviour or a limitation of getCollectionGraph()? I was expecting that a cardinality="many" relationship should return all the related rows not one.

EDIT: after more searching it seems this has come up before: #118 with a pr addressing it #123. @opengeek is it likely #123 will be looked at in near future? Thanks

[wshawn]

Your reference is 5 years ago.

There are two things you can do to find the criteria break. First, simplify your get collection to the first relation until it is working and subsequently expand it for the next relation and so forth.

You can try a simple $TestClass->getMany("actions"). // Aliases are usually capitalized as they are classes and index=index doesn't do anything if I remember correctly.

Once you have all of the relations showing up add the criteria. And see if only the ones with TestClass.id=5 is showing.

Loop through those and grab one to get its relation. If that all is working then your criteria statement is incorrect. I always had to opt for an external criteria to ensure it was working.

Now as to the criteria. There were several instances when I had to use the shorthand form of array for it to work: ['TestClass.id' => 5] instead of array('TestClass.id' => 5). I have no idea why and never wanted to invest more time in discovering the cause.

I might also add, that it is typically unnecessary to have a graph that extensive, though I have rarely also had to. An example of a criteria for such a graph:

$criteria = $this->xpdo->newQuery ( 'cbClient' );
		$criteria->where ( array (
				'companyProgramId' => 1,
				'isSuspended' => false,
				'isActive' => true,
				'teamLeaderId' => $this->getPrimaryKey (),
				'teamLeaderCommissionDisabled' => false 
		) );

For a three year old MODX3 example:

 /**
     * Provides a hydrated response of queries.
     * @param int $limit The number of queries to return.
     * @param int $offset The number of queries to skip before retrieval.
     * @param string $direction The direction the queries should be returned: ASC or DESC.
     * @return array A collection of \SanityLLC\\SanitySwarm\\Database\\savfClientQuery
     */
    final public function getQueries(int $limit = 0, int $offset = 0, string $direction = 'DESC'): array
    {
        $criteria = $this->xpdo->newQuery('SanityLLC\SanitySwarm\Database\savfClientQuery');
        $criteria->where(['savfClientQuery.clientId' => $this->userId]);
        $criteria = $this->setQueryLimit($criteria, $limit, $offset, $direction);
        $criteria = $this->setQuerySortDirection($criteria, 'Query.addedOn', $direction);
      
        return $this->xpdo->getCollectionGraph('SanityLLC\SanitySwarm\Database\savfClientQuery', '{"Swarms":{"Query":{"Client":{}}}}', $criteria, false);
    }

In short, I would be looking at your criteria and relations. I also noticed, looking through my code base that I opt to use getObjectGraph much more frequently than getCollectionGraph as I am typically working with a specific object and not a collection of them.

Read this first

Wow. After rereading your post you are getting what you are asking for. You are asking for a TestClass with an ID of 5. When I suspect you are wanting Activities or Actions. You get a collection of the object class named in the first argument of getCollectionGraph. To get a collection of those you must rewrite the argument to the object you are wanting and build your relation back from there.

In essence write the graph backwards as the content you are actually interacting with should be closer to the outside of the graph. In most cases I have found that I don't need the criteria relations only what they point to.

You can try

$x = array_pop($collection);
var_dump(array_pop($x->toArray()));

I suspect you will find a single object there with all of your data under it.

You also do not need to the getMany as you already have them in the graph.

Here is a graph written backwards, as you may see the Client is the current client and as such does not need defined in the criteria. In fact, almost no definitions are needed as it simply relies on the relationships.

 /**
     * Retrieves the reports associated with the client.
     * @param int $limit The number of records to return.
     * @param int $offset The number of records to skip before retrieval.
     * @param string $direction The direction the records should be returned: ASC or DESC.
     * @return array A collection of \SanityLLC\SanitySwarm\Database\savfReport
     */
    final public function getReports(int $limit = 0, int $offset = 0, string $direction = 'DESC'): array
    {
        $criteria = $this->xpdo->newQuery('\SanityLLC\SanitySwarm\Database\savfReport');
        $criteria = $this->setQueryLimit($criteria, $limit, $offset, $direction);
        $x = $this->xpdo->getCollectionGraph('\SanityLLC\SanitySwarm\Database\savfReport', '{"Origin": {"Query":{"Client":{}}} }', $criteria);
        return $x ?? array();
    }

I hope this helps.