XPDO getCollectionGraph() only return one object not the related collection

Development
1

I am running XPDO v3 outside of modx and created a schema with some related tables.
When I query the main table and try to get the related table items, it only returns the last one. Instead of all the items.

This is (part of) my schema:

 <object class="MiTeams" table="mi_teams" extends="xPDO\Om\xPDOObject">
        <field key="ID" dbtype="int" precision="20" phptype="integer" null="false" index="pk" generated="native"/>
        <field key="date" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="title" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="created_by" dbtype="tinyint" precision="4" phptype="int" null="true"/>
        <field key="created_on" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="modified_by" dbtype="tinyint" precision="4" phptype="int" null="true"/>
        <field key="modified_on" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="done" dbtype="tinyint" precision="1" phptype="int" default="0"/>

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
            <column key="ID" length="" collation="A" null="false"/>
        </index>

        <composite alias="MiTeamTask" class="MiApi\Model\MiTeamTasks" local="ID" foreign="team_id" cardinality="many" owner="local"/>
        <composite alias="MiTeamUser" class="MiApi\Model\MiTeamUsers" local="ID" foreign="team_id" cardinality="many" owner="local"/>
        <composite alias="MiTeamBus" class="MiApi\Model\MiTeamBusses" local="ID" foreign="team_id" cardinality="many" owner="local"/>

    </object>

    <object class="MiTeamTasks" table="mi_team_tasks" extends="xPDO\Om\xPDOObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="task_id" dbtype="int" phptype="int" default="0" null="false"/>
        <field key="task_type" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="contract_id" dbtype="int" precision="10" phptype="int" null="false"/>
        <field key="client" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="address" dbtype="varchar" precision="255" phptype="string" null="false"/>

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
            <column key="team_id" length="" collation="A" null="false"/>
        </index>

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>

    <object class="MiTeamUsers" table="mi_team_users" extends="xPDO\Om\xPDOObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="user_id" dbtype="tinyint" precision="100" phptype="int" default="0"/>

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
            <column key="team_id" length="" collation="A" null="false"/>
        </index>

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>

    <object class="MiTeamBusses" table="mi_team_busses" extends="xPDO\Om\xPDOObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="bus_id" dbtype="tinyint" precision="100" phptype="int" default="0"/>

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
            <column key="team_id" length="" collation="A" null="false"/>
        </index>

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>

And this is my code to get the teams and the Tasks:

$teamsClass = MiTeams::class;
        $c = $this->xpdo->newQuery($teamsClass);
        //$c->where(['date:LIKE' => $date.'%']);
        $teams = $this->xpdo->getCollectionGraph($teamsClass, '{"MiTeamTask":{}}', $c);

        foreach ($teams as $team) {
            $tasks = $team->getMany('MiTeamTask');

            foreach ($tasks as $task) {
                print_r($task->toArray());
            }
        }

This in my MiTeams table:

insert into mi_api_1.mi_teams (ID, date, title, created_by, created_on, modified_by, modified_on, done)
values  (1, '2023-01-03 10:46:30', 'test team', 22, '2023-01-03 10:46:44', null, null, 0);

I have 2 tasks in MiTeamTasks:

insert into mi_api_1.mi_team_tasks (team_id, task_id, task_type, contract_id, client, address)
values  (1, 2, 'Plaatsen', 122345, 'company one', 'street one 14'),
        (1, 4, 'Ophalen', 122346, 'berdijf 2', 'straat 2');

The result of the print_r is this:

Array
(
[team_id] => 1
[task_id] => 4
[task_type] => Ophalen
[contract_id] => 122346
[client] => berdijf 2
[address] => straat 2
)

As you can see its only the last one.

I do not know what I did wrong, but I must have missed somthing in my schema I think…
When I set the 2nd task to team_id 2 then I see the first one.

Hope someone can help me out with this problem…

2

The problem is probably this unique index in the class “MiTeamTasks”.

<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
    <column key="team_id" length="" collation="A" null="false"/>
</index>

Your primary key for this class should probably be the field “task_id” instead (or maybe a combination of “team_id” and “task_id” or a new “id” field).

3

Ok, I changed the unique and the primary both to false and now I get this:

stil only one item but with both Items in there…:

Array
(
[team_id] => 1
[task_id] => 2
[task_type] => Plaatsen
[contract_id] => 122345
[client] => company one
[address] => street one 14
[MiTeams_ID] => 1
[MiTeams_date] => 2023-01-03 10:46:30
[MiTeams_title] => test team
[MiTeams_created_by] => 22
[MiTeams_created_on] => 2023-01-03 10:46:44
[MiTeams_modified_by] =>
[MiTeams_modified_on] =>
[MiTeams_done] => 0
[MiTeamTask_team_id] => 1
[MiTeamTask_task_id] => 4
[MiTeamTask_task_type] => Ophalen
[MiTeamTask_contract_id] => 122346
[MiTeamTask_client] => berdijf 2
[MiTeamTask_address] => straat 2
)

But when I add a 3th row in the task table it prints the first and then the last:

(
[team_id] => 1
[task_id] => 2
[task_type] => Plaatsen
[contract_id] => 122345
[client] => company one
[address] => street one 14
[MiTeams_ID] => 1
[MiTeams_date] => 2023-01-03 10:46:30
[MiTeams_title] => test team
[MiTeams_created_by] => 22
[MiTeams_created_on] => 2023-01-03 10:46:44
[MiTeams_modified_by] =>
[MiTeams_modified_on] =>
[MiTeams_done] => 0
[MiTeamTask_team_id] => 1
[MiTeamTask_task_id] => 3
[MiTeamTask_task_type] => test
[MiTeamTask_contract_id] => 45367347
[MiTeamTask_client] => company 3
[MiTeamTask_address] => street 3
)

So Looks a bit closer to the answer…
but still not what I expect to get.

4

I update the model so it looks like this now:

 <object class="MiTeams" table="mi_teams" extends="xPDO\Om\xPDOObject">
        <field key="ID" dbtype="int" precision="20" phptype="integer" null="false" index="pk" generated="native"/>
        <field key="date" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="title" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="created_by" dbtype="tinyint" precision="4" phptype="int" null="true"/>
        <field key="created_on" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="modified_by" dbtype="tinyint" precision="4" phptype="int" null="true"/>
        <field key="modified_on" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="done" dbtype="tinyint" precision="1" phptype="int" default="0"/>

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

        <composite alias="MiTeamTask" class="MiApi\Model\MiTeamTasks" local="ID" foreign="team_id" cardinality="many" owner="local"/>
        <composite alias="MiTeamUser" class="MiApi\Model\MiTeamUsers" local="ID" foreign="team_id" cardinality="many" owner="local"/>
        <composite alias="MiTeamBus" class="MiApi\Model\MiTeamBusses" local="ID" foreign="team_id" cardinality="many" owner="local"/>

    </object>

    <object class="MiTeamTasks" table="mi_team_tasks" extends="MiApi\Model\MiTeams">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="task_id" dbtype="int" phptype="int" default="0" null="false"/>
        <field key="task_type" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="contract_id" dbtype="int" precision="10" phptype="int" null="false"/>
        <field key="client" dbtype="varchar" precision="255" phptype="string" null="false"/>
        <field key="address" dbtype="varchar" precision="255" phptype="string" null="false"/>

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

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>

    <object class="MiTeamUsers" table="mi_team_users" extends="xPDO\Om\xPDOObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="user_id" dbtype="tinyint" precision="100" phptype="int" default="0"/>

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

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>

    <object class="MiTeamBusses" table="mi_team_busses" extends="xPDO\Om\xPDOObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="bus_id" dbtype="tinyint" precision="100" phptype="int" default="0"/>

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

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>

    </object>
5

Why does the class “MiTeamTasks” extend “MiTeams” now?

6

It was a test, but this did nothing, changed it back now

7

Maybe you class still needs an primary key to work correctly.
Try extending xPDO\Om\xPDOSimpleObject instead. That gives you an autoincrement primary key field “id”.

<object class="MiTeamTasks" table="mi_team_tasks" extends="xPDO\Om\xPDOSimpleObject">
8

Nope no change other than the need of an extra database column.

9

When I use joins instead of graphs, te resuts is as expected, so for now I think I use that instead…

$teamsClass = MiTeams::class;
        $c = $this->xpdo->newQuery($teamsClass);
        $c->where(['date:LIKE' => $date.'%']);
        $c->leftJoin(MiTeamTasks::class, 'MiTeamTask');
        $teams = $this->xpdo->getCollection($teamsClass, $c);

        foreach ($teams as $team) {
            $tasks = $team->getMany('MiTeamTask');

            foreach ($tasks as $task) {

                print_r($task->toArray());
            }
        }

result:

Array
(
[team_id] => 1
[task_id] => 5678
[task_type] => Ophalen
[contract_id] => 1234
[client] => Client2
[address] => Adress 2
)
Array
(
[team_id] => 1
[task_id] => 1234
[task_type] => Plaatsen
[contract_id] => 56789
[client] => Client 1
[address] => Address 1
)
10

You don’t need this join.
$team->getMany('MiTeamTask'); makes a second database query.

I tested it, and without a primary key in the “MiTeamTasks” table, getCollectionGraph() indeed doesn’t work correctly. But when I add an “id” column, all works fine.

Simplified schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="MiApi\Model" baseClass="xPDO\Om\xPDOObject" platform="mysql" defaultEngine="InnoDB" version="3.0">
	<object class="MiTeams" table="mi_teams" extends="xPDO\Om\xPDOObject">
        <field key="ID" dbtype="int" precision="20" phptype="integer" null="false" index="pk" generated="native"/>
        <field key="title" dbtype="varchar" precision="255" phptype="string" null="false"/>

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
            <column key="ID" length="" collation="A" null="false"/>
        </index>

        <composite alias="MiTeamTask" class="MiApi\Model\MiTeamTasks" local="ID" foreign="team_id" cardinality="many" owner="local"/>
    </object>

    <object class="MiTeamTasks" table="mi_team_tasks" extends="xPDO\Om\xPDOSimpleObject">
        <field key="team_id" dbtype="int" phptype="int" null="false" index="index"/>
        <field key="client" dbtype="varchar" precision="255" phptype="string" null="false"/>

        <aggregate alias="MiTeam" class="MiApi\Model\MiTeams" local="team_id" foreign="ID" cardinality="one" owner="foreign"/>
    </object>
</model>

Snippet code:

$teams = $this->xpdo->getCollectionGraph(\MiApi\Model\MiTeams::class, '{"MiTeamTask":{}}');
$output = "";
foreach ($teams as $team) {
    $output .= "<h3>" . $team->get("title") . "</h3>";
    $tasks = $team->getMany('MiTeamTask');

    foreach ($tasks as $task) {
        $output .= "<li>" . $task->get("client") . "</li>";
    }
}
return $output;
11

Yes it did need an id, I added that and removed my generated files and re-generated them, now it works!!

Thank you so much for helping me, You Rock!

12

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.