$criteria = $modx->newQuery('modResource');
$criteria->innerJoin('modTemplateVarResource', 'TemplateVarResources');
$criteria->innerJoin('modTemplateVar', 'TemplateVar', 'TemplateVarResources.tmplvarid = TemplateVar.id');
$criteria->where(array('published' => 1,
'template' => 4,
'deleted' => 0
));
if (isset($_POST["jaartal"]) && $_POST["jaartal"] != '0') {
$criteria->where(array(
'TemplateVar.name' => 'startjaar',
'TemplateVarResources.value' => $_POST["jaartal"]
),xPDOQuery::SQL_AND);
}
if (isset($_POST["categorie"]) && $_POST["categorie"] != '0') {
$criteria->where(array(
'TemplateVar.name' => 'categorie',
'TemplateVarResources.value' => $_POST["categorie"]
),xPDOQuery::SQL_AND);
}
$docArray = $modx->getCollection('modResource', $criteria);
$criteria->andCondition(array(
'TemplateVar.name' => 'categorie',
'TemplateVarResources.value' => $_POST["categorie"],
)
);
☆ A M B ☆
$criteria = $modx->newQuery('modResource');
$criteria->innerJoin('modTemplateVarResource', 'CategoryTemplateVarResources', 'modResource.id = CategoryTemplateVarResources.resource');
$criteria->innerJoin('modTemplateVar', 'CategoryTemplateVar', 'CategoryTemplateVarResources.tmplvarid = CategoryTemplateVar.id AND CategoryTemplateVar.name = \'categorie\'');
$criteria->innerJoin('modTemplateVarResource', 'JaartalTemplateVarResources', 'modResource.id = JaartalTemplateVarResources.resource');
$criteria->innerJoin('modTemplateVar', 'JaartalTemplateVar', 'JaartalTemplateVarResources.tmplvarid = JaartalTemplateVar.id AND JaartalTemplateVar.name = \'startjaar\'');
...
if (isset($_POST["categorie"]) && $_POST["categorie"] != '0') {
$criteria->where(array(
'CategoryTemplateVarResources.value' => $_POST["categorie"]
),xPDOQuery::SQL_AND);
}
'TemplateVarResources.value:LIKE' => '%'.$_POST["trefwoord"].'%'
$tvFilters = "description==projectleider";
$tvFilters = "description=inArray=projectleider";
$criteria->condition($criteria->query['where'][0][1], $cond, xPDOQuery::SQL_AND, null, $cGroup);
<?php
if (!function_exists('tvFilters')) {
function tvFilters($tvFilters = '', &$criteria) {
global $modx;
$tvFilters = !empty($tvFilters) ? explode('||', $tvFilters) : array();
if (!empty($tvFilters)) {
$tmplVarTbl = $modx->getTableName('modTemplateVar');
$tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource');
$conditions = array();
$operators = array(
'<=>' => '<=>',
'===' => '=',
'!==' => '!=',
'<>' => '<>',
'==' => 'LIKE',
'!=' => 'NOT LIKE',
'<<' => '<',
'<=' => '<=',
'=<' => '=<',
'>>' => '>',
'>=' => '>=',
'=>' => '=>');
foreach ($tvFilters as $fGroup => $tvFilter) {
$filterGroup = array();
$filters = explode(',', $tvFilter);
$multiple = count($filters) > 0;
foreach ($filters as $filter) {
$operator = '==';
$sqlOperator = 'LIKE';
foreach ($operators as $op => $opSymbol) {
if (strpos($filter, $op, 1) !== false) {
$operator = $op;
$sqlOperator = $opSymbol;
break;
}
}
$tvValueField = 'tvr.value';
$tvDefaultField = 'tv.default_text';
$f = explode($operator, $filter);
if (count($f) == 2) {
$tvName = $modx->quote($f[0]);
if (is_numeric($f[1]) && !in_array($sqlOperator, array('LIKE', 'NOT LIKE'))) {
$tvValue = $f[1];
if ($f[1] == (integer)$f[1]) {
$tvValueField = "CAST({$tvValueField} AS SIGNED INTEGER)";
$tvDefaultField = "CAST({$tvDefaultField} AS SIGNED INTEGER)";
} else {
$tvValueField = "CAST({$tvValueField} AS DECIMAL)";
$tvDefaultField = "CAST({$tvDefaultField} AS DECIMAL)";
}
} else {
$tvValue = $modx->quote($f[1]);
}
if ($multiple) {
$filterGroup[] = "(EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) " .
"OR EXISTS (SELECT 1 FROM {$tmplVarTbl} tv WHERE tv.name = {$tvName} AND {$tvDefaultField} {$sqlOperator} {$tvValue} AND tv.id NOT IN (SELECT tmplvarid FROM {$tmplVarResourceTbl} WHERE contentid = modResource.id)) " .
")";
} else {
$filterGroup = "(EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) " .
"OR EXISTS (SELECT 1 FROM {$tmplVarTbl} tv WHERE tv.name = {$tvName} AND {$tvDefaultField} {$sqlOperator} {$tvValue} AND tv.id NOT IN (SELECT tmplvarid FROM {$tmplVarResourceTbl} WHERE contentid = modResource.id)) " .
")";
}
} elseif (count($f) == 1) {
$tvValue = $modx->quote($f[0]);
if ($multiple) {
$filterGroup[] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)";
} else {
$filterGroup = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)";
}
}
}
$conditions[] = $filterGroup;
}
if (!empty($conditions)) {
$firstGroup = true;
foreach ($conditions as $cGroup => $c) {
if (is_array($c)) {
$first = true;
foreach ($c as $cond) {
if ($first && !$firstGroup) {
$criteria->condition($criteria->query['where'][0][1], $cond, xPDOQuery::SQL_OR, null, $cGroup);
} else {
$criteria->condition($criteria->query['where'][0][1], $cond, xPDOQuery::SQL_AND, null, $cGroup);
}
$first = false;
}
} else {
$criteria->condition($criteria->query['where'][0][1], $c, $firstGroup ? xPDOQuery::SQL_AND : xPDOQuery::SQL_OR, null, $cGroup);
}
$firstGroup = false;
}
}
return true;
}
}
}
$c = $modx->newQuery('modResource');
$c->where(array('published' => 1,
'template' => 4,
'deleted' => 0
));
$filter = array();
if (isset($_POST["jaartal"]) && $_POST["jaartal"] != '0') {
$filter[] = 'startjaar===' . $_POST["jaartal"] ;
}
if (isset($_POST["categorie"]) && $_POST["categorie"] != '0') {
$filter[] = 'categorie===' . $_POST["categorie"] ;
}
if (count($filter)>0){
tvFilters(implode(',',$filter), $c);
}
$docArray = $modx->getCollection('modResource', $c);