[[!getResources? &tpl=`featuredWork` &includeTVs=`1` &processTVs=`1` &tvFilters=`Service==%Website Design%` ]]
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
....modResource.parent IN (22) AND...
parents : Comma-delimited list of ids serving as parents. Use -1 to ignore parents when specifying resources to include.
....modResource.parent IN (-1) AND...
....modResource.parent IN (0) AND...
[[!getResources? &tpl=`featuredWork` &includeTVs=`1` &processTVs=`1` &resources=`42` &parents=`-1` ]]
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
<?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); }
[[!filterResources? &tvNames=`Service` &Service=`Website Design` &tpl=`featuredWork` ]]
$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'));
$c->where(array('published'=>1,'deleted:!=' =>'1')); $c->where(array('id:IN'=>$childs)); $c->sortby($sortby);
[[!filterResources? &tvNames=`articlestags` &articlestags=`[[*articlestags]]` &tpl=`contentItem` &parent=`2` &debug=`1` ]]
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