I came across a little problem I had, getting related resources by tag.
The getResource snippet allows for a tvFilter. As the documentation reads:
* tvFilters - (Opt) Delimited-list of TemplateVar values to filter resources by. Supports two
* delimiters and two value search formats. THe first delimeter || represents a logical OR and the
* primary grouping mechanism. Within each group you can provide a comma-delimited list of values.
* These values can be either tied to a specific TemplateVar by name, e.g. myTV==value, or just the
* value, indicating you are searching for the value in any TemplateVar tied to the Resource. An
* example would be &tvFilters=`filter2==one,filter1==bar%||filter1==foo`
* [NOTE: filtering by values uses a LIKE query and % is considered a wildcard.]
* [NOTE: this only looks at the raw value set for specific Resource, i. e. there must be a value
* specifically set for the Resource and it is not evaluated.]
Now, the current filter was no good to me, since I needed an AND OR query. I modified it to suite my needs.
Basically what the snippet does it first explode on the || seperator, indicating the type of search. This is done way above at the top of the snippet. Now, if the script !empty passes, it explodes on the comma , seperator and already counts the type of filter it is. If any || is placed, it’s an OR query. Otherwise it would be an AND query. (tags==%test% -> and `tv`.`value` LIKE ’%test’) Now, I don’t want that, I need an AND OR query. So, I’ve replaced some code. Below is the example how it looked:
if (!empty($tvFilters)) {
$tmplVarTbl = $modx->getTableName('modTemplateVar');
$tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource');
$conditions = array();
foreach ($tvFilters as $fGroup => $tvFilter) {
$filterGroup = count($tvFilters) > 1 ? $fGroup + 1 : 0;
$filters = explode(',', $tvFilter);
foreach ($filters as $filter) {
$f = explode('==', $filter);
if (count($f) == 2) {
$tvName = $modx->quote($f[0]);
$tvValue = $modx->quote($f[1]);
$conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`name` = {$tvName} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
} elseif (count($f) == 1) {
$tvValue = $modx->quote($f[0]);
$conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
}
}
}
And how it looks now:
foreach ($tvFilters as $fGroup => $tvFilter) {
$filterGroup = count($tvFilters) > 1 ? $fGroup + 1 : 0;
$filters = explode(',', $tvFilter);
foreach ($filters as $key => $filter) {
$f = explode('==', $filter);
if ($key == 0 && $filterGroup > 0) {
$tvName = $modx->quote($f[0]);
$tvValue = $modx->quote($f[1]);
$conditions[][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`name` = {$tvName} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
continue;
}
if (count($f) == 2) {
$tvName = $modx->quote($f[0]);
$tvValue = $modx->quote($f[1]);
$conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`name` = {$tvName} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
} elseif (count($f) == 1) {
$tvValue = $modx->quote($f[0]);
$conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
}
}
}
Notice the continue; function inside the foreach. I’m setting the first condition to be an AND statement, since the query automaticlly increases the count on the $filterGroup. Now, the first statement is an AND and the rest always will be OR. Don’t fear, then only one filter is given, the system ignores the first portion with the continue; function and executes it’s normal behaviour.
This is exactly what I needed and perhaps some of you can profit from it as well.
