Multi-class single attribute fetch for eZ Publish

It is a reasonably common use case to fetch a list of content objects based on a common attribute implemented across multiple content classes. For example, to fetch all articles, blog posts and comments published by a given author. Certainly one could do multiple fetches and then massage the result sets together, but that's a lot of work. In this post I'll create a custom template operator (which is not the same as a custom fetch function, although the latter might be a better extension if one were a fan of doing things 'semantically') The operator will do the fetch with one SQL query. And I hope to shine some light on the eZ Publish db schema around objects, along the way.

Below is the SQL wrapped in a PHP function. The function provides some ability to query on different classes, attributes, types of attributes and to pick the subtree to search.

You're going to anticipate that doing this on a very big subtree (100,000 objects) could have performance issues if you don't take care:

• Use LIMIT and OFFSET.
• You could certainly remove most of the SELECT fields.
• You could also optimize out the join to ezcontentclass_attribute if you have the content class ids in hand.

Other improvements would include - and I mention these to anticipate and agree in advance with comments:

•  the ability to search on multiple subtrees
• and to sort on text fields.

I like to include newlines in my SQL so that I can dump them and read them easily. The DB doesn't care about them, so they're a no-penalty improvement for maintenance.

Last thing about this function: I haven't made any effort to check the inputs. I'd do that if this function were sitting in some API that I was offering to other developers but since this is for internal consumption I've opted for clean.

And a disclaimer: Writing custom code directly against the DB schema has some risk to it. Because the DB is considered "internal" to eZ Publish, there are people who consider it changeable. This part of the schema isn't super likely to change since it's central to everything, but it is something to keep in mind.

function createFetchSQL( $rootPath, $attrName, $classNames, $filterType, $filterComp, $limit, $offset )
{
    $or = "";

    $query = "";
    $query .= "\n select distinct";
    $query .= "\n  ezcontentobject.name as OBJECT_NAME";
    $query .= "\n  , ezcontentclass.identifier as CLASS_NAME";
    $query .= "\n  , ezcontentclass_attribute.identifier as ATTRIBUTE_NAME";
    $query .= "\n  , ezcontentobject_attribute.data_int as VALUE_INT";
    $query .= "\n  , ezcontentobject_attribute.data_text as VALUE_TEXT";
    $query .= "\n  , ezcontentobject_tree.node_id as NODE_ID";
    $query .= "\n  , ezcontentobject.id as CONTENT_OBJECT_ID";
    $query .= "\n  , ezcontentobject_tree.path_string as PATH";
    $query .= "\n from ezcontentclass";

    // the object of interest
    $query .= "\n join ezcontentobject";
    $query .= "\n   on ezcontentobject.contentclass_id = ezcontentclass.id";

    // the name of the attribute
    $query .= "\n  join ezcontentclass_attribute";
    $query .= "\n   on ezcontentclass_attribute.contentclass_id = ezcontentclass.id";

    // the published version of the object
    $query .= "\n  join ezcontentobject_version";
    $query .= "\n   on (ezcontentobject_version.contentobject_id = ezcontentobject.id AND ezcontentobject_version.status = 1 )";

    // the value of the attribute (with the current version)
    $query .= "\n  join ezcontentobject_attribute";
    $query .= "\n   on (ezcontentobject_attribute.contentobject_id = ezcontentobject.id AND ezcontentobject_attribute.version = ezcontentobject_version.version )";

    // the name of the content class
    $query .= "\n  join ezcontentclass_name ";
    $query .= "\n   on ezcontentclass_name.contentclass_id = ezcontentclass.id";

    // the subtree to search
    $query .= "\n  join ezcontentobject_tree";
    $query .= "\n   on ezcontentobject_tree.contentobject_id = ezcontentobject.id";

    $query .= "\n where ";

    // trick to remove the degeneracy in c.o.--c.o.attr.
    $query .= "\n  ezcontentobject_attribute.contentclassattribute_id = ezcontentclass_attribute.id";

    // search indicated subtree
    $query .= "\n  and ezcontentobject_tree.path_string like '" . $rootPath . "%'";

    // list of classes to include
    $query .= "\n  and ";
    $query .= "\n   (";
    foreach( $classNames as $class )
    {
        $query .= $or . " ezcontentclass.identifier = \"" . $class . "\" ";
        $or = "or";
    }
    $query .= "   )";

    // the common attribute
    $query .= "\n  and ";
    $query .= "\n   ezcontentclass_attribute.identifier = \"" . $attrName . "\"";

    // the filter on the attribute
    $query .= "\n  and";
    switch( $filterType )
    {
        case "like":
            $query .= "\n   ezcontentobject_attribute.data_text like \"%" . $filterComp . "%\"";
            break;

        case "=":
        case "<":
        case ">":
        case "<=":
        case ">=":
            $query .= "\n   ezcontentobject_attribute.data_int " . $filterType . " " . $filterComp;
            break;

       default:
            // should never get here - this will cause bad SQL
            break;
    }

    $query .= "\n order by ezcontentobject_attribute.data_int DESC";
    if( 0 != $limit )
    {
        $query .= "\n limit " . $limit;
        $query .= "\n offset " . $offset;
    }
    return $query;
}

That's really the guts of any template operator. But just for completeness, I'll run through the rest of the code.

Within extension / multiclassfetch / autoloads / eztemplateautoload.php you need:

<?php

$eZTemplateOperatorArray = array();

$eZTemplateOperatorArray[] = array( "script" => "extension/multiclassfetch/classes/multiclassfetch.php",
                                    "class" => "MultiClassFetch",
                                    "operator_names" => array( "fetch_multi_classes", "fetch_count_multi_classes" ) );
?>

Notice that there is a second function defined which simply returns a count. This is pretty useful as is enables placing most of the smarts in the template, where it's cheap to manage.

Within extension / multiclassfetch / classes / multiclassfetch.php you need:

<?php
/*
 * implement: "fetch_multi_classes", "fetch_count_multi_classes"
 *
 * note: setting the limit to 0 disables the offset/limit paging function
 *
 * note: path looks like: /1/2/160/216/
 *
 * note: sorts on the numeric representation of the common attribute
 *
 */
class MultiClassFetch
{
 /*!
 \return an array with the template operator name.
 */
 function operatorList()
 {
 return array( "fetch_multi_classes", "fetch_count_multi_classes" );
 }
 /*!
 \return true to tell the template engine that the parameter list exists per operator type,
 this is needed for operator classes that have multiple operators.
 */
 function namedParameterPerOperator()
 {
 return true;
 }
 /*!
 See eZTemplateOperator::namedParameterList
 */
 function namedParameterList()
 {
 return
 array(
 'fetch_multi_classes' => array(
 "root_path" => array( 'type' => 'string', 'required' => true )
 , "attribute_name" => array( 'type' => 'string', 'required' => true )
 , "content_class_names" => array( 'type' => 'array', 'required' => true )
 , "filter_type" => array( 'type' => 'string', 'required' => true )
 , "filter_compare" => array( 'type' => 'string', 'required' => true )
 , "offset" => array( 'type' => 'integer', 'required' => false, "default" => 0 )
 , "limit" => array( 'type' => 'integer', 'required' => false, "default" => 25 )
 )
 , "fetch_count_multi_classes" => array(
 "root_path" => array( 'type' => 'string', 'required' => true )
 , "attribute_name" => array( 'type' => 'string', 'required' => true )
 , "content_class_names" => array( 'type' => 'array', 'required' => true )
 , "filter_type" => array( 'type' => 'string', 'required' => true )
 , "filter_compare" => array( 'type' => 'string', 'required' => true )
 )
 );
 }
 /*!
 Executes the PHP function for the operator cleanup and modifies \a $operatorValue.
 */
 function modify( $tpl,
 $operatorName,
 $operatorParameters,
 $rootNamespace,
 $currentNamespace,
 &$operatorValue,
 $namedParameters )
 {
 switch ( $operatorName )
 {
 case 'fetch_multi_classes':
 $operatorValue = MultiClassFetch::fetchOverClassesOnAttribute(
 $namedParameters[ "root_path" ]
 , $namedParameters[ "attribute_name" ]
 , $namedParameters[ "content_class_names" ]
 , $namedParameters[ "filter_type" ]
 , $namedParameters[ "filter_compare" ]
 , $namedParameters[ "offset" ]
 , $namedParameters[ "limit" ] );
 break;
 case "fetch_count_multi_classes":
 $operatorValue = MultiClassFetch::fetchCountOverClassesOnAttribute(
 $namedParameters[ "root_path" ]
 , $namedParameters[ "attribute_name" ]
 , $namedParameters[ "content_class_names" ]
 , $namedParameters[ "filter_type" ]
 , $namedParameters[ "filter_compare" ] );
 break;
 default:
 break;
 }
 }
 /*
 * impl
 *
 * generate fetch SQL
 *
 */
 function createFetchSQL( $rootPath, $attrName, $classNames, $filterType, $filterComp, $limit, $offset )
 {
    // ... as above ...
 }
 /*
 * impl
 *
 * generate fetch SQL
 *
 */
 function createCountSQL( $rootPath, $attrName, $classNames, $filterType, $filterComp )
 {
 $query = "";
 $or = "";
 $query .= " SELECT count( distinct ezcontentobject.id ) AS COUNT ";
 $query .= "\n from ezcontentclass";
 // the object of interest
 $query .= "\n join ezcontentobject";
 $query .= "\n on ezcontentobject.contentclass_id = ezcontentclass.id";
 // the name of the attribute
 $query .= "\n join ezcontentclass_attribute";
 $query .= "\n on ezcontentclass_attribute.contentclass_id = ezcontentclass.id";
 // the published version of the object
 $query .= "\n join ezcontentobject_version";
 $query .= "\n on (ezcontentobject_version.contentobject_id = ezcontentobject.id AND ezcontentobject_version.status = 1 )";
 // the value of the attribute (with the current version)
 $query .= "\n join ezcontentobject_attribute";
 $query
.= "\n on (ezcontentobject_attribute.contentobject_id =
ezcontentobject.id AND ezcontentobject_attribute.version =
ezcontentobject_version.version )";
 // the name of the content class
 $query .= "\n join ezcontentclass_name ";
 $query .= "\n on ezcontentclass_name.contentclass_id = ezcontentclass.id";
 // the subtree to search
 $query .= "\n join ezcontentobject_tree";
 $query .= "\n on ezcontentobject_tree.contentobject_id = ezcontentobject.id";
 $query .= "\n where ";
 // a trick to remove the degeneracy in c.o.--c.o.attr.
 $query .= "\n ezcontentobject_attribute.contentclassattribute_id = ezcontentclass_attribute.id";
 // search indicated subtree
 $query .= "\n and ezcontentobject_tree.path_string like '" . $rootPath . "%'";
 // list of classes to include
 $query .= "\n and ";
 $query .= " (";
 foreach( $classNames as $class )
 {
 $query .= $or . " ezcontentclass.identifier = \"" . $class . "\" ";
 $or = "or";
 }
 $query .= " )";
 // the common attribute
 $query .= "\n and ";
 $query .= "\n ezcontentclass_attribute.identifier = \"" . $attrName . "\"";
 // the filter on the attribute
 $query .= "\n and";
 switch( $filterType )
 {
 case "like":
 $query .= " ezcontentobject_attribute.data_text like \"%" . $filterComp . "%\"";
 break;
 case "=":
 case "<":
 case ">":
 case "<=":
 case ">=":
 $query .= " ezcontentobject_attribute.data_int " . $filterType . " " . $filterComp;
 break;
 default:
 // should never get here - this will cause bad SQL
 break;
 }
 return $query;
 }
 /*
 * impl
 *
 * fetch list of nodes based on filter on common attribute over multiple
 * content classes
 *
 */
 function fetchOverClassesOnAttribute( $rootPath, $attrName, $classNames, $filterType, $filterComp, $offset, $limit )
 {
 $db = eZDB::instance();
 $query = MultiClassFetch::createFetchSQL( $rootPath, $attrName, $classNames, $filterType, $filterComp, $limit, $offset );
 $rows = $db->arrayQuery( $query );
 return $rows;
 }
 /*
 * get the count of objects ...
 *
 */
 function fetchCountOverClassesOnAttribute( $rootPath, $attrName, $classNames, $filterType, $filterComp )
 {
 $db = eZDB::instance();
 $query = MultiClassFetch::createCountSQL( $rootPath, $attrName, $classNames, $filterType, $filterComp );
 $rows = $db->arrayQuery( $query );
 return $rows[ 0 ][ "COUNT" ];
 }
}

Within extension / multiclassfetch / settings / site.ini.append.php you need:

<?php /*

[TemplateSettings]
ExtensionAutoloadPath[]=multiclassfetch

*/ ?>

Then, enable the extension where you need it and call it from your template like:

{def $syndicateList = fetch_multi_classes( "/1/2/", "home_page_display", array( "news", "article" ), ">", "0", 0, 0 )}

<div id="main-news-top"></div>
<div id="main-news-middle">
    <div id="main-box-news-1">
        {attribute_view_gui attribute=$node.data_map.left_panel_news}
    </div>

    <div id="main-box-news-2">
        <h1>Updates</h1>
        {if 0|eq($syndicateList|count())}
            There are currently no news items.
        {else}
            {def $itemNode=null}
            {foreach $syndicateList as $listItem}
                {set $itemNode=fetch(content,node,hash(node_id,$listItem.NODE_ID))}
                {node_view_gui content_node=$itemNode view=line}
            {/foreach}
        {/if}
    </div>
</div>
<div id="main-news-bottom"></div>

And that's it.

Ah ... the sweetness that are custom template operators. Basically, with these things you can set to do anything from your templates. And to prove that point, my next post will be about implementing rather fancy SSO (Single Sign On) handlers as custom template operators! And these'll be no shirkers - they'll even support custom logging.