How to retrieve rows from many to many relation in propel?

Question

I did not find in the documentation how to select nested properties. I need the result like this:

{
  "data": [
    {
      "Id": 3,
      "Fname": "Fname",
      "Lname": "Lname",
      "Mname": "Mname",
      "UserId": 1549,
      "Login": "manager",
      "Email": "[email protected]",
      "Password": null,
      "UserRole": [
        {
          UserRole object
        },
        {
          UserRole object
        },
        {
          UserRole object
        }
      ]
    }
  ],
  "totalCount": 1
}

But my code

$items = PanelUserQuery::create()
    ->joinWithUser()
;
$itemsCollection = $items->find();
$totalCount = $items->count();
$itemsArray = $itemsCollection ? $itemsCollection->toArray(null, null, TableMap::TYPE_PHPNAME, true) : [];
var_dump($itemsArray);

returning this array:

{
  "data": [
    {
      "Id": 3,
      "Fname": "Fname",
      "Lname": "Lname",
      "Mname": "Mname",
      "UserId": 1549,
      "Login": "manager",
      "Email": "[email protected]",
      "Password": null,
      "User": {
        "Id": 1549,
        "Login": "manager",
        "Email": "[email protected]",
        "Password": null,
        "PanelUsers": [
          "*RECURSION*"
        ]
      }
    }
  ],
  "totalCount": 1
}

My schema: User table

<table name="user">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
    <column name="login" type="varchar" size="999"/>

    <column name="email" type="varchar" size="999"/>
    <column name="password" type="varchar" size="40"/>
</table>

Role table

<table name="role">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
    <column name="name" type="varchar" size="999" required="true"/>
    <column name="guid" type="varchar" size="999" required="true"/>
    <unique>
        <unique-column name="name"/>
    </unique>
    <unique>
        <unique-column name="guid"/>
    </unique>
</table>

UserRole table

<table name="user_role" isCrossRef="true">
    <column name="user_id" type="integer" primaryKey="true"/>
    <column name="role_id" type="integer" primaryKey="true"/>
    <foreign-key foreignTable="user">
        <reference local="user_id" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="role">
        <reference local="role_id" foreign="id"/>
    </foreign-key>
    <unique>
        <unique-column name="user_id"/>
        <unique-column name="role_id"/>
    </unique>
</table>

PanelUser table

<table name="panel_user">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
    <column name="fname" type="varchar" size="999"/>
    <column name="lname" type="varchar" size="999"/>
    <column name="mname" type="varchar" size="999"/>
    <column name="user_id" type="integer" required="true" />
    <foreign-key foreignTable="user" onDelete="CASCADE">
        <reference local="user_id" foreign="id" />
    </foreign-key>
    <behavior name="delegate">
        <parameter name="to" value="user" />
    </behavior>
</table>

I don't understand how retrieve UserRole property and pass to PanelUser object. Help with understanding query methods or text some code, please

Answer 1

You don't have to flatten the entire result set just yet (->toArray()), you can do getUserRole() in a loop on $itemsCollection->UserRole.

foreach ($itemsCollection->getUserRole() as $userRole) {
    var_dump($userRole->toArray());
}