MODX3: Problem with getCollection and leftJoin


I have the following code:

$c = $modx->newQuery($ordersClass);
$c->select( $modx->getSelectColumns($ordersClass, 'Orders', 'Orders' . '_', array('id', 'order_nr', 'user_id', 'fullname') ));
$c->select( $modx->getSelectColumns('modUserProfile', 'Profile', 'Profile' . '_', array('id','photo')) );
$c->leftJoin('modUserProfile', 'Profile', 'Profile.internalKey=Orders.user_id');

$items = $modx->getCollection($ordersClass, $c);


$sql = $c->toSQL();

I can see the correct generated SQL:

SELECT `Orders`.`id` AS `Orders_id`, `Orders`.`order_nr` AS `Orders_order_nr`, `Orders`.`user_id` AS `Orders_user_id`, `Orders`.`fullname` AS `Orders_fullname`, `Profile`.`id` AS `Profile_id`, `Profile`.`photo` AS `Profile_photo` FROM `xxx_orders` AS `Orders` LEFT JOIN `xxx_user_attributes` `Profile` ON Profile.internalKey=Orders.user_id 

but if I iterate, where is no field from joined table:

foreach ($items as $item){
    $items_array = $item->toArray();


What do I have made wrong?

Thank you in advance.


I believe you have to use different parameters for the toArray function to see only the selected (and the joined) fields.

I think this should work:

$item->toArray('', false, true);

thank you, but no effect.

a look into object array with


there all fields from xxx_orders insteed of the selected an no from joined.
problem in schema declaration?

Not if the SQL query is correct.

It has to be the toArray() function.

but in the object array there is no field from joined table. :thinking:

yes, sql is correct and runs.

but why the fields in item array not like defined such as
in array print only id


Does it work if you leave the third parameter (column_prefix) empty for the “Orders” table?

amazing, yes now it runs. :smiley:

How did you come up with it?

I struggled with xPDO and Joins before. :wink:
I vaguely remembered that an ‘id’ column is always required.

Also sometimes, if the SQL-query gets too elaborated, if can be easier to ditch the xPDO abstraction and use normal PDO instead.

thank you very much :pray: