We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 21792
    • 37 Posts
    using

    MODx 2.0.8
    getResources 1.4.0
    PHP 5.3.8
    MySQL 5.0.77

    Hi, While on page with id 22 I have this call to getResourses:

    [[!getResources?
    	&tpl=`featuredWork`
    	&includeTVs=`1`
    	&processTVs=`1`
    	&tvFilters=`Service==%Website Design%`
    ]]


    This does not output anything, so I added the debug parameter and looked at the SQL, which is

    SELECT
    	`modResource`.`id`,
    	`modResource`.`type`,
    	`modResource`.`contentType`,
    	`modResource`.`pagetitle`,
    	`modResource`.`longtitle`,
    	`modResource`.`description`,
    	`modResource`.`alias`,
    	`modResource`.`link_attributes`,
    	`modResource`.`published`,
    	`modResource`.`pub_date`,
    	`modResource`.`unpub_date`,
    	`modResource`.`parent`,
    	`modResource`.`isfolder`,
    	`modResource`.`introtext`,
    	`modResource`.`richtext`,
    	`modResource`.`template`,
    	`modResource`.`menuindex`,
    	`modResource`.`searchable`,
    	`modResource`.`cacheable`,
    	`modResource`.`createdby`,
    	`modResource`.`createdon`,
    	`modResource`.`editedby`,
    	`modResource`.`editedon`,
    	`modResource`.`deleted`,
    	`modResource`.`deletedon`,
    	`modResource`.`deletedby`,
    	`modResource`.`publishedon`,
    	`modResource`.`publishedby`,
    	`modResource`.`menutitle`,
    	`modResource`.`donthit`,
    	`modResource`.`haskeywords`,
    	`modResource`.`hasmetatags`,
    	`modResource`.`privateweb`,
    	`modResource`.`privatemgr`,
    	`modResource`.`content_dispo`,
    	`modResource`.`hidemenu`,
    	`modResource`.`class_key`,
    	`modResource`.`context_key`,
    	`modResource`.`content_type`
    FROM
    	`modx_site_content` AS `modResource`
    WHERE
    	(
    		(
    			modResource.parent IN (22)
    			AND
    			`modResource`.`deleted` = 0
    			AND
    			`modResource`.`published` = 1
    			AND
    			`modResource`.`hidemenu` = 0
    		)
    		AND EXISTS (
    			SELECT
    				1
    			FROM
    				`modx_site_tmplvar_contentvalues` tvr
    			JOIN
    				`modx_site_tmplvars` tv
    				ON
    				tvr.value LIKE '%Website Design%'
    				AND
    				tv.name = 'Service'
    				AND
    				tv.id = tvr.tmplvarid
    			WHERE
    				tvr.contentid = modResource.id
    		)
    	)
    ORDER BY
    	publishedon DESC
    LIMIT
    	5


    running this in mysql does not produce any errors, so I analysed the SQL and saw this part

    ....modResource.parent IN (22) AND...


    The resources I'm trying to include have various different parents, but all have a TV of 'Service' with a value like 'Website Design'

    If I take out that one line of the SQL, and run the query, I get the results I expect.

    Back to the Docs

    parents : Comma-delimited list of ids serving as parents. Use -1 to ignore parents when specifying resources to include.

    Even though I'm not specifying a list of resources I tried adding &parents=`-1` anyway - same result - an empty set as the SQL had
    ....modResource.parent IN (-1) AND...
    in it

    So, the question really is this:

    How can I use getResources to pull back pages filtered by template values only (ie omiting parents and resources)?
    Is this even possible?
      • 4172
      • 5,888 Posts
      did you try
      &parents=`0`
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 21792
        • 37 Posts
        I did,

        and guess what, that line of SQL is
        ....modResource.parent IN (0) AND...
        which is just as useless

        If I use this snippet call

        [[!getResources?
        	&tpl=`featuredWork`
        	&includeTVs=`1`
        	&processTVs=`1`
        	&resources=`42`
        	&parents=`-1`
        ]]

        the SQL is
        SELECT
        	`modResource`.`id`,
        	`modResource`.`type`,
        	`modResource`.`contentType`,
        	`modResource`.`pagetitle`,
        	`modResource`.`longtitle`,
        	`modResource`.`description`,
        	`modResource`.`alias`,
        	`modResource`.`link_attributes`,
        	`modResource`.`published`,
        	`modResource`.`pub_date`,
        	`modResource`.`unpub_date`,
        	`modResource`.`parent`,
        	`modResource`.`isfolder`,
        	`modResource`.`introtext`,
        	`modResource`.`richtext`,
        	`modResource`.`template`,
        	`modResource`.`menuindex`,
        	`modResource`.`searchable`,
        	`modResource`.`cacheable`,
        	`modResource`.`createdby`,
        	`modResource`.`createdon`,
        	`modResource`.`editedby`,
        	`modResource`.`editedon`,
        	`modResource`.`deleted`,
        	`modResource`.`deletedon`,
        	`modResource`.`deletedby`,
        	`modResource`.`publishedon`,
        	`modResource`.`publishedby`,
        	`modResource`.`menutitle`,
        	`modResource`.`donthit`,
        	`modResource`.`haskeywords`,
        	`modResource`.`hasmetatags`,
        	`modResource`.`privateweb`,
        	`modResource`.`privatemgr`,
        	`modResource`.`content_dispo`,
        	`modResource`.`hidemenu`,
        	`modResource`.`class_key`,
        	`modResource`.`context_key`,
        	`modResource`.`content_type`
        FROM
        	`modx_site_content` AS `modResource`
        WHERE
        	(
        		(
        			modResource.parent IN (-1)
        			AND
        			`modResource`.`deleted` = 0
        			AND
        			`modResource`.`published` = 1
        			AND
        			`modResource`.`hidemenu` = 0
        		)
        		OR
        		`modResource`.`id` IN (42)
        	)
        ORDER BY
        	publishedon DESC
        LIMIT
        	5
        


        and i get the results I expect

        I still don't get any results for using TVs only

        I even tried setting the parents to 0 and increasing max depth - still nothing
        [ed. note: Northie last edited this post 12 years, 3 months ago.]
          • 4172
          • 5,888 Posts
          Not sure, why getResources doesn't do what you want, but you can try this snippet.
          Name it filterResources with this code:

          <?php
          
          //[[filterResources? &tvNames=`cb1,cb2` &cb1=`value A`]]
          
          //$tvNames = 'cb1,cb2';
          //$debug = 1;
          
          $element = $modx->getOption('element',$scriptProperties,'getResources');
          
          $tmplVarTbl = $modx->getTableName('modTemplateVar');
          $tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource');
          
          $tvNames = explode(',', $tvNames);
          
          $c = $modx->newQuery('modResource');
          $columns = $modx->getSelectColumns('modResource', 'modResource', 'res_', array('id'));
          $c->select($columns);
          
          foreach ($tvNames as $cGroup => $tvName) {
          
          if (isset($_REQUEST[$tvName])){
              $scriptProperties[$tvName] = $_REQUEST[$tvName];    
          }
          
          if (isset($scriptProperties[$tvName])){
              $tvValue = $scriptProperties[$tvName];
              $tvName = $modx->quote($tvName);
          
              if (is_array($tvValue)) {
                  $conjunction = xPDOQuery::SQL_AND;
                  foreach ($tvValue as $value) {
                      $c->where("EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON CONCAT('||',tvr.value,'||') LIKE '%||{$value}||%' AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)",
                          $conjunction, null, $cGroup);
                      $conjunction = xPDOQuery::SQL_OR;
                  }
              } else {
                  $c->where("EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON CONCAT('||',tvr.value,'||') LIKE '%||{$tvValue}||%' AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)",
                      xPDOQuery::SQL_AND, null, $cGroup);
              }        
          }
          
          
          }
          
          $c->prepare();
          if ($debug) {
              echo $c->toSql() . '
          ';
          }
          
          $sql = $c->toSql();
          
          
          $stmt = $modx->query($sql);
          if ($stmt) {
              while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                  $ids[] = $row['res_id'];
              }
          }
          
          
          $scriptProperties['resources'] = implode(',', $ids);
          if ($debug) {
              echo 'IDs: ' . $scriptProperties['resources'] . '
          ';
          }
          
          if (!empty($ids)) {
              return $modx->runSnippet($element, $scriptProperties);
          }
          


          and try calling it this way:

          [[!filterResources? 
          &tvNames=`Service` 
          &Service=`Website Design`
          &tpl=`featuredWork`
          ]]


          [ed. note: Bruno17 last edited this post 12 years, 3 months ago.]
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
          • Did you file a bug for getResources? And does this behavior continue in MODX 2.1.5?
              • 21792
              • 37 Posts
              @Bruno Thanks for that snippet, I'll give it a go...looks promising

              @Everettg_99 I was hoping that it wasn't a bug and that I was just not using the snippet correctly.

              Come Monday I might try upgrading to 2.1.5 and see what happens...the only issue for upgrading for me is the way we have our server set up - We have over 20 sites running revo 2.0.8 all from the same code base, all using deprecated parts of the api that have been removed in 2.1+ sad
                • 9995
                • 1,613 Posts
                somehow i can't get custom TV's.

                ##edit

                [ [+tv.name]] [ed. note: fourroses666 last edited this post 12 years, 3 months ago.]
                  Evolution user, I like the back-end speed and simplicity smiley
                • Did you include the

                  &includeTVs=`1`


                  in your Snippet call?
                    • 19328
                    • 433 Posts
                    hmm, third try to post, there seems to be something wrong with my post or the forums!

                    I know this is an old thread, but I'm trying to use the filterResources snippet as well (thank you Bruno).
                    I want to add something to the query, to only show resources from a specified parent and only published and undeleted ones.

                    I added this (got it from another thread):
                    $parent = isset($parent) ? $parent : $modx->resource->get('id');
                    $depth= isset($depth) ? $depth : '10';
                    $sortby = isset($sortby) ? $sortby : 'pagetitle';
                    $childs = $modx->getChildIds($parent,$depth,array('context' => 'web'));


                    And then this:
                    $c->where(array('published'=>1,'deleted:!=' =>'1'));
                    $c->where(array('id:IN'=>$childs));
                    $c->sortby($sortby);


                    My snippet call:
                    [[!filterResources? 
                    &tvNames=`articlestags` 
                    &articlestags=`[[*articlestags]]`
                    &tpl=`contentItem`
                    &parent=`2`
                    &debug=`1`
                    ]]


                    The debug output is:
                    SELECT `modResource`.`id` AS `res_id` FROM `modxeh_site_content` AS `modResource` WHERE ( EXISTS (SELECT 1 FROM `modxeh_site_tmplvar_contentvalues` tvr JOIN `modxeh_site_tmplvars` tv ON CONCAT(',',tvr.value,',') LIKE '%,tag1,%' AND tv.name = 'articlestags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modxeh_site_tmplvar_contentvalues` tvr JOIN `modxeh_site_tmplvars` tv ON CONCAT(',',tvr.value,',') LIKE '%,another tag,%' AND tv.name = 'articlestags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND ( `modResource`.`published` = 1 AND `modResource`.`deleted` != 1 ) AND `modResource`.`id` IN (3,4,5,7) ) ORDER BY pagetitle ASC IDs: 5,6,2,7,4


                    The snippet does correctly show only published resources, so that part is working. But it's also showing resources that don't belong to the specified parent (namely resource with ID 2 and ID 6).

                    Should I write the where clause a different way? Or has it got something to do with the alias for the ID column? Thanks for any help!
                    I also attached my entire snippet to this post.