On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • 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 4 years, 8 months ago.]
    • Here is an example that I did recently: https://github.com/jgulledge19/MenuBuilder/blob/master/core/components/menubuilder/model/menubuilder/MenuBuilder.php look at the getBranch method, around line 389. There might be different ways to achieve this as well. My example gets a bit complicated in PHP but you hopefully will be able to follow along.