function buildSortSQL($sortArray)
{
$sortCount = 0;
$sortList = false;
if (isset($sortArray) and is_array($sortArray) and count($sortArray) > 0) {
$sortList = $sortArray;
if (count($sortList) > 1 and !is_array($sortList[0])) {
$sortList = array($sortList);
}
}
$attributeJoinCount = 0;
$attributeFromSQL = "";
$attributeWereSQL = "";
$selectSQL = '';
if ($sortList !== false) {
$sortingFields = '';
foreach ($sortList as $sortBy) {
if (is_array($sortBy) and count($sortBy) > 0) {
if ($sortCount > 0) {
$sortingFields .= ', ';
}
$sortField = $sortBy[0];
switch ($sortField) {
case 'path':
$sortingFields .= 'path_string';
break;
case 'published':
$sortingFields .= 'ezcontentobject.published';
break;
case 'modified':
$sortingFields .= 'ezcontentobject.modified';
break;
case 'section':
$sortingFields .= 'ezcontentobject.section_id';
break;
case 'depth':
$sortingFields .= 'depth';
break;
case 'class_identifier':
$sortingFields .= 'ezcontentclass.identifier';
$selectSQL .= ', ezcontentclass.identifier';
break;
case 'class_name':
$classNameFilter = eZContentClassName::sqlFilter();
$selectSQL .= ", " . $classNameFilter['nameField'] . " AS class_name";
$sortingFields .= "class_name";
$attributeFromSQL .= " INNER JOIN {$classNameFilter['from']} ON ({$classNameFilter['where']})";
break;
case 'priority':
$sortingFields .= 'ezcontentobject_tree.priority';
break;
case 'name':
$sortingFields .= 'ezcontentobject_name.name';
break;
case 'attribute':
$sortClassID = $sortBy[2];
// Look up datatype for sorting
if (!is_numeric($sortClassID)) {
$sortClassID = eZContentObjectTreeNode::classAttributeIDByIdentifier($sortClassID);
}
$sortDataType = $sortClassID === false ? false : eZContentObjectTreeNode::sortKeyByClassAttributeID($sortClassID);
$sortKey = false;
if ($sortDataType == 'string') {
$sortKey = 'sort_key_string';
} else {
$sortKey = 'sort_key_int';
}
$sortingFields .= "a{$attributeJoinCount}.{$sortKey}";
$attributeFromSQL .= " INNER JOIN ezcontentobject_attribute as a{$attributeJoinCount} ON (a{$attributeJoinCount}.contentobject_id = ezcontentobject.id AND a{$attributeJoinCount}.version = ezcontentobject_name.content_version)";
$attributeWereSQL .= " AND a{$attributeJoinCount}.contentclassattribute_id = {$sortClassID}";
$selectSQL .= ", a{$attributeJoinCount}.{$sortKey}";
$attributeJoinCount++;
break;
default:
eZDebug::writeWarning('Unknown sort field: ' . $sortField, __METHOD__);
continue;
}
$sortOrder = true;
// true is ascending
if (isset($sortBy[1])) {
$sortOrder = $sortBy[1];
}
$sortingFields .= $sortOrder ? " ASC" : " DESC";
++$sortCount;
}
}
}
// Should we sort?
if ($sortCount == 0) {
$sortingFields = " ezcontentobject.published ASC";
}
return array('sortingFields' => $sortingFields, 'selectSQL' => $selectSQL, 'fromSQL' => $attributeFromSQL, 'whereSQL' => $attributeWereSQL);
}
/**
* Returns an array to filter a query by the given attributes in $attributeFilter
*
* @param array|bool $attributeFilter
* @param array $sortingInfo
* @param array|bool $language
* @return array|bool
*/
static function createAttributeFilterSQLStrings(&$attributeFilter, &$sortingInfo = array('sortCount' => 0, 'attributeJoinCount' => 0), $language = false)
{
// Check for attribute filtering
$filterSQL = array('from' => '', 'where' => '');
if ($language !== false && !is_array($language)) {
$language = array($language);
}
$totalAttributesFiltersCount = 0;
$invalidAttributesFiltersCount = 0;
if (isset($attributeFilter) && $attributeFilter !== false) {
if (!is_array($attributeFilter)) {
eZDebug::writeError("\$attributeFilter needs to be an array", __METHOD__);
return $filterSQL;
}
$filterArray = $attributeFilter;
// Check if first value of array is a string.
// To check for and/or filtering
$filterJoinType = 'AND';
if (is_string($filterArray[0])) {
if (strtolower($filterArray[0]) == 'or') {
$filterJoinType = 'OR';
} else {
if (strtolower($filterArray[0]) == 'and') {
$filterJoinType = 'AND';
}
}
unset($filterArray[0]);
}
$attibuteFilterJoinSQL = "";
$filterCount = $sortingInfo['sortCount'];
$justFilterCount = 0;
$db = eZDB::instance();
if (is_array($filterArray)) {
// Handle attribute filters and generate SQL
$totalAttributesFiltersCount = count($filterArray);
foreach ($filterArray as $filter) {
$isFilterValid = true;
// by default assumes that filter is valid
$filterAttributeID = $filter[0];
$filterType = $filter[1];
$filterValue = is_array($filter[2]) ? '' : $db->escapeString($filter[2]);
$useAttributeFilter = false;
switch ($filterAttributeID) {
case 'path':
$filterField = 'path_string';
break;
case 'published':
$filterField = 'ezcontentobject.published';
break;
case 'modified':
$filterField = 'ezcontentobject.modified';
break;
case 'modified_subnode':
$filterField = 'modified_subnode';
break;
case 'node_id':
$filterField = 'ezcontentobject_tree.node_id';
break;
case 'contentobject_id':
$filterField = 'ezcontentobject_tree.contentobject_id';
break;
case 'section':
$filterField = 'ezcontentobject.section_id';
break;
case 'state':
// state only supports =, !=, in, and not_in
// other operators do not make any sense in this context
$hasFilterOperator = true;
switch ($filterType) {
case '=':
case '!=':
$subQueryCondition = 'contentobject_state_id = ' . (int) $filter[2];
$filterOperator = $filterType == '=' ? 'IN' : 'NOT IN';
break;
case 'in':
case 'not_in':
if (is_array($filter[2])) {
$subQueryCondition = $db->generateSQLINStatement($filter[2], 'contentobject_state_id', false, false, 'int');
$filterOperator = $filterType == 'in' ? 'IN' : 'NOT IN';
} else {
$hasFilterOperator = false;
}
break;
default:
$hasFilterOperator = false;
eZDebug::writeError("Unknown attribute filter type for state: {$filterType}", __METHOD__);
break;
}
if ($hasFilterOperator) {
if ($filterCount - $sortingInfo['sortCount'] > 0) {
$attibuteFilterJoinSQL .= " {$filterJoinType} ";
}
$attibuteFilterJoinSQL .= "ezcontentobject.id {$filterOperator} (SELECT contentobject_id FROM ezcobj_state_link WHERE {$subQueryCondition})";
$filterCount++;
$justFilterCount++;
}
continue 2;
break;
case 'depth':
$filterField = 'depth';
break;
case 'class_identifier':
$filterField = 'ezcontentclass.identifier';
break;
case 'class_name':
$classNameFilter = eZContentClassName::sqlFilter();
$filterField = $classNameFilter['nameField'];
$filterSQL['from'] .= " INNER JOIN {$classNameFilter['from']} ON ({$classNameFilter['where']})";
break;
case 'priority':
$filterField = 'ezcontentobject_tree.priority';
break;
case 'name':
$filterField = 'ezcontentobject_name.name';
break;
case 'owner':
$filterField = 'ezcontentobject.owner_id';
break;
case 'visibility':
$filterValue = $filterValue == '1' ? 0 : 1;
$filterField = 'ezcontentobject_tree.is_invisible';
break;
default:
$useAttributeFilter = true;
break;
}
if ($useAttributeFilter) {
if (!is_numeric($filterAttributeID)) {
$filterAttributeID = eZContentObjectTreeNode::classAttributeIDByIdentifier($filterAttributeID);
}
if ($filterAttributeID === false) {
$isFilterValid = false;
if ($filterJoinType === 'AND') {
// go out
$invalidAttributesFiltersCount = $totalAttributesFiltersCount;
break;
}
++$invalidAttributesFiltersCount;
} else {
// Check datatype for filtering
$filterDataType = eZContentObjectTreeNode::sortKeyByClassAttributeID($filterAttributeID);
if ($filterDataType === false) {
$isFilterValid = false;
if ($filterJoinType === 'AND') {
// go out
$invalidAttributesFiltersCount = $totalAttributesFiltersCount;
break;
}
// check next filter
++$invalidAttributesFiltersCount;
} else {
$sortKey = false;
if ($filterDataType == 'string') {
$sortKey = 'sort_key_string';
} else {
$sortKey = 'sort_key_int';
}
$filterField = "a{$filterCount}.{$sortKey}";
// Use the same joins as we do when sorting,
// if more attributes are filtered by we will append them
if ($filterCount >= $sortingInfo['attributeJoinCount']) {
$filterSQL['from'] .= " INNER JOIN ezcontentobject_attribute a{$filterCount} ON (a{$filterCount}.contentobject_id = ezcontentobject.id) ";
}
// Ref http://issues.ez.no/19190
// If language param is set, we must take it into account.
if ($language) {
eZContentLanguage::setPrioritizedLanguages($language);
}
$filterSQL['where'] .= "\n a{$filterCount}.contentobject_id = ezcontentobject.id AND\n a{$filterCount}.contentclassattribute_id = {$filterAttributeID} AND\n a{$filterCount}.version = ezcontentobject_name.content_version AND ";
$filterSQL['where'] .= eZContentLanguage::sqlFilter("a{$filterCount}", 'ezcontentobject') . ' AND ';
if ($language) {
eZContentLanguage::clearPrioritizedLanguages();
}
}
}
}
if ($isFilterValid) {
$hasFilterOperator = true;
// Controls quotes around filter value, some filters do this manually
$noQuotes = false;
// Controls if $filterValue or $filter[2] is used, $filterValue is already escaped
$unEscape = false;
switch ($filterType) {
case '=':
$filterOperator = '=';
break;
case '!=':
$filterOperator = '<>';
break;
case '>':
$filterOperator = '>';
break;
case '<':
$filterOperator = '<';
break;
case '<=':
$filterOperator = '<=';
break;
case '>=':
$filterOperator = '>=';
break;
case 'like':
case 'not_like':
$filterOperator = $filterType == 'like' ? 'LIKE' : 'NOT LIKE';
// We escape the string ourselves, this MUST be done before wildcard replace
$filter[2] = $db->escapeString($filter[2]);
$unEscape = true;
// Since * is used as wildcard we need to transform the string to
// use % as wildcard. The following rules apply:
// - % -> \%
// - * -> %
// - \* -> *
// - \\ -> \
$filter[2] = preg_replace(array('#%#m', '#(?<!\\\\)\\*#m', '#(?<!\\\\)\\\\\\*#m', '#\\\\\\\\#m'), array('\\%', '%', '*', '\\\\'), $filter[2]);
break;
case 'in':
case 'not_in':
$filterOperator = $filterType == 'in' ? 'IN' : 'NOT IN';
// Turn off quotes for value, we do this ourselves
$noQuotes = true;
if (is_array($filter[2])) {
reset($filter[2]);
while (list($key, $value) = each($filter[2])) {
// Non-numerics must be escaped to avoid SQL injection
$filter[2][$key] = is_numeric($value) ? $value : "'" . $db->escapeString($value) . "'";
}
$filterValue = '(' . implode(",", $filter[2]) . ')';
} else {
$hasFilterOperator = false;
}
break;
case 'between':
case 'not_between':
$filterOperator = $filterType == 'between' ? 'BETWEEN' : 'NOT BETWEEN';
// Turn off quotes for value, we do this ourselves
$noQuotes = true;
if (is_array($filter[2])) {
// Check for non-numerics to avoid SQL injection
if (!is_numeric($filter[2][0])) {
$filter[2][0] = "'" . $db->escapeString($filter[2][0]) . "'";
}
if (!is_numeric($filter[2][1])) {
$filter[2][1] = "'" . $db->escapeString($filter[2][1]) . "'";
}
$filterValue = $filter[2][0] . ' AND ' . $filter[2][1];
}
break;
default:
$hasFilterOperator = false;
eZDebug::writeError("Unknown attribute filter type: {$filterType}", __METHOD__);
break;
}
if ($hasFilterOperator) {
if ($filterCount - $sortingInfo['sortCount'] > 0) {
$attibuteFilterJoinSQL .= " {$filterJoinType} ";
}
// If $unEscape is true we get the filter value from the 2nd element instead
// which must have been escaped by filter type
$filterValue = $unEscape ? $filter[2] : $filterValue;
$attibuteFilterJoinSQL .= "{$filterField} {$filterOperator} ";
$attibuteFilterJoinSQL .= $noQuotes ? "{$filterValue} " : "'{$filterValue}' ";
$filterCount++;
$justFilterCount++;
}
}
}
// end of 'foreach ( $filterArray as $filter )'
if ($totalAttributesFiltersCount == $invalidAttributesFiltersCount) {
eZDebug::writeNotice("Attribute filter returned false");
$filterSQL = false;
} else {
if ($justFilterCount > 0) {
$filterSQL['where'] .= " ( " . $attibuteFilterJoinSQL . " ) AND ";
}
}
}
// end of 'if ( is_array( $filterArray ) )'
}
return $filterSQL;
}
