Browsing through the master branch on GitHub (https://github.com/z…lib/util/DBUtil.php) it seems that the ['__META__']['operator'] disappeared. It was used in _generateCategoryFilter(). This operator made it possible to AND the filtering of multiple categories. It was committed by Mateo here: (27130).
Is there a new approach available to achieve the same thing in 1.3?
The operator property is still available 1.2.5, but there is a bug there.
The $n++ happens to early. I've modified it as follows:
Code
/**
* Build a list of objects which are mapped to the specified categories
*
* @param categoryFilter The category list to use for filtering
* @param returnArray Whether or not to return an array (optional) (default=false)
*
* @return The resulting string or array
*/
function _generateCategoryFilter($tablename, $categoryFilter, $returnArray = false)
{
if (!$categoryFilter) {
return '';
}
if (!pnModDBInfoLoad('Categories')) {
return '';
}
// check the meta data
if (isset($categoryFilter['__META__']['module'])) {
$modname = $categoryFilter['__META__']['module'];
} else {
$modname = pnModGetName();
}
// check operator to use
// when it's AND, the where contains subqueries
if (isset($categoryFilter['__META__']['operator']) && in_array(strtolower($categoryFilter['__META__']['operator']), array('and', 'or'))) {
$op = strtoupper($categoryFilter['__META__']['operator']);
} else {
$op = 'OR';
}
unset($categoryFilter['__META__']);
// get the properties IDs in the category register
Loader::loadClass('CategoryRegistryUtil');
$propids = CategoryRegistryUtil::getRegisteredModuleCategoriesIds($modname, $tablename);
// build the where clause
$n = 1; // subquery counter
$catmapobjtbl = DBUtil::getLimitedTablename('categories_mapobj');
$where = array();
foreach ($categoryFilter as $property => $category)
{
$prefix = '';
if ($op == 'AND') {
$prefix = "table$n.";
}
// this allows to have an array of categories IDs
if (is_array($category)) {
$wherecat = array();
foreach ($category as $cat) {
$wherecat[] = "{$prefix}cmo_category_id='" . DataUtil::formatForStore($cat) . "'";
}
$wherecat = '(' . implode(' OR ', $wherecat) . ')';
// if there's only one category ID
} else {
$wherecat = "{$prefix}cmo_category_id='" . DataUtil::formatForStore($category) . "'";
}
// process the where depending of the operator
if ($op == 'AND') {
$where[] = "cmo_obj_id IN (SELECT {$prefix}cmo_obj_id FROM $catmapobjtbl table$n WHERE {$prefix}cmo_reg_id = '".DataUtil::formatForStore($propids[$property])."' AND $wherecat)";
} else {
$where[] = "(cmo_reg_id='" . DataUtil::formatForStore($propids[$property]) . "' AND $wherecat)";
}
$n++;
}
$where = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND (" . implode(" $op ", $where) . ')';
// perform the query
$objIds = DBUtil::selectFieldArray('categories_mapobj', 'obj_id', $where);
// this ensures that we return an empty set if no objects are mapped to the requested categories
if (!$objIds)
$objIds[] = -1;
if ($returnArray)
return $objIds;
return implode(',', $objIds);
}
* Build a list of objects which are mapped to the specified categories
*
* @param categoryFilter The category list to use for filtering
* @param returnArray Whether or not to return an array (optional) (default=false)
*
* @return The resulting string or array
*/
function _generateCategoryFilter($tablename, $categoryFilter, $returnArray = false)
{
if (!$categoryFilter) {
return '';
}
if (!pnModDBInfoLoad('Categories')) {
return '';
}
// check the meta data
if (isset($categoryFilter['__META__']['module'])) {
$modname = $categoryFilter['__META__']['module'];
} else {
$modname = pnModGetName();
}
// check operator to use
// when it's AND, the where contains subqueries
if (isset($categoryFilter['__META__']['operator']) && in_array(strtolower($categoryFilter['__META__']['operator']), array('and', 'or'))) {
$op = strtoupper($categoryFilter['__META__']['operator']);
} else {
$op = 'OR';
}
unset($categoryFilter['__META__']);
// get the properties IDs in the category register
Loader::loadClass('CategoryRegistryUtil');
$propids = CategoryRegistryUtil::getRegisteredModuleCategoriesIds($modname, $tablename);
// build the where clause
$n = 1; // subquery counter
$catmapobjtbl = DBUtil::getLimitedTablename('categories_mapobj');
$where = array();
foreach ($categoryFilter as $property => $category)
{
$prefix = '';
if ($op == 'AND') {
$prefix = "table$n.";
}
// this allows to have an array of categories IDs
if (is_array($category)) {
$wherecat = array();
foreach ($category as $cat) {
$wherecat[] = "{$prefix}cmo_category_id='" . DataUtil::formatForStore($cat) . "'";
}
$wherecat = '(' . implode(' OR ', $wherecat) . ')';
// if there's only one category ID
} else {
$wherecat = "{$prefix}cmo_category_id='" . DataUtil::formatForStore($category) . "'";
}
// process the where depending of the operator
if ($op == 'AND') {
$where[] = "cmo_obj_id IN (SELECT {$prefix}cmo_obj_id FROM $catmapobjtbl table$n WHERE {$prefix}cmo_reg_id = '".DataUtil::formatForStore($propids[$property])."' AND $wherecat)";
} else {
$where[] = "(cmo_reg_id='" . DataUtil::formatForStore($propids[$property]) . "' AND $wherecat)";
}
$n++;
}
$where = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND (" . implode(" $op ", $where) . ')';
// perform the query
$objIds = DBUtil::selectFieldArray('categories_mapobj', 'obj_id', $where);
// this ensures that we return an empty set if no objects are mapped to the requested categories
if (!$objIds)
$objIds[] = -1;
if ($returnArray)
return $objIds;
return implode(',', $objIds);
}
. Guess I'll need to add a ticket to get it back.
