We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 53118
    • 28 Posts
    Hi guys,

    I use pdoUsers + pdoPage to list the registered users with the &where=`username LIKE \"%gerard%\"` condition

    But the registered users also have extended fields (eg: color, size...)

    Extended fields is json data

    Is there a custom solution to search inside extended fields as if they were normal sql field

    Maybe there is a builtin Modx or even MySQL tool designed to deal with this ?

    I'd rather not to create a new table for the extra fields because the extended fields are very flexible: if later I want to add a new column for new users I just have to add a new html input field for the new users registration

    If I create a new table it won't be so flexible

    Thank you smiley

    This question has been answered by BobRay. See the first response.

    [ed. note: radoslav last edited this post 6 years, 6 months ago.]
    • discuss.answer
      • 3749
      • 24,544 Posts
      This could help: https://forums.modx.com/thread/92012/filter-on-extended-field-with-pdousers.

      I believe pdoTools returns the extended field as a JSON string, if the values are unique, you can search for them. You may need an OR: statement if you want to search on multiple values at the same time. [ed. note: BobRay last edited this post 6 years, 6 months ago.]
        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
        • 53118
        • 28 Posts
        Thank you BobRay !
          • 3749
          • 24,544 Posts
          Don't thank me until it works. wink
            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
            • 53118
            • 28 Posts
            yes you are right, I could'nt make it work with extended fields, I will have to create a custom table

            ClassExtender is a nice extra BobRay !
            [ed. note: radoslav last edited this post 6 years, 6 months ago.]
              • 53118
              • 28 Posts
              I just installed classExtender it's easy and it works very good

              but now I have a search engine page where we can filter the members

              Because I have many members I would like to have a pagination for the results

              I think I will customize pdoPage to work with GetExtUsers

              If someone has a better idea feel free to let me know smiley thank you smiley
                • 3749
                • 24,544 Posts
                I don't think it pdoPage will work with GetExtUsers. I could be wrong. I suspect that pdoPage requires the target snippet to use limit and offset in its queries. GetExtUsers uses limit, but not offset, though it looks easy enough to add code to respect an offset. It would go in the section around lines 60-70 in the snippet. The new code would look like this (only three lines are changed or added):


                $outerTpl = $modx->getOption('extUserOuterTpl', $sp, 'extUserOuterTpl');
                $innerTpl = $modx->getOption('extUserInnerTpl', $sp, 'extUserInnerTpl');
                $rowTpl = $modx->getOption('extUserRowTpl', $sp, 'extUserRowTpl');
                $sortBy = $modx->getOption('sortby', $sp, 'username');
                $sortDir = $modx->getOption('sortdir', $sp, 'ASC');
                $limit = $modx->getOption('limit', $sp, 'null', true);
                
                $offset = $modx->getOption('offset', $sp, 'null', true); //new
                $offset = ($offset === null) ? 0 : (int) $offset; //new
                
                $c = $modx->newQuery($userClass);
                $c->sortby($sortBy, $sortDir);
                $c->where($where);
                if ($limit !== null) {
                    $c->limit($limit, $offset); // changed
                }
                
                $users = $modx->getCollectionGraph($userClass, '{"Profile":{},"User":{}}', $c);
                
                

                Let me know if it works and I'll include it in the next release.

                  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
                  • 53118
                  • 28 Posts
                  Hi Thanks BobRay,

                  the offset and limit params work but the other functions of pdoPage do not work (the pagination itself does not work, all the placeholders are empty)

                  UPDATE:
                  I edited the GetExtUser snippet and now it works like a charm(needs more testing but I think it's ok)

                  As a memo I will post here all the changes to GetExtUser snippet, all the new lines have a //new comment:
                  $sortBy = $modx->getOption('sortby', $sp, 'username');
                  $sortDir = $modx->getOption('sortdir', $sp, 'ASC');
                  $limit = $modx->getOption('limit', $sp, 'null', true);//new
                  $offset = $modx->getOption('offset', $sp, 0, true); //new
                  $element = $modx->getOption('element', $sp, 'null', true); //new


                  $c = $modx->newQuery($userClass);
                  $c->sortby($sortBy, $sortDir);
                  $c->where($where);
                  
                  if ($element == 'GetExtUsers'){//new
                      $totalUsers = $modx->getCollectionGraph($userClass, '{"Profile":{},"User":{}}', $c);//new
                      $total = count($totalUsers); // new
                      $c->limit($limit, $offset); //new
                  }//new


                  now the last line "return $output;" has to be replaced by :
                  $modx->setPlaceholder($totalVar,$total); //new
                  
                  if (!empty($toPlaceholder)) { //new
                      $modx->setPlaceholder($toPlaceholder, $output); //new
                  } else { //new
                      return $output; //new
                  } //new


                  here is the snippet call
                  notice that &sortby=`id` did not work I assume because there are 2 id fields (in modUser and extUser) this is why it had to be changed to a field that is unique in both tables (userdata_id is the foreign key in the ext_user_data table)
                  [[!PdoPage?
                  	&element=`GetExtUsers`
                  	&toPlaceholder=`pdoPageUsers`
                   	&where=`[[!+your.search_filters]]`
                  	&sortby=`userdata_id`
                  	&sortdir=`DESC`
                  	&limit=`[[++adverts_per_page]]`
                  	&maxLimit=`[[++adverts_max_limit]]`
                  	&page=`1`
                  	&pageLimit=`10`
                  	&tplPage=`@INLINE <li><a href="[[+href]]" title="">[[+pageNo]]</a></li>`
                  	&tplPageWrapper=`@INLINE <div class="pagination"><ul class="pagination">[[+first]][[+prev]][[+pages]][[+next]][[+last]]</ul></div>`
                  	&itplPageActive=`@INLINE <li class="active"><a href="[[+href]]" title="">[[+pageNo]]</a></li>`
                  	&tplPageFirst=`@INLINE <li class="control"><a href="[[+href]]" title="">[[%adverts_firstpage]]</a></li>`
                  	&tplPageLast=`@INLINE <li class="control"><a href="[[+href]]" title="">[[%adverts_lastpage]]</a></li>`
                  	&tplPagePrev=`@INLINE <li class="control"><a href="[[+href]]" title="">«</a></li>`
                  	&tplPageNext=`@INLINE <li class="control"><a href="[[+href]]" title="">»</a></li>`
                  	&tplPageSkip=`@INLINE <li class="disabled"><span>...</span></li>`
                  	&tplPageFirstEmpty=`@INLINE <li class="disabled"><span>[[%adverts_firstpage]]</span></li>`
                  	&tplPageLastEmpty=`@INLINE <li class="disabled"><span>[[%adverts_lastpage]]</span></li>`
                  	&tplPagePrevEmpty=`@INLINE <li class="disabled"><span>«</span></li>`
                  	&tplPageNextEmpty=`@INLINE <li class="disabled"><span>»</span></li>`
                  ]]


                  now you can see the results with the following code:
                  <ul>
                  	<li>Total Users Found: [[!+page.total]]</li>
                  	<li>Pages: [[!+pageCount]]</li>
                  </ul>
                  
                  [[!+page.nav]]
                  
                  <ul>
                  
                  	[[!+pdoPageUsers]]
                  
                  </ul>
                  
                  <ul>
                  	<li>Total Users Found: [[!+page.total]]</li>
                  	<li>Pages: [[!+pageCount]]</li>
                  </ul>
                  
                  [[!+page.nav]]
                  


                  this works fine in my local modx install

                  this has not been tested with ajax [ed. note: radoslav last edited this post 6 years, 6 months ago.]
                    • 3749
                    • 24,544 Posts
                    Nice work. Thanks! smiley

                    A couple notes,

                    I think this might do the job without the "if" tests below for both limit and offset. I could be wrong:

                    $limit = $modx->getOption('limit', $sp, 0, true);//new
                    $offset = $modx->getOption('offset', $sp, 0, true); //new



                    // if ($limit !== null) {// removed
                        $c->limit($limit, $offset); //new
                    // }  // removed
                    


                    Do you envision something like a &totalUsersPlaceholder property in addition to a &toPlaceholder property?

                    I can't tell if you're actually using the $totalUsers array or just using it to get the count. If the latter, I think the count could be pulled more conveniently with:

                    $total = $modx->getCount($userClass, $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
                      • 53118
                      • 28 Posts
                      Yes you are right I will remove the if condition

                      I used getCount at first but it turns out getCount is not working properly with some queries (it always returns 0) this is why getCollectionGraph
                      https://forums.modx.com/thread/87389/xpdo-getcount-not-working-with-innerjoin-leftjoin-rightjoin-or-groupby

                      Maybe there is a more efficient methode than getCollectionGraph?

                      &totalUsersPlaceholder is not needed because pdoPage already has its own placeholder for this => [[!+page.total]]

                      I will do some more testing today and update everything