I have a somewhat complex query I'd like to translate into one of my getlist processors for a CMP I'm working on. I know how to handle the joins, but am unsure about how to (or if it's possible to) implement the subqueries in the xpdo query. Here's the plain sql I'm working with:
SELECT Users.id, active,
accesscode_id, registeredon,
email, lastlogin, logincount,
(SELECT accesscode
FROM passport_accesscodes
WHERE id=accesscode_id) AS code,
(SELECT name
FROM member_groups AS Groups
LEFT JOIN membergroup_names AS Groupnames ON Groups.user_group = Groupnames.id
WHERE Groups.member = 604 AND Groups.rank = 0) AS primarygroup,
(SELECT COUNT(DISTINCT item_id)
FROM passport_tracking
WHERE user_id = 604 AND trackingtype = 1) AS downloads
FROM passport_accesscoderegistry AS Registry
LEFT JOIN users AS Users ON Users.id = Registry.user_id
LEFT JOIN user_attributes AS Profile ON Profile.internalKey = Registry.user_id
WHERE Registry.user_id = 604
;
And, part of the getlist code:
class passportAccessCodeRegistryGetListProcessor extends modObjectGetListProcessor {
public $classKey = 'passportAccessCodeRegistry';
public $languageTopics = array('passport:default');
public $defaultSortField = 'registeredon';
public $defaultSortDirection = 'DESC';
public $objectType = 'passport.usage';
public function prepareQueryBeforeCount(xPDOQuery $c) {
$c->leftJoin('modUser','modUser','modUser.id = passportAccessCodeRegistry.user_id');
$c->leftJoin('modUserProfile','Profile','Profile.internalKey = passportAccessCodeRegistry.user_id');
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c) {
$c->select($this->modx->getSelectColumns('passportAccessCodeRegistry','passportAccessCodeRegistry'));
$c->select($this->modx->getSelectColumns('modUser','modUser','',array('id','active','primary_group')));
$c->select($this->modx->getSelectColumns('modUserProfile','Profile','',array('id','internalKey','email','lastlogin','logincount')));
return $c;
}
public function prepareRow(xPDOObject $object) {
$objectArray = $object->toArray();
}
}
The motivation for trying to get all this info back in a single query, aside from performance, is to maintain the sortability of columns displayed via modx's native grid implementation. I'd initially queried these items separately and returned the results in the $objectArray, which displayed the data fine but attempting to sort on those items resulted in a blank grid.
[ed. note: smg6511v2 last edited this post 9 years ago.]
☆ A M B ☆