Open jeffwhitfield opened 12 years ago
I assume this REQUIRES that a TV has a value list separated by commas instead of the default || separator normally used by the TV's?
I believe so. Basically uses the value from an autotag TV which I think uses a comma separated list last time I checked.
Jeff Whitfield 214-233-4329 jeffwhitfield@gmail.com
• Sent from my iPhone •
On Jun 15, 2012, at 1:56 PM, Jason Cowardreply@reply.github.com wrote:
I assume this REQUIRES that a TV has a value list separated by commas instead of the default || separator normally used by the TV's?
Reply to this email directly or view it on GitHub: https://github.com/opengeek/getResources/pull/64#issuecomment-6363958
Verified and, yeah, requires comma separated list. Reason is due to the FIND_IN_SET function requires a comma separated list when searching an array.
It seems to be the right "operator" FIND_IN_SET that i need it, but, somehow it doesn't work to me.
i have a TV like 23,24,25,26 and
my getResources call:
[[!getResources? &parents=1,2,3,4,5
&includeTVs=1
&processTVs=1
&tvPrefix=`&tpl=
tpl&tvFilters=
myTV(IN)[[*id]]` ]]
am i using right this operator?
p.s. I'm using 2.2.1-pl
sorry for my english :(
Why is the IN operator still not included? Tried it and it works perfect! Makes life easier...
@christianseel I hesitated to include this because it uses a MySQL-specific solution and I wanted to maintain compat with SQLSRV (at least at the time I did). I also simply have not tried it and saw the report of it not working above.
I'll look into it today.
That makes sense. But I think it's ok if the documentation highlights that this operator is mysql-only, since it's really usefull.
here is my "snippet.getresources.php" script. i have made same changes for long time ago, sorry i forgot to post it.
@opengeek i'm not sure if it is the best way, but it works to me.
usage: &tvFilters=tv-ids-with-commas-separated(IN)[[*id]]
<?php /**
filter2==one,filter1==bar%||filter1==foo
{{"alias:LIKE":"foo%", "OR:alias:LIKE":"%bar"},{"OR:pagetitle:=":"foobar", "AND:description:=":"raboof"}}
*/* set default properties */ $tpl = !empty($tpl) ? $tpl : ''; $includeContent = !empty($includeContent) ? true : false; $includeTVs = !empty($includeTVs) ? true : false; $includeTVList = !empty($includeTVList) ? explode(',', $includeTVList) : array(); $processTVs = !empty($processTVs) ? true : false; $processTVList = !empty($processTVList) ? explode(',', $processTVList) : array(); $tvPrefix = isset($tvPrefix) ? $tvPrefix : 'tv.'; $parents = (!empty($parents) || $parents === '0') ? explode(',', $parents) : array($modx->resource->get('id')); array_walk($parents, 'trim'); $parents = array_unique($parents); $depth = isset($depth) ? (integer) $depth : 10;
$tvFilters = !empty($tvFilters) ? explode('||', $tvFilters) : array();
$where = !empty($where) ? $modx->fromJSON($where) : array(); $showUnpublished = !empty($showUnpublished) ? true : false; $showDeleted = !empty($showDeleted) ? true : false;
$sortby = isset($sortby) ? $sortby : 'publishedon'; $sortbyTV = isset($sortbyTV) ? $sortbyTV : ''; $sortbyAlias = isset($sortbyAlias) ? $sortbyAlias : 'modResource'; $sortbyEscaped = !empty($sortbyEscaped) ? true : false; $sortdir = isset($sortdir) ? $sortdir : 'DESC'; $sortdirTV = isset($sortdirTV) ? $sortdirTV : 'DESC'; $limit = isset($limit) ? (integer) $limit : 5; $offset = isset($offset) ? (integer) $offset : 0; $totalVar = !empty($totalVar) ? $totalVar : 'total';
$dbCacheFlag = !isset($dbCacheFlag) ? false : $dbCacheFlag; if (is_string($dbCacheFlag) || is_numeric($dbCacheFlag)) { if ($dbCacheFlag == '0') { $dbCacheFlag = false; } elseif ($dbCacheFlag == '1') { $dbCacheFlag = true; } else { $dbCacheFlag = (integer) $dbCacheFlag; } }
/* multiple context support */ $contextArray = array(); $contextSpecified = false; if (!empty($context)) { $contextArray = explode(',',$context); array_walk($contextArray, 'trim'); $contexts = array(); foreach ($contextArray as $ctx) { $contexts[] = $modx->quote($ctx); } $context = implode(',',$contexts); $contextSpecified = true; unset($contexts,$ctx); } else { $context = $modx->quote($modx->context->get('key')); }
$pcMap = array(); $pcQuery = $modx->newQuery('modResource', array('id:IN' => $parents), $dbCacheFlag); $pcQuery->select(array('id', 'context_key')); if ($pcQuery->prepare() && $pcQuery->stmt->execute()) { foreach ($pcQuery->stmt->fetchAll(PDO::FETCH_ASSOC) as $pcRow) { $pcMap[(integer) $pcRow['id']] = $pcRow['context_key']; } }
$children = array(); $parentArray = array(); foreach ($parents as $parent) { $parent = (integer) $parent; if ($parent === 0) { $pchildren = array(); if ($contextSpecified) { foreach ($contextArray as $pCtx) { if (!in_array($pCtx, $contextArray)) { continue; } $options = $pCtx !== $modx->context->get('key') ? array('context' => $pCtx) : array(); $pcchildren = $modx->getChildIds($parent, $depth, $options); if (!empty($pcchildren)) $pchildren = array_merge($pchildren, $pcchildren); } } else { $cQuery = $modx->newQuery('modContext', array('key:!=' => 'mgr')); $cQuery->select(array('key')); if ($cQuery->prepare() && $cQuery->stmt->execute()) { foreach ($cQuery->stmt->fetchAll(PDO::FETCH_COLUMN) as $pCtx) { $options = $pCtx !== $modx->context->get('key') ? array('context' => $pCtx) : array(); $pcchildren = $modx->getChildIds($parent, $depth, $options); if (!empty($pcchildren)) $pchildren = array_merge($pchildren, $pcchildren); } } } $parentArray[] = $parent; } else { $pContext = array_key_exists($parent, $pcMap) ? $pcMap[$parent] : false; if ($debug) $modx->log(modX::LOG_LEVEL_ERROR, "context for {$parent} is {$pContext}"); if ($pContext && $contextSpecified && !in_array($pContext, $contextArray, true)) { $parent = next($parents); continue; } $parentArray[] = $parent; $options = !empty($pContext) && $pContext !== $modx->context->get('key') ? array('context' => $pContext) : array(); $pchildren = $modx->getChildIds($parent, $depth, $options); } if (!empty($pchildren)) $children = array_merge($children, $pchildren); $parent = next($parents); } $parents = array_merge($parentArray, $children);
/* build query */ $criteria = array("modResource.parent IN (" . implode(',', $parents) . ")"); if ($contextSpecified) { $contextResourceTbl = $modx->getTableName('modContextResource'); $criteria[] = "(modResource.context_key IN ({$context}) OR EXISTS(SELECT 1 FROM {$contextResourceTbl} ctx WHERE ctx.resource = modResource.id AND ctx.context_key IN ({$context})))"; } if (empty($showDeleted)) { $criteria['deleted'] = '0'; } if (empty($showUnpublished)) { $criteria['published'] = '1'; } if (empty($showHidden)) { $criteria['hidemenu'] = '0'; } if (!empty($hideContainers)) { $criteria['isfolder'] = '0'; } $criteria = $modx->newQuery('modResource', $criteria); if (!empty($tvFilters)) { $tmplVarTbl = $modx->getTableName('modTemplateVar'); $tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource'); $conditions = array(); $operators = array( '<=>' => '<=>', '===' => '=', '!==' => '!=', '<>' => '<>', '==' => 'LIKE', '!=' => 'NOT LIKE', '<<' => '<', '<=' => '<=', '=<' => '=<', '>>' => '>', '>=' => '>=', '=>' => '=>', '(IN)' => 'FIND_IN_SET' ); 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($sqlOperator == 'FIND_IN_SET'){
$tvValueField = 'tvr.value';
$tvDefaultField = 'tv.default_text';
if ($multiple) {
$filterGroup[] =
"(EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON FIND_IN_SET({$tvValue}, REPLACE({$tvValueField},'||',',')) 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 FIND_IN_SET({$tvValue}, REPLACE({$tvDefaultField},'||',',')) 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 FIND_IN_SET({$tvValue}, REPLACE({$tvValueField},'||',',')) 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 FIND_IN_SET({$tvValue}, REPLACE({$tvDefaultField},'||',',')) AND tv.id NOT IN (SELECT tmplvarid FROM {$tmplVarResourceTbl} WHERE contentid = modResource.id)) " .
")";
}
} else { 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;
}
}
}
/* include/exclude resources, via &resources=123,-456
prop */
if (!empty($resources)) {
$resourceConditions = array();
$resources = explode(',',$resources);
$include = array();
$exclude = array();
foreach ($resources as $resource) {
$resource = (int)$resource;
if ($resource == 0) continue;
if ($resource < 0) {
$exclude[] = abs($resource);
} else {
$include[] = $resource;
}
}
if (!empty($include)) {
$criteria->where(array('OR:modResource.id:IN' => $include), xPDOQuery::SQL_OR);
}
if (!empty($exclude)) {
$criteria->where(array('modResource.id:NOT IN' => $exclude), xPDOQuery::SQL_AND, null, 1);
}
}
if (!empty($where)) {
$criteria->where($where);
}
$total = $modx->getCount('modResource', $criteria); $modx->setPlaceholder($totalVar, $total);
$fields = array_keys($modx->getFields('modResource')); if (empty($includeContent)) { $fields = array_diff($fields, array('content')); } $columns = $includeContent ? $modx->getSelectColumns('modResource', 'modResource') : $modx->getSelectColumns('modResource', 'modResource', '', array('content'), true); $criteria->select($columns); if (!empty($sortbyTV)) { $criteria->leftJoin('modTemplateVar', 'tvDefault', array( "tvDefault.name" => $sortbyTV )); $criteria->leftJoin('modTemplateVarResource', 'tvSort', array( "tvSort.contentid = modResource.id", "tvSort.tmplvarid = tvDefault.id" )); if (empty($sortbyTVType)) $sortbyTVType = 'string'; if ($modx->getOption('dbtype') === 'mysql') { switch ($sortbyTVType) { case 'integer': $criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS SIGNED INTEGER) AS sortTV"); break; case 'decimal': $criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS DECIMAL) AS sortTV"); break; case 'datetime': $criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS DATETIME) AS sortTV"); break; case 'string': default: $criteria->select("IFNULL(tvSort.value, tvDefault.default_text) AS sortTV"); break; } } elseif ($modx->getOption('dbtype') === 'sqlsrv') { switch ($sortbyTVType) { case 'integer': $criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS BIGINT) AS sortTV"); break; case 'decimal': $criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS DECIMAL) AS sortTV"); break; case 'datetime': $criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS DATETIME) AS sortTV"); break; case 'string': default: $criteria->select("ISNULL(tvSort.value, tvDefault.default_text) AS sortTV"); break; } } $criteria->sortby("sortTV", $sortdirTV); } if (!empty($sortby)) { if (strpos($sortby, '{') === 0) { $sorts = $modx->fromJSON($sortby); } else { $sorts = array($sortby => $sortdir); } if (is_array($sorts)) { while (list($sort, $dir) = each($sorts)) { if ($sortbyEscaped) $sort = $modx->escape($sort); if (!empty($sortbyAlias)) $sort = $modx->escape($sortbyAlias) . ".{$sort}"; $criteria->sortby($sort, $dir); } } } if (!empty($limit)) $criteria->limit($limit, $offset);
if (!empty($debug)) { $criteria->prepare(); $modx->log(modX::LOG_LEVEL_ERROR, $criteria->toSQL()); } $collection = $modx->getCollection('modResource', $criteria, $dbCacheFlag);
$idx = !empty($idx) && $idx !== '0' ? (integer) $idx : 1; $first = empty($first) && $first !== '0' ? 1 : (integer) $first; $last = empty($last) ? (count($collection) + $idx - 1) : (integer) $last;
/* include parseTpl */ include_once $modx->getOption('getresources.core_path',null,$modx->getOption('core_path').'components/getresources/').'include.parsetpl.php';
$templateVars = array(); if (!empty($includeTVs) && !empty($includeTVList)) { $templateVars = $modx->getCollection('modTemplateVar', array('name:IN' => $includeTVList)); } foreach ($collection as $resourceId => $resource) { $tvs = array(); if (!empty($includeTVs)) { if (empty($includeTVList)) { $templateVars = $resource->getMany('TemplateVars'); } foreach ($templateVars as $tvId => $templateVar) { if (!empty($includeTVList) && !in_array($templateVar->get('name'), $includeTVList)) continue; if ($processTVs && (empty($processTVList) || in_array($templateVar->get('name'), $processTVList))) { $tvs[$tvPrefix . $templateVar->get('name')] = $templateVar->renderOutput($resource->get('id')); } else { $tvs[$tvPrefix . $templateVar->get('name')] = $templateVar->getValue($resource->get('id')); } } } $odd = ($idx & 1); $properties = arraymerge( $scriptProperties ,array( 'idx' => $idx ,'first' => $first ,'last' => $last ) ,$includeContent ? $resource->toArray() : $resource->get($fields) ,$tvs ); $resourceTpl = ''; $tplidx = 'tpl' . $idx; if (!empty($$tplidx)) { $resourceTpl = parseTpl($$tplidx, $properties); } if ($idx > 1 && empty($resourceTpl)) { $divisors = getDivisors($idx); if (!empty($divisors)) { foreach ($divisors as $divisor) { $tplnth = 'tpl_n' . $divisor; if (!empty($$tplnth)) { $resourceTpl = parseTpl($$tplnth, $properties); if (!empty($resourceTpl)) { break; } } } } } if ($idx == $first && empty($resourceTpl) && !empty($tplFirst)) { $resourceTpl = parseTpl($tplFirst, $properties); } if ($idx == $last && empty($resourceTpl) && !empty($tplLast)) { $resourceTpl = parseTpl($tplLast, $properties); } if ($odd && empty($resourceTpl) && !empty($tplOdd)) { $resourceTpl = parseTpl($tplOdd, $properties); } if (!empty($tpl) && empty($resourceTpl)) { $resourceTpl = parseTpl($tpl, $properties); } if (empty($resourceTpl)) { $chunk = $modx->newObject('modChunk'); $chunk->setCacheable(false); $output[]= $chunk->process(array(), '
' . print_r($properties, true) .''); } else { $output[]= $resourceTpl; } $idx++; }
/* output */ $toSeparatePlaceholders = $modx->getOption('toSeparatePlaceholders',$scriptProperties,false); if (!empty($toSeparatePlaceholders)) { $modx->setPlaceholders($output,$toSeparatePlaceholders); return ''; }
$output = implode($outputSeparator, $output); $toPlaceholder = $modx->getOption('toPlaceholder',$scriptProperties,false); if (!empty($toPlaceholder)) { $modx->setPlaceholder($toPlaceholder,$output); return ''; } print $output;
Hm. Any news on the IN operator? It would be very useful - in the past I always had to make dirty workarounds, changes in data representation or coding own solutions (for already existing functionality of getresources)...
Added an 'IN' operator to tvFilters. This uses the MySQL FIND_IN_SET function and will allow for filtering a comma delimited field value explicitly with a given value. Will also be adding a pull request for an update to tagLister that accommodates these changes.