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

    I have 2 questions, related to the migxLoopCollection snippet:
    1. Can I add groupby parameter to the result (without changing the snippet code)?
    2. Can I limit the returned fields?

    The first thing I curtrenty solved by adding a parameter (groupby) to the snippet
    and small code

    if (!empty($groupby)) {
    $c->groupby($groupby);
    }

    (This is simple change, because I currently need one column to group on and don't care about sorting)

    Is there better approach to this?
    How can I solve my second need?
    Have in mind, I'm not very familiar with xPDO and with MIGX (MIGXdb) internals

    Regards,
      • 4172
      • 5,888 Posts
      does it work with this one?

      use it with &limit=`5`

      <?php
      
      $tpl = $modx->getOption('tpl', $scriptProperties, '');
      $limit = $modx->getOption('limit', $scriptProperties, '0');
      $offset = $modx->getOption('offset', $scriptProperties, 0);
      $totalVar = $modx->getOption('totalVar', $scriptProperties, 'total');
      
      $where = $modx->getOption('where', $scriptProperties, '');
      $where = !empty($where) ? $modx->fromJSON($where) : array();
      $queries = $modx->getOption('queries', $scriptProperties, '');
      $queries = !empty($queries) ? $modx->fromJSON($queries) : array();
      $sortConfig = $modx->getOption('sortConfig', $scriptProperties, '');
      $sortConfig = !empty($sortConfig) ? $modx->fromJSON($sortConfig) : array();
      $configs = $modx->getOption('configs', $scriptProperties, '');
      $configs = explode(',', $configs);
      $toSeparatePlaceholders = $modx->getOption('toSeparatePlaceholders', $scriptProperties, false);
      $toPlaceholder = $modx->getOption('toPlaceholder', $scriptProperties, false);
      $outputSeparator = $modx->getOption('outputSeparator', $scriptProperties, '');
      //$placeholdersKeyField = $modx->getOption('placeholdersKeyField', $scriptProperties, 'MIGX_id');
      $placeholdersKeyField = $modx->getOption('placeholdersKeyField', $scriptProperties, 'id');
      $toJsonPlaceholder = $modx->getOption('toJsonPlaceholder', $scriptProperties, false);
      $jsonVarKey = $modx->getOption('jsonVarKey', $scriptProperties, 'migx_outputvalue');
      $prefix = isset($scriptProperties['prefix']) ? $scriptProperties['prefix'] : null;
      
      $packageName = $scriptProperties['packageName'];
      $joins = $modx->getOption('joins', $scriptProperties, '');
      $joins = !empty($joins) ? $modx->fromJson($joins) : false;
      
      $selectfields = $modx->getOption('selectfields', $scriptProperties, '');
      $selectfields = !empty($selectfields) ? explode(',', $selectfields) : null;
      
      $packagepath = $modx->getOption('core_path') . 'components/' . $packageName . '/';
      $modelpath = $packagepath . 'model/';
      
      $modx->addPackage($packageName, $modelpath, $prefix);
      $classname = $scriptProperties['classname'];
      
      $base_path = $modx->getOption('base_path', null, MODX_BASE_PATH);
      $base_url = $modx->getOption('base_url', null, MODX_BASE_URL);
      
      $migx = $modx->getService('migx', 'Migx', $modx->getOption('migx.core_path', null, $modx->getOption('core_path') . 'components/migx/') . 'model/migx/', $scriptProperties);
      if (!($migx instanceof Migx))
          return '';
      //$modx->migx = &$migx;
      $defaultcontext = 'web';
      $migx->working_context = isset($modx->resource) ? $modx->resource->get('context_key') : $defaultcontext;
      
      $properties = array();
      foreach ($scriptProperties as $property => $value) {
          $properties['property.' . $property] = $value;
      }
      
      $idx = 0;
      $output = array();
      $c = $modx->newQuery($classname);
      $c->select($modx->getSelectColumns($classname, $classname, '', $selectfields));
      
      if ($joins) {
          $migx->prepareJoins($classname, $joins, $c);
      }
      
      if (!empty($where)) {
          $c->where($where);
      }
      
      if (!empty($queries)) {
          foreach ($queries as $key => $query) {
              $c->where($query, $key);
          }
      
      }
      
      if (!empty($groupby)) {
          $c->groupby($groupby);
      }
      
      //set "total" placeholder for getPage
      $total = $modx->getCount($classname, $c);
      $modx->setPlaceholder($totalVar, $total);
      
      if (is_array($sortConfig)) {
          foreach ($sortConfig as $sort) {
              $sortby = $sort['sortby'];
              $sortdir = isset($sort['sortdir']) ? $sort['sortdir'] : 'ASC';
              $c->sortby($sortby, $sortdir);
          }
      }
      
      //&limit, &offset
      if (!empty($limit)) {
          $c->limit($limit, $offset);
      }
      
      //$c->prepare();echo $c->toSql();
      if ($collection = $modx->getCollection($classname, $c)) {
          foreach ($collection as $object) {
              $fields = $object->toArray('', false, true);
              if ($toJsonPlaceholder) {
                  $output[] = $fields;
              } else {
                  $fields['_alt'] = $idx % 2;
                  $idx++;
                  $fields['_first'] = $idx == 1 ? true : '';
                  $fields['_last'] = $idx == $limit ? true : '';
                  $fields['idx'] = $idx;
                  $rowtpl = $tpl;
                  //get changing tpls from field
                  if (substr($tpl, 0, 7) == "@FIELD:") {
                      $tplField = substr($tpl, 7);
                      $rowtpl = $fields[$tplField];
                  }
      
                  if (!isset($template[$rowtpl])) {
                      if (substr($rowtpl, 0, 6) == "@FILE:") {
                          $template[$rowtpl] = file_get_contents($modx->config['base_path'] . substr($rowtpl, 6));
                      } elseif (substr($rowtpl, 0, 6) == "@CODE:") {
                          $template[$rowtpl] = substr($tpl, 6);
                      } elseif ($chunk = $modx->getObject('modChunk', array('name' => $rowtpl), true)) {
                          $template[$rowtpl] = $chunk->getContent();
                      } else {
                          $template[$rowtpl] = false;
                      }
                  }
      
                  $fields = array_merge($fields, $properties);
      
                  if ($template[$rowtpl]) {
                      $chunk = $modx->newObject('modChunk');
                      $chunk->setCacheable(false);
                      $chunk->setContent($template[$rowtpl]);
                      if (!empty($placeholdersKeyField) && isset($fields[$placeholdersKeyField])) {
                          $output[$fields[$placeholdersKeyField]] = $chunk->process($fields);
                      } else {
                          $output[] = $chunk->process($fields);
                      }
                  } else {
                      if (!empty($placeholdersKeyField)) {
                          $output[$fields[$placeholdersKeyField]] = '<pre>' . print_r($fields, 1) . '</pre>';
                      } else {
                          $output[] = '<pre>' . print_r($fields, 1) . '</pre>';
                      }
                  }
              }
      
      
          }
      }
      
      
      if ($toJsonPlaceholder) {
          $modx->setPlaceholder($toJsonPlaceholder, $modx->toJson($output));
          return '';
      }
      
      if (!empty($toSeparatePlaceholders)) {
          $modx->toPlaceholders($output, $toSeparatePlaceholders);
          return '';
      }
      /*
      if (!empty($outerTpl))
      $o = parseTpl($outerTpl, array('output'=>implode($outputSeparator, $output)));
      else 
      */
      if (is_array($output)) {
          $o = implode($outputSeparator, $output);
      } else {
          $o = $output;
      }
      
      if (!empty($toPlaceholder)) {
          $modx->setPlaceholder($toPlaceholder, $o);
          return '';
      }
      
      return $o;
      
      
      
      [ed. note: Bruno17 last edited this post 11 years, 3 months ago.]
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 6848
        • 52 Posts
        Hi,
        Sorry my question was not clear. When I asked for limit, I meant to limit the returned fields, not records
        (like the difference between "SELECT * from..." and "SELECT field1, field2 from...")

        probably some change in this???:
        $c->select($modx->getSelectColumns($classname,$classname));

        But actually this new limit parameter is better than the limit which was before.
        I think you can leave it this way in the new versions.
          • 6848
          • 52 Posts
          After some xPDO education smiley I think there is no positive answer to my previous question.
          The getCollection call seems to have no possibilities to limit the number of fields it returns.
          You are using the $c object only as a criteria to the call. its "SELECT" part is not relevant.
          And as the name and purpose of the snippet is LoopCollection, I think it should stay as it is now.

          For returning similar results, but for specific fields, probably different snippet should be created, which executes $c object and returns the results in a suitable way/format.

            • 4172
            • 5,888 Posts
            There is now a new property &selectfields

            Use a commaseperated list of fields to select only the fields you want to get.
            Don't forget the primary-key-field in this list!
              -------------------------------

              you can buy me a beer, if you like MIGX

              http://webcmsolutions.de/migx.html

              Thanks!
              • 6848
              • 52 Posts
              Quote from: Bruno17 at Jan 06, 2013, 07:19 AM
              There is now a new property &selectfields

              Use a commaseperated list of fields to select only the fields you want to get.
              Don't forget the primary-key-field in this list!

              I tried exactly this yesterday and it is technically working, but getCollection call returns all class members (fields).
              The created select statement is OK, but the $c object is used only as an additional criteria (WHERE, SORT BY, GROUP BY..) for the call.
              The SELECT part is not considered.

              You can see this by printing the returned collection. It has all the fields from the table (object) and their values.

              That's why in my previous post I wrote that the snippet (according to its name and purpose) doesn't need this functionality.
              In addition, there is not so big problem with this. The fields limit is mainly speed (performance) issues and only if the fields content is too big.
              And if one needs fields limitation, the easiest way is to duplicate the class and create one with reduced fields.

              Other approach would be to create similar snippet, but using something like $c->execute ..... instead of getCollection

              Thank you for the perfect component. This is one of the components you must have if you want to create something more than a "web site"

              Regards,
                • 4172
                • 5,888 Posts
                what you need to get only the selectfields with toArray() is this:

                $object->toArray('', false, true);


                what I had modified, too

                see also:
                http://rtfm.modx.com/display/xPDO20/toArray
                  -------------------------------

                  you can buy me a beer, if you like MIGX

                  http://webcmsolutions.de/migx.html

                  Thanks!
                  • 6848
                  • 52 Posts
                  It seems I have to put more time to read xPDO (and PHP PDO documentation in general)
                  My experience is mainly with java related layers like hibernate and myBatis.

                  This layer looks quite interesting and easy to use (when you know it smiley)

                  Thank you again.