New Community Forums are coming. Watch this space for news.
Subscribe: RSS
  • Hello there.

    How can i query the DB in order to extract multiple tpl variables for 1 resource?

    i mean, as you already know, THE TABLE modx_site_content contains pagetitle and content in this table.

    THE TABLE modx_site_tmplvar_contentvalues contains the tplvars a user created for different purposes: it contains as columns contentid(which refers to the id in modx_site_content) and value(which contains the value of, for example, image-description, image and SEO-Title)

    modx_site_content
    -----------------------------------------------------------
    id | pagetitle | content
    -----------------------------------------------------------
    1 article 1 this is the content1...
    2 article 2 this is the content2...


    modx_site_tmplvar_contentvalues
    -------------------------------------------------------------
    tmplvarid | contentid | value
    -------------------------------------------------------------
    1 1 path-to-image1.jpg
    2 1 description for image1
    3 1 SEO title for article 1
    1 2 path-to-image2.jpg
    2 2 description for image2
    3 2 SEO title for article 2

    i would like this result:

    pagetitle | content | image(it contains the url) | image-description | SEO-Title
    ----------------------------------------------------------------------------------------------------------------
    article 1 | this is the content1... | path-to-image.jpg | description for image1 | SEO title for article 1
    article 2 | this is the content2... | path-to-image2.jpg | description for image2 | SEO title for article 2


    Thanks and would appreciate your help
    sorry, file attachment(table-ok.jpg) ifs for the table(screenshot)

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

    [ed. note: jacielluve last edited this post 4 months, 2 weeks ago.]
    • discuss.answer
      Are you opposed to using the getResources or pdoResources extras? They're designed to do exactly what you want and very easy to set up, though a custom snippet would be faster.

      In a snippet, you could use $modx->getCollectionGraph() to get the resources and their TV values in one query *if* no TV is set to its default value (I think you're fine on that).

      The simplest method (slower, but not as slow as getResources) is to get the resources with getCollection(), then do something like this:

      $output = '';
      $docs = $modx->getCollection('modResource', $criteria);
      foreach($docs as $doc) {
         $fields = array(
             'pagetitle' => $doc->get('pagetitle'),
             'image' => $doc->getTVValue('image'), // name or ID of the TV in the parentheses
             'description' => $doc->getTVValue('description'),
         );
         $output .= $modx->getChunk('myTpl', $fields);
      }
      return $output;
      

      The myTpl Tpl chunk would look like this:

      <div class="item">
          <p>[[+pagetitle]]</p>
          <img src="[[+image]]">
          <p>[[+description]]</p>
      </div>




      [ed. note: BobRay last edited this post 4 months, 2 weeks 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
      • Thanks for the answer Bob. I use getresources in almost 80% of my project, and i bet your code is correct, but i wanted to extract code using the sql sintax XD, i'll use getresources or some kind of json in order to get export data to another db XD