MODX3: Problem with getCollection and leftJoin

Development
1

Hello,

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);

with:

$c->prepare();
$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();
    
    print_r($items_array);

}

What do I have made wrong?

Thank you in advance.

bye
Chris

2

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);
3

thank you, but no effect.

a look into object array with

print_r($items);

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

4

Not if the SQL query is correct.

It has to be the toArray() function.

5

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
Orders_id?
in array print only id

:thinking:

6

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

7

amazing, yes now it runs. :smiley:

How did you come up with it?

8

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.

9

thank you very much :pray: