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
558175
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 'visibility':
                        {
                            $sortingFields .= $treeTableName . '.is_invisible';
                        } break;
                        case 'name':
                        {
                            $sortingFields .= 'ezcontentobject_name.name';
                        } break;
                        case 'attribute':
                        {
                            $classAttributeID = $sortBy[2];
                            if ( !is_numeric( $classAttributeID ) )
                                $classAttributeID = eZContentObjectTreeNode::classAttributeIDByIdentifier( $classAttributeID );


                            $contentAttributeTableAlias = "a$attributeJoinCount";
                            $datatypeFromSQL = "ezcontentobject_attribute $contentAttributeTableAlias";
                            $datatypeWhereSQL = "
                                   $contentAttributeTableAlias.contentobject_id = ezcontentobject.id AND
                                   $contentAttributeTableAlias.contentclassattribute_id = $classAttributeID AND
                                   $contentAttributeTableAlias.version = ezcontentobject.current_version AND";
                            $datatypeWhereSQL .= eZContentLanguage::sqlFilter( $contentAttributeTableAlias, 'ezcontentobject' );

                            $dataType = eZDataType::create( eZContentObjectTreeNode::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 = eZContentObjectTreeNode::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 2;
                            }
                        }
                    }
                    $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;
    }
1350043
protected function getSortableColumns()
    {
        if ($this->sortableColumns !== null) {
            return $this->sortableColumns;
        }

        $columns = $this->getColumns();
        $sortable = array_filter($columns, function ($column) {
            return $column->sortable;
        });

        return $this->sortableColumns = $sortable;
    }
747532
private function orderQueryBuilderBy(QueryBuilder $queryBuilder, TableInterface $table, $sort)
    {
        if (!is_array($sort)) {
            $sort = array($sort => $table->getOption('order', 'DESC'));
        }

        foreach ($sort as $fieldName => $order) {
            // Check field is sortable
            $field = $table->getField($fieldName);
            if (!$field->getOption('sortable')) {
                throw new InvalidArgumentException("Field $fieldName is not sortable in table " . $table->getName());
            }

            foreach ((array)$field->getOption('order_path') as $orderBy) {
                $queryBuilder->addOrderBy($this->fixQueryPath($orderBy, $field), $order);
            }
        }
    }
1235873
public function search_records(array $searchFields, array $requiredFields, $orderBy=null, $limit=null, $offset=null) {
		$data = array();
		if(is_array($searchFields) && is_array($requiredFields)) {
			$limitOffsetStr = '';
			if(is_numeric($limit) && $limit > 0) {
				$limitOffsetStr = ' LIMIT '. $limit;
				
				//using an offset without a limit seems silly...
				if(is_numeric($limitOffsetStr) && $offset > 0) {
					$limitOffsetStr .= ' OFFSET '. $offset;
				}
			}
			
			$filterStr = '';
			$required = '';
			$params = array();
			foreach($searchFields as $f=>$v) {
				$filterStr = ToolBox::create_list($filterStr, '(lower('. $f .') LIKE :'. $f .' OR :'. $f .' IS NULL)', ' AND ');
				$params[$f] = '%'. $v .'%';
			}
			foreach($requiredFields as $f=>$v) {
				$required = ToolBox::create_list($required, $f .'=:'. $f, ', ');
				$params[$f] = $v;
			}
			
			$sql = 'SELECT * FROM '. $this->tableName .' WHERE ('. $filterStr .') AND '. $required . $orderByStr . $limitOffsetStr;
			
			$orderByStr = ' ORDER BY '. $this->pkeyField;
			if(is_string($orderBy) && strlen($orderBy)) {
				$orderByStr = ' ORDER BY '. $orderBy;
			}
			
			try {
				$data = $this->dbObj->run_query($sql, $params);
			}
			catch(Exception $e) {
				throw new Exception(__METHOD__ .': error while performing search: '. $e->getMessage());
			}
		}
		else {
			throw new Exception(__METHOD__ .': missing searchFields or requiredFields');
		}
		
		return($data);
	}
1018695
public static String getSortClause (String sql,Criteria crt) {
        if (null == sql ) return null;

        if (null != crt && null != crt.getSort()) {
            String[] sorts = crt.getSort().split(",");
            String s = "";
            for(String sort: sorts) {
                if(s.length()==0) 
                    s = sort + " "+crt.getDir();
                else
                    s = s + " , " +sort + " "+crt.getDir();
            }
            return sql + " ORDER BY " + s;
        }
        return sql;
    }
720913
public function getSortTable(DataList $list)
    {
        $field = $this->getSortField();

        if ($list instanceof ManyManyList) {
            $extra = $list->getExtraFields();
            $table = $list->getJoinTable();

            if ($extra && array_key_exists($field, $extra)) {
                return $table;
            }
        }

        $classes = ClassInfo::dataClassesFor($list->dataClass());

        foreach ($classes as $class) {
            if (singleton($class)->hasOwnTableDatabaseField($field)) {
                return $class;
            }
        }

        throw new Exception("Couldn't find the sort field '$field'");
    }
1359594
protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
    {
        $indexBuffer = [];
        foreach ($tableIndexes as $tableIndex) {
            $tableIndex = array_change_key_case($tableIndex, CASE_LOWER);

            $keyName = strtolower($tableIndex['name']);
            $buffer  = [];

            if (strtolower($tableIndex['is_primary']) === 'p') {
                $keyName              = 'primary';
                $buffer['primary']    = true;
                $buffer['non_unique'] = false;
            } else {
                $buffer['primary']    = false;
                $buffer['non_unique'] = ! $tableIndex['is_unique'];
            }
            $buffer['key_name']    = $keyName;
            $buffer['column_name'] = $this->getQuotedIdentifierName($tableIndex['column_name']);
            $indexBuffer[]         = $buffer;
        }

        return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
    }
907663
public function search(Search $entity, $limit, $offset, $sort_column, $sort_direction)
    {
        $selector = $this->selector
            ->create()
            ->addCountry($entity)
            ->addDateAdd($entity)
            ->addDateEnd($entity)
            ->addDatePremiere($entity)
            ->addGenres($entity)
            ->addLabels($entity)
            ->addName($entity)
            ->addStorage($entity)
            ->addStudio($entity)
            ->addType($entity)
            ->sort($sort_column, $sort_direction)
            ->limit($limit)
            ->offset($offset);

        return [
            'list' => $selector
                ->getQuerySelect()
                ->getQuery()
                ->getResult(),
            'total' => $selector
                ->getQueryTotal()
                ->getQuery()
                ->getSingleScalarResult(),
        ];
    }
318543
public function requestPropertyComparisonStrategy(TreePath $treePath) {
		$queryPoint = $this->findSelectQueryPoint($treePath);
		$comparisonStrategy = null;
		if ($treePath->hasNext()) {
			$comparisonStrategy = $queryPoint->requestPropertyComparisonStrategy($treePath);
		} else {
			$comparisonStrategy = $queryPoint->requestComparisonStrategy();
		}
		
		if ($comparisonStrategy->getType() != ComparisonStrategy::TYPE_COLUMN) {
			throw new QueryConflictException('Property can not be compared in having clause: '
					. TreePath::prettyPropertyStr($treePath->getDones()));
		}
		
		return new ComparisonStrategy(new SelectColumnComparable(
				$comparisonStrategy->getColumnComparable(),
				$this->queryModel->getQueryItemSelect()));
	}
766543
protected function addSorting(QueryBuilder $queryBuilder, array $sorting = [])
    {
        foreach ($sorting as $field => $sortOrder) {
            if (!$this->isFieldSortable($field)) {
                // Skip: invalid field
                continue;
            }
            if (!$this->isSortOrderValid($sortOrder)) {
                // Skip: invalid sort order
                continue;
            }

            $methodName = sprintf('add%sSorting', ucfirst($field));
            if (method_exists($this, $methodName)) {
                $this->$methodName($queryBuilder, $sortOrder);
                continue;
            }

            $queryBuilder->addOrderBy($this->addEntityAlias($field), strtolower($sortOrder));
        }

        return $queryBuilder;
    }
1219445
public function findAllForDataTables($search, $sortColumn, $sortDirection)
    {
        // select
        $qb = $this->getQueryBuilder(false)
            ->select('p.id, p.name, c.id categoryId, c.name categoryName, b.id brandId, b.name brandName, 
                      p.price, p.stock, p.active, p.available, p.highlighted, p.freeTransport, actor.id actorId, actor.name actorName');

        // join
        $qb->leftJoin('p.category', 'c')
//            ->join('c.parentCategory', 'pc')
            ->leftJoin('p.brand', 'b')
            ->leftJoin('p.actor', 'actor')
            ;

        // search
        if (!empty($search)) {
            $qb->andWhere('p.name LIKE :search OR
                        c.name LIKE :search OR
                        b.name LIKE :search OR 
                        actor.name LIKE :search' )
                ->setParameter('search', '%'.$search.'%');
        }

        // sort by column
        switch($sortColumn) {
            case 0:
                $qb->orderBy('actor.name', $sortDirection);
                break;
            case 1:
                $qb->orderBy('p.name', $sortDirection);
                break;
            case 3:
                $qb->orderBy('c.name', $sortDirection);
                break;
            case 4:
                $qb->orderBy('b.name', $sortDirection);
                break;
            case 5:
                $qb->orderBy('p.price', $sortDirection);
                break;
            case 7:
                $qb->orderBy('p.stock', $sortDirection);
                break;
            case 8:
                $qb->orderBy('p.active', $sortDirection);
                break;
            case 9:
                $qb->orderBy('p.available', $sortDirection);
                break;
            case 10:
                $qb->orderBy('p.highlighted', $sortDirection);
                break;
            case 11:
                $qb->orderBy('p.freeTransport', $sortDirection);
                break;
        }

        return $qb->getQuery();
    }
1331207
protected function pre_filter_posts_columns( $columns, $sortable = false ) {
		// for cocoon
		unset( $columns['thumbnail'] );
		unset( $columns['memo'] );

		$_columns = $this->app->db->get_columns( $this->get_related_table_name() );
		foreach ( $this->get_manage_posts_columns() as $k => $v ) {
			if ( $sortable && ( ! is_array( $v ) || empty( $v['sortable'] ) ) ) {
				continue;
			}
			$key = $this->get_post_type() . '-' . $k;
			if ( $sortable ) {
				$columns[ $key ] = [ $key, ! empty( $v['desc'] ) ];
				continue;
			}

			$name = isset( $_columns[ $k ] ) ? $this->table_column_to_name( $k, $_columns ) : '';
			if ( is_array( $v ) ) {
				if ( isset( $v['name'] ) ) {
					$name = $v['name'];
				}
				$columns[ $key ] = $name;
			} elseif ( ! $sortable ) {
				$columns[ $key ] = empty( $name ) ? $k : $name;
			}
		}

		return $columns;
	}
1007944
public function applySorts(string $tableOrAlias, iterable $sorts): self
    {
        foreach ($sorts as $columnName => $isAsc) {
            assert(is_string($columnName) === true && is_bool($isAsc) === true);
            $fullColumnName = $this->quoteDoubleIdentifier($tableOrAlias, $columnName);
            $this->addOrderBy($fullColumnName, $isAsc === true ? 'ASC' : 'DESC');
        }

        return $this;
    }
313617
public function getSort($sortName): ?OrderBy
    {
        [$name, $sorts] = $this->getArrayAndKeyName($sortName, 'sorts');

        return array_first($sorts, function ($sort) use ($name) {
            return $sort->getName() === $name;
        });
    }
221485
protected function checkSortableColumnHasAttribute(TableListColumn $column): void
    {
        if (! $column->getAttribute('attribute') && $column->getAttribute('isSortableColumn')) {
            $errorMessage = 'One of the sortable columns has no defined attribute. You have to define a column '
                            . 'attribute for each sortable columns by setting a string parameter in the '
                            . '« addColumn() » method.';
            throw new ErrorException($errorMessage);
        }
    }
201188
public function sort(TreeNodeInterface $node)
    {
        if (!$node->getTree()->hasChildren($node)) {
            return array();
        }

        $parentId = $node->getId();
        $sortMode = $node->getSortMode();
        $sortDir = $node->getSortDir();

        $queryBuilder = $this->connection->createQueryBuilder();
        $queryBuilder
            ->select('t.id')
            ->from('tree', 't')
            ->where($queryBuilder->expr()->eq('t.parent_id', $parentId));

        switch ($sortMode) {
            case TreeInterface::SORT_MODE_TITLE:
                $queryBuilder
                    ->addSelect('evmf.backend AS sorter')
                    ->leftJoin('t', 'element', 'e', 't.type_id = e.eid')
                    ->leftJoin('e', 'element_version', 'ev', 'e.eid = ev.eid AND e.latest_version = ev.version')
                    ->leftJoin('ev', 'element_version_mapped_field', 'evmf', 'ev.id = evmf.element_version_id AND evmf.language = '.$this->connection->quote($this->sortLanguage))
                    ->orderBy('evmf.backend', $sortDir);
                break;

            case TreeInterface::SORT_MODE_CREATEDATE:
                $queryBuilder
                    ->addSelect('t.modify_time AS sorter')
                    ->orderBy('t.modify_time', $sortDir);
                break;

            case TreeInterface::SORT_MODE_PUBLISHDATE:
                $queryBuilder
                    ->addSelect('to.publish_time AS sorter')
                    ->leftJoin('t', 'tree_online', 'to', 'eto.tree_id = et.id AND eto.language = '.$this->connection->quote($this->sortLanguage))
                    ->orderBy('to.publish_time', $sortDir);
                break;

            case TreeInterface::SORT_MODE_CUSTOMDATE:
                $queryBuilder
                    ->addSelect('evmf.date AS sorter')
                    ->leftJoin('t', 'element', 'e', 't.type_id = e.eid')
                    ->leftJoin('e', 'element_version', 'ev', 'e.eid = ev.eid AND e.latest_version = ev.version')
                    ->leftJoin('ev', 'element_version_mapped_field', 'evmf', 'ev.id = evmf.element_version_id AND evmf.language = '.$this->connection->quote($this->sortLanguage))
                    ->orderBy('evmf.date', $sortDir);
                break;

            default:
                $queryBuilder
                    ->addSelect('t.sort AS sorter')
                    ->orderBy('t.sort', 'ASC');
                break;
        }

        $result = $this->connection->fetchAll($queryBuilder->getSQL());
        $result = array_column($result, 'id');

        return $result;
    }
807221
private function sortIdsBySortingColumn($idList)
    {
        // Only one item, what shall we sort here then?
        if (1 === \count($idList)) {
            return $idList;
        }

        static $sorting;
        if (isset($sorting[$cacheKey = $this->get('id') . \implode(',', $idList)])) {
            return $sorting[$cacheKey];
        }

        // Now sort the values according to our sorting column.
        $filter = $this
            ->getTagMetaModel()
            ->getEmptyFilter()
            ->addFilterRule(new StaticIdList($idList));

        $itemIds = $this->getTagMetaModel()->getIdsFromFilter(
            $filter,
            $this->getSortingColumn(),
            0,
            0,
            $this->getSortDirection()
        );

        // Manual sorting of items for checkbox wizard.
        if ($this->isCheckboxWizard() || $this->isTreePicker()) {
            // Keep order from input array, and add non existent ids to the end.
            return $sorting[$cacheKey] = \array_merge(
                // Keep order from input array...
                \array_intersect($idList, $itemIds),
                // ... and add non existent ids to the end.
                \array_diff($idList, $itemIds)
            );
        }
        // Use new order and add non existent ids to the end.
        return $sorting[$cacheKey] = \array_merge($itemIds, \array_diff($idList, $itemIds));
    }