We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 37024
    • 14 Posts
    valkovdesign Reply #1, 14 years ago
    Hello,

    I'm trying to list user profiles in following way:

    Gemany
    Berlin
    Fullname1 Fullname2 Fullname3 Fullname4
    United Kingdom
    London
    Fullname5 Fullname6 Fullname7 Fullname8
    Manchester
    Fullname9 Fullname10 Fullname11 Fullname12

    United States
    New York
    Fullname13 Fullname14 Fullname15 Fullname16


    retrieving data from user_attributes table in Modx Revo. but without success.

    Is it possible to achieve such listing? All ideas are welcome and thanks in advance.
    [ed. note: valkovdesign last edited this post 14 years ago.]
      valkovdesign.com
      • 28042 ☆ A M B ☆
      • 24,524 Posts
      You would first need to fetch the headings, then the subheadings, then the users for each subheading. You might be able to do it with one query, using group_concat. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
        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
        • 37024
        • 14 Posts
        valkovdesign Reply #3, 14 years ago
        Thank you sottwell! I'll try this aproach.
          valkovdesign.com
          • 3749
          • 24,544 Posts
          You might put the users in separate user groups, if they're not already, then you can just do this:

          <?php
          $userGroup = $modx->getObject('modUserGroup', array('name' => 'Germany'));
          
          $users = $userGroup->getMany('UserGroupMembers');
          
          foreach ($users as $user) {
          
             $profile = $user->getOne('Profile);
          
             /* do display stuff here */
            $fullName = $profile->get('fullname');
            $email = $profile->get('email');
          
          }



          ------------------------------------------------------------------------------------------
          PLEASE, PLEASE specify the version of MODX you are using.
          MODX info for everyone: http://bobsguides.com/modx.html
            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
            • 37024
            • 14 Posts
            valkovdesign Reply #5, 14 years ago
            Thanks Bob, but should i manually create user groups for all possible countries? The idea is that there will be profiles from all over the world?
              valkovdesign.com
              • 28042 ☆ A M B ☆
              • 24,524 Posts
              That's why I first suggested getting all of the countries (select distinct), then the cities, then the users. There should be a way to make a single query using group_concat or something similar, then handle the results in arrays, without having to make so many queries.

              Another option is to maintain a couple of relational tables, one for a list of countries, one for a list of cities with a field for country_id, and one to link city_id with user_id. It's just a series of one-to-many relationships; one country -> many cities, and one city -> many users.

              That's getting into heavier relational database theory than I'm really comfortable with, but I suspect that xPDO would make it quite painless once it got organized properly. [ed. note: sottwell last edited this post 14 years ago.]
                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
                • 37024
                • 14 Posts
                valkovdesign Reply #7, 14 years ago
                I almost succeeded to get desired results by using GROUP_CONCAT. Echoed results directly from snippet show the list as in example above. I'll post the code when finish with it.

                UPDATE: The problem now is when listing that way I can not make a link from "Fullname" to its profile, as the links i've generated to show user profiles end with: /profile.html?userId=n (using FURLs) [ed. note: valkovdesign last edited this post 14 years ago.]
                  valkovdesign.com
                  • 16348
                  • 64 Posts
                  Can't you just fetch the internalKey as well from the UserProfile table and use that field in your link?
                    • 28042 ☆ A M B ☆
                    • 24,524 Posts
                    Yes, that can be tricky if you don't realize that the user ID becomes the "internalKey" field in the other user tables.
                      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
                      • 37024
                      • 14 Posts
                      Succeed to list data as mentioned above with foll code:
                      <?php
                      /* build the query */
                      $c = $modx->newQuery('modUserProfile');
                      $c->select(array(
                         'country',
                         'city',
                         'GROUP_CONCAT(fullname SEPARATOR "--")',
                         'GROUP_CONCAT(CAST(internalKey AS CHAR))',
                      ));
                      $c->where(array(
                       'internalKey:!=' => '1',
                       'blocked:=' => '0',
                      ));
                      $c->groupby(city);
                      $c->prepare();
                       //print $c->toSQL();  (for Debug)
                      $c->stmt->execute();
                      $checkcountry=''; //for checking dublicate countries
                      
                      while ($row = $c->stmt->fetch(PDO::FETCH_ASSOC)) {
                      $combineddata= array('city' => $row[city], 'user'=>$users=explode("--",$row['GROUP_CONCAT(fullname SEPARATOR "--")']));
                      //checking if country is already listed
                           if ($checkcountry != $row[country]){
                           echo "<strong>". $row[country] ."</strong>";
                           }
                           //looping through combined data array
                           foreach ($combineddata as $key=>$value){
                                // checking if value is array for listing users
                                if (is_array($value)){
                                //$value if array - listing users
                                    foreach ($value as $user){
                                    echo "<em>".$user."</em></br>";
                                    }
                                } else {
                                // $value is string - listing cities
                                echo $value."</br>";
                                }
                           }
                      //assigning value for current country
                      $checkcountry=$row[country];
                      }


                      Still just plain tex list echoed direcly from snippet. Is it possible to show it using a chunk?
                      [ed. note: valkovdesign last edited this post 14 years ago.]
                        valkovdesign.com