Requirements

For taking proper advantage of this feature you should know something about:

• writing an extended_attribute_filter (fetch content list)
• SQL
• the eZ Publish database layout

Function

There is an undocumented feature in the eZ Publish fetch content list function for sorting on arbitrary sql statements. On regular fetches this feature is not available. However, when including an extended_attribute_filter parameter in the fetch, eZ Publish allows the user to sort on any arbitrary sql clause.

Use

Add an empty 'extended_attribute_filter' parameter in the fetch like this: 'extended_attribute_filter', hash(). Then add a 'sort_by' parameter, like this: 'sort_by', $sql_statement.

The sql string in the $sql_statement variable is inserted directly in the ORDER BY sql clause. Tables included by a regular fetch content list are:

• ezcontentobject_tree
• ezcontentobject
• ezcontentobject_name
• ezcontentclass

which means that any fields within these tables can be used in the $sql_statement string. If a sort_by attribute is involved, the ezcontentobject_attribute table are also included.

Furthermore, if required one can add other tables in an extended_attribute_filter's code, giving you access to sort on any field or combination of fields in the database.

Examples

fetch( 'content', 'list', hash( 'parent_node_id', 2,
                                'sort_by', array( 'ezcontentclass.identifier ', true() ),
                                'extended_attribute_filter', hash()
    ) )

This example will sort the resulting node list by the class identifier string. Note that this will not work if the extended_attribute_filter parameter is not included.

fetch( 'content', 'list', hash( 'parent_node_id', 2,
                                'sort_by', array( ' RAND() ', true() ),
                                'extended_attribute_filter', hash()
    ) )

This example will give a randomized list of nodes. This is not possible to do without the extended sort feature.

fetch( 'content', 'list', hash( 'parent_node_id', 2,
                                'sort_by', array( array( 'cast( a0.data_text as unsigned )', true() ),
                                                  array( 'attribute', true(), 'article/select_attribute' ) ),
                                'extended_attribute_filter', hash()
    ) )
 

This sorts an attribute of the selection datatype in the article class by the selection id as a number (and not by selection id as a string, as is default for this datatype, which makes the sort fail on id's above 9). The second sort element tricks the fetch into including the ezcontentobject_attribute table in the sql, named as a0, but will not have any effect on the actual sorting.

fetch( 'content', 'list', hash( 'parent_node_id', 2,
                                'sort_by', array( 'obj.name ', true() ),
                                'extended_attribute_filter', hash( 'id', 'ObjectRelationSortFilter',
                                                                   'params', hash( 'relation_attribute_id', 337,
                                                                                   'related_class_attribute_id', 343 ) )
    ) )

Here is an example on how to sort the nodes by an attribute of the related objects in an object relations attribute.

This example requires an extended_attribute_filter to include the right tables, the main function of the code for this follows...see the extended_attribute_filter documentation for setting up this.

function sqlObjectRelationSort($params)
    {
        $sqlTables= ", ezcontentobject_tree AS node
                LEFT JOIN ( ezcontentobject_link AS link, ezcontentobject AS obj, ezcontentobject_attribute AS attr )
                ON ( node.contentobject_id = link.from_contentobject_id AND
                    node.contentobject_version = link.from_contentobject_version AND
                    link.contentclassattribute_id = {$params['relation_attribute_id']} AND
                    link.to_contentobject_id = obj.id AND
                    obj.id = attr.contentobject_id AND
                    obj.current_version = attr.version AND
                    attr.contentclassattribute_id = {$params['related_class_attribute_id']} )";

        $sqlJoins = ' ezcontentobject_tree.node_id = node.node_id AND ';

        return array( 'tables' => $sqlTables, 'joins'  => $sqlJoins, 'columns' => '' );
    }

As you see, this filter requires two parameters. relation_attribute_id is used to specify the attribute id of the object relation. related_class_attribute_id is used to specify the attribute id of the related object that is to be sorted on. This function requires that all nodes are of the same class, and all related objects in the specified object relation attribute are of the same class.

This can potentially be further expanded to sort on the attributes of the children of the nodes. This is left as an excercise for the reader.

Debugging

Whenever developing extended_attribute_filters or extended sorting, switch on the SQL output in the debug in settings/override/site.ini.append.php:

If you can't immediately see what is wrong, copy the complete sql statement from debug and paste it into your favourite SQL program. It is a lot easier to make the sql work there than to test it against eZ Publish.