We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 3749
    • 24,544 Posts
    Thanks Bruno,

    I had tried adding the selects, but when I add any of them (or all three), no users are found. If I use only the extUser select, xPDO crashes with a maximum nesting level error.

    With all three, here's the resulting query (with no users found):

    SELECT `id`, `username`, `password`, `cachepwd`, `class_key`, `active`, `remote_key`, `remote_data`, `hash_class`, `salt`, `primary_group`, `session_stale`, `sudo`,

    `Profile`.`id` AS `Profile_id`, `Profile`.`internalKey` AS `Profile_internalKey`, `Profile`.`fullname` AS `Profile_fullname`, `Profile`.`email` AS `Profile_email`, `Profile`.`phone` AS `Profile_phone`, `Profile`.`mobilephone` AS `Profile_mobilephone`, `Profile`.`blocked` AS `Profile_blocked`, `Profile`.`blockeduntil` AS `Profile_blockeduntil`, `Profile`.`blockedafter` AS `Profile_blockedafter`, `Profile`.`logincount` AS `Profile_logincount`, `Profile`.`lastlogin` AS `Profile_lastlogin`, `Profile`.`thislogin` AS `Profile_thislogin`, `Profile`.`failedlogincount` AS `Profile_failedlogincount`, `Profile`.`sessionid` AS `Profile_sessionid`, `Profile`.`dob` AS `Profile_dob`, `Profile`.`gender` AS `Profile_gender`, `Profile`.`address` AS `Profile_address`, `Profile`.`country` AS `Profile_country`, `Profile`.`city` AS `Profile_city`, `Profile`.`state` AS `Profile_state`, `Profile`.`zip` AS `Profile_zip`, `Profile`.`fax` AS `Profile_fax`, `Profile`.`photo` AS `Profile_photo`, `Profile`.`comment` AS `Profile_comment`, `Profile`.`website` AS `Profile_website`, `Profile`.`extended` AS `Profile_extended`,

    `userData`.`id` AS `userData_id`, `userData`.`userdata_id` AS `userData_userdata_id`, `userData`.`firstName` AS `userData_firstName`, `userData`.`lastName` AS `userData_lastName`, `userData`.`title` AS `userData_title`, `userData`.`company` AS `userData_company`, `userData`.`registrationDate` AS `userData_registrationDate`, `userData`.`category1` AS `userData_category1`, `userData`.`category2` AS `userData_category2`, `userData`.`category3` AS `userData_category3`

    FROM `modx_users` AS `modUser`

    JOIN `ext_user_data` `userData` ON modUser.id = userData.userdata_id

    LEFT JOIN `modx_user_attributes` `Profile` ON `modUser`.`id` = `Profile`.`internalKey`

    ORDER BY username ASC


    Here's the schema, if that helps:

    <?xml version="1.0" encoding="UTF-8"?>
    <model package="extendeduser" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="ext_" version="1.0.0">
        <!-- extend the modUser class -->
        <object class="extUser" extends="modUser">
            <composite alias="Data" local="id" class="userData" foreign="userdata_id" cardinality="one" owner="local"/>
        </object>
        <object class="userData" table="user_data" extends="xPDOSimpleObject">
            <field key="userdata_id" dbtype="int" precision="11" phptype="integer" null="false" attributes="unsigned"/>
            <field key="firstName" dbtype="varchar" precision="50" phptype="string" null="true"/>
            <field key="lastName" dbtype="varchar" precision="50" phptype="string" null="true"/>
            <field key="title" dbtype="varchar" precision="100" phptype="string" null="true"/>
            <field key="company" dbtype="varchar" precision="100" phptype="string" null="true"/>
            <field key="registrationDate" dbtype="datetime" phptype="datetime"/>
            <field key="category1" dbtype="varchar" precision="100" phptype="string" null="true"/>
            <field key="category2" dbtype="varchar" precision="100" phptype="string" null="true"/>
            <field key="category3" dbtype="varchar" precision="100" phptype="string" null="true"/>
    
            <index alias="userdata_id" name="userdata_id" primary="false" unique="true" type="BTREE">
                <column key="userdata_id" length="" collation="A" null="false"/>
            </index>
            <aggregate alias="User" class= "modUser" local="userdata_id" foreign="id" cardinality="one" owner="foreign"/>
        </object>
    </model>
      Did I help you? Buy me a beer
      Get my Book: MODX:The Official Guide
      MODX info for everyone: http://bobsguides.com/modx.html
      My MODX Extras
      Bob's Guides is now hosted at A2 MODX Hosting
      • 4172
      • 5,888 Posts
      sorry, I forgot to add an alias for modUser (Column 'id' in field list is ambiguous).

      This was working for me here http://www.revo222.webcmsolutions.de/75/bobgetuser.html

      $packageName = 'extendeduser';
      
      $packagepath = $modx->getOption('core_path') . 'components/' . $packageName . '/';
      $modelpath = $packagepath . 'model/';
      if (is_dir($modelpath)) {
          $modx->addPackage($packageName, $modelpath, $prefix);
      }
      
      $c = $modx->newQuery('modUser');
      $c->innerJoin('userData', 'userData', array('modUser.id = userData.userdata_id'));
      $c->leftJoin('modUserProfile', 'Profile');
      
      $c->select($modx->getSelectColumns('modUser','modUser'));
      $c->select($modx->getSelectColumns('userData','userData','userData_'));
      $c->select($modx->getSelectColumns('modUserProfile','Profile','Profile_'));
      
      $c->where(array('userData.firstName' => 'Bob'));
      
      $c->sortby('username');
      
      $c->prepare(); echo $c->toSql();
      
      if ($collection = $modx->getCollection('modUser',$c)){
          foreach ($collection as $object){
              $user = $object->toArray();
                  echo '<pre>' . print_r($user,1) . '</pre>';
          }
      }
      
      return '';
      


      I think, also the migxLoopCollection - call should work then. [ed. note: Bruno17 last edited this post 12 years, 2 months ago.]
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 4172
        • 5,888 Posts
        Quote from: Bruno17 at Aug 04, 2014, 12:51 AM


        I think, also the migxLoopCollection - call should work then.

        yes, this does return the same result, as the snippet-code above:

        [[!migxLoopCollection?
          &packageName=`extendeduser`
          &classname=`modUser`
          &joins=`[
            {"alias":"userData","classname":"userData","on":"modUser.id = userData.userdata_id"},
            {"alias":"Profile"}
          ]`
          &where=`{"userData.firstName":"Bob"}`
        ]]
        
          -------------------------------

          you can buy me a beer, if you like MIGX

          http://webcmsolutions.de/migx.html

          Thanks!
          • 3749
          • 24,544 Posts
          Thanks Bruno, I'm sure that would work, but I realized that I could come at it from the opposite direction -- getting the userData object directly and using getCollectionGraph() to get everyone's related User and Profile objects.

          I added the Profile to the userData schema (User was already there), so this is actually much easier (and it doesn't care what the user class_key is -- it's fine with modUser):


          $c = $modx->newQuery('userData');
          $users = $modx->getCollectionGraph('userData', '{"Profile":{},"User":{}}', $c);
          
            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting
            • 28042 ☆ A M B ☆
            • 24,524 Posts
            Are you going to make one of your blog tutorials out of this? I'm very interested, and would appreciate further clarification, perhaps even an example, particularly of the schema involved.

            Ah! Never mind, I ran across your other post on the subject https://forums.modx.com/thread/92481/extended-modx-objects-question#dis-post-505530
              Studying MODX in the desert - http://sottwell.com
              Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
              Join the Slack Community - http://modx.org
              • 3749
              • 24,544 Posts
              I will do a blog post, but probably not for a while. I'm working on a new version of ClassExtender that implements this for users and resources.
                Did I help you? Buy me a beer
                Get my Book: MODX:The Official Guide
                MODX info for everyone: http://bobsguides.com/modx.html
                My MODX Extras
                Bob's Guides is now hosted at A2 MODX Hosting