/**
* Creates an array with sorting SQL strings to be appended to a query
*
* @param array|bool $sortList
* @param string $treeTableName
* @param bool $allowCustomColumns
* @return array
*/
static function createSortingSQLStrings($sortList, $treeTableName = 'ezcontentobject_tree', $allowCustomColumns = false)
{
$sortingInfo = array('sortCount' => 0, 'sortingFields' => " path_string ASC", 'attributeJoinCount' => 0, 'attributeFromSQL' => "", 'attributeTargetSQL' => "", 'attributeWhereSQL' => "");
if ($sortList and is_array($sortList) and count($sortList) > 0) {
if (count($sortList) > 1 and !is_array($sortList[0])) {
$sortList = array($sortList);
}
$sortingFields = '';
$sortCount = 0;
$attributeJoinCount = 0;
$stateJoinCount = 0;
$attributeFromSQL = "";
$attributeWhereSQL = "";
$datatypeSortingTargetSQL = "";
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 'path_string':
$sortingFields .= 'path_identification_string';
break;
case 'published':
$sortingFields .= 'ezcontentobject.published';
break;
case 'modified':
$sortingFields .= 'ezcontentobject.modified';
break;
case 'modified_subnode':
$sortingFields .= 'modified_subnode';
break;
case 'section':
$sortingFields .= 'ezcontentobject.section_id';
break;
case 'node_id':
$sortingFields .= $treeTableName . '.node_id';
break;
case 'contentobject_id':
$sortingFields .= 'ezcontentobject.id';
break;
case 'depth':
$sortingFields .= 'depth';
break;
case 'class_identifier':
$sortingFields .= 'ezcontentclass.identifier';
break;
case 'class_name':
$classNameFilter = eZContentClassName::sqlFilter();
$sortingFields .= 'contentclass_name';
$datatypeSortingTargetSQL .= ", {$classNameFilter['nameField']} AS contentclass_name";
$attributeFromSQL .= " INNER JOIN {$classNameFilter['from']} ON ({$classNameFilter['where']})";
break;
case 'priority':
$sortingFields .= $treeTableName . '.priority';
break;
case 'name':
$sortingFields .= 'ezcontentobject_name.name';
break;
case 'attribute':
$classAttributeID = $sortBy[2];
if (!is_numeric($classAttributeID)) {
$classAttributeID = eZContentObjectTreeNodeNoLanguage::classAttributeIDByIdentifier($classAttributeID);
}
$contentAttributeTableAlias = "a{$attributeJoinCount}";
$datatypeFromSQL = "ezcontentobject_attribute {$contentAttributeTableAlias}";
$datatypeWhereSQL = "\n {$contentAttributeTableAlias}.contentobject_id = ezcontentobject.id AND\n {$contentAttributeTableAlias}.contentclassattribute_id = {$classAttributeID} AND\n {$contentAttributeTableAlias}.version = ezcontentobject.current_version AND";
$datatypeWhereSQL .= eZContentLanguage::sqlFilter($contentAttributeTableAlias, 'ezcontentobject');
$dataType = eZDataType::create(eZContentObjectTreeNodeNoLanguage::dataTypeByClassAttributeID($classAttributeID));
if (is_object($dataType) && $dataType->customSorting()) {
$params = array();
$params['contentobject_attr_id'] = "{$contentAttributeTableAlias}.id";
$params['contentobject_attr_version'] = "{$contentAttributeTableAlias}.version";
$params['table_alias_suffix'] = "{$attributeJoinCount}";
$sql = $dataType->customSortingSQL($params);
$datatypeFromSQL .= " INNER JOIN {$sql['from']} ON ({$sql['where']})";
$datatypeSortingFieldSQL = $sql['sorting_field'];
$datatypeSortingTargetSQL .= ', ' . $sql['sorting_field'];
} else {
// Look up datatype for standard sorting
$sortKeyType = eZContentObjectTreeNodeNoLanguage::sortKeyByClassAttributeID($classAttributeID);
switch ($sortKeyType) {
case 'string':
$sortKey = 'sort_key_string';
break;
case 'int':
default:
$sortKey = 'sort_key_int';
break;
}
$datatypeSortingFieldSQL = "a{$attributeJoinCount}.{$sortKey}";
$datatypeSortingTargetSQL .= ', ' . $datatypeSortingFieldSQL;
}
$sortingFields .= "{$datatypeSortingFieldSQL}";
$attributeFromSQL .= " INNER JOIN {$datatypeFromSQL} ON ({$datatypeWhereSQL})";
$attributeJoinCount++;
break;
case 'state':
$stateGroupID = $sortBy[2];
if (!is_numeric($stateGroupID)) {
$stateGroup = eZContentObjectStateGroup::fetchByIdentifier($stateGroupID);
if ($stateGroup) {
$stateGroupID = $stateGroup->attribute('id');
} else {
eZDebug::writeError("Unknown content object state group '{$stateGroupID}'");
continue 2;
}
}
$stateAlias = "s{$stateJoinCount}";
$stateLinkAlias = "sl{$stateJoinCount}";
$sortingFields .= "{$stateAlias}.priority";
$datatypeSortingTargetSQL .= ", {$stateAlias}.priority";
$attributeFromSQL .= " INNER JOIN ezcobj_state {$stateAlias} ON ({$stateAlias}.group_id = {$stateGroupID})" . " INNER JOIN ezcobj_state_link {$stateLinkAlias}" . " ON ({$stateLinkAlias}.contentobject_id = ezcontentobject.id AND {$stateLinkAlias}.contentobject_state_id = {$stateAlias}.id)";
break;
default:
if ($allowCustomColumns) {
$sortingFields .= $sortField;
} else {
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;
}
}
$sortingInfo['sortCount'] = $sortCount;
$sortingInfo['sortingFields'] = $sortingFields;
$sortingInfo['attributeTargetSQL'] = $datatypeSortingTargetSQL;
$sortingInfo['attributeJoinCount'] = $attributeJoinCount;
$sortingInfo['attributeFromSQL'] = $attributeFromSQL;
$sortingInfo['attributeWhereSQL'] = $attributeWhereSQL;
} else {
if ($sortList === array()) {
$sortingInfo['sortingFields'] = '';
}
}
return $sortingInfo;
}