innerJoin + select returns all fields of the main object

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #1, 15 years, 2 months ago

So what I’m trying to do is get a JSON array of modUser + modUserProfile objects for my grid that only has these fields:
- id (user)
- active (user)
- fullname (profile)
- email (profile)

Should be simple enough.. but with the code below it returns all fields from the modUser object (and the two profile fields I requested). While it’s not a huge problem, it does fetch password hashes and stuff like that and it’s just plain useless. It’s not harming anything right now, but would love to know what I’m doing wrong here tongue

The toSql you see ti breaking on returns what I would expect:

SELECT modUser.id, modUser.active, Profile.fullname, Profile.email FROM `modx_users` AS `modUser` JOIN `modx_user_attributes` `Profile` ON `modUser`.`id` =  `Profile`.`internalKey

This is the processor code:

$c = $modx->newQuery('modUser');

$total = $modx->getCount('modUser',$c);

$c->select(
    array(
        'modUser.id',
        'modUser.active',
        'Profile.fullname',
        'Profile.email',
    )
);

$c->innerJoin('modUserProfile','Profile');

$c->prepare();
return $modx->error->failure($c->toSql());

$results = array();
$qr = $modx->getCollection('modUser',$c);
foreach ($qr as $r) {
    $results[] = $r->toArray();
}

$ra = array(
    'success' => true,
    'total' => $total,
    'results' => $results
);

return $modx->toJSON($ra);

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 2 months ago

xPDO/MODX uses a technique called lazy-loading when you do not include all the fields that comprise an object. Any fields marked lazy are loaded on demand when requested via the xPDOObject->get() method (which is used by xPDOObject->toArray()) by issuing additional SQL statements to fill the requested values. When you call toArray(), you are triggering these additional SQL requests and getting the values for all fields back as requested. Use get() for the specific fields you want instead, e.g.

<?php
    $results[] = $r->get(array(
        'id',
        'active',
        'fullname',
        'email'
    ));

It would really be much more efficient to just do this IMO:

<?php
$users = $modx->getCollectionGraph('modUser', '{"Profile":{}}');
$results = array();
foreach ($users as $user) {
    $results = array_merge($user->get(array('id', 'active')), $user->Profile->get(array('fullname', 'email')));
}
$ra = array(
    'success' => true,
    'total' => $total,
    'results' => $results
);
return $modx->toJSON($ra);

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

863 Posts

Lucas Reply #3, 15 years, 2 months ago

Thanks Mark for posting this question (that I’ve been wondering about for ages) and Jason for providing a great answer!

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #4, 15 years, 2 months ago

Thanks Jason!

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.