Filtering from multiple object ID

Question

I'm trying to filter an eZPublish fetch.

I've got a class which has an object relationS attribute. My filter allows to fetch objects of this class filtered by the object relations attribute.

I've started using the Enhanced Object Relations Filtering extension (http://projects.ez.no/index.php/enhanced_object_relation_filter), but this one only works with "AND" conditions and I want "OR".

I was able to edit the file to add the "OR" logic and this is what I got:

<?php
class EORExtendedFilter
{

    function CreateSqlParts( $params )
    {
        $db =& eZDB::instance();

        $tables = array();
        $joins  = array();

        // foreach filtered attribute, we add a join the relation table and filter
        // on the attribute ID + object ID
        foreach( $params as $param )
        {
            if ( !is_array( $param ) )
                continue;

            if ( !is_numeric( $param[0] ) )
            {
                $classAttributeId = eZContentObjectTreeNode::classAttributeIDByIdentifier( $param[0] );
            }
            else
            {
                $classAttributeId = $param[0];
            }

            // multiple objects ids
            if ( is_array($param[1]) )
            {
                foreach( $param[1] as $objectId )
                {
                    $join = array(); // 'OR' logic

                    if ( is_numeric( $objectId ) )
                    {
                        $tableName = 'eor_link_' . $objectId;
                        $tables[] = 'ezcontentobject_link ' . $tableName;

                        $join[]  = $tableName . '.from_contentobject_id = ezcontentobject.id';
                        $join[]  = $tableName . '.from_contentobject_version = ezcontentobject.current_version';
                        $join[]  = $tableName . '.contentclassattribute_id = ' . $classAttributeId;
                        $join[]  = $tableName . '.to_contentobject_id = ' . $objectId;
                    }

                    // 'OR' logic
                    $joins[] = $join;

                }
            }
            // single object id
            else
            {
                $objectId = $param[1];

                $tableName = 'eor_link_' . $objectId;
                $tables[] = 'ezcontentobject_link ' . $tableName;

                $joins[]  = $tableName . '.from_contentobject_id = ezcontentobject.id';
                $joins[]  = $tableName . '.from_contentobject_version = ezcontentobject.current_version';
                $joins[]  = $tableName . '.contentclassattribute_id = ' . $classAttributeId;
                $joins[]  = $tableName . '.to_contentobject_id = ' . $objectId;
            }
        }

        if ( !count( $tables ) or !count( $joins ) )
        {
            $tables = $joins = '';
        }
        else
        {
            $tables = "\n, "    . implode( "\n, ", $tables );

            // 'OR' logic
            if ( is_array($param[1]) )
            {
                $andClauses = array();
                foreach ($joins as $attr)
                {
                    $andClauses[] = implode( " AND\n ", $attr );
                }

                $joins =  implode( " OR\n ", $andClauses ) . " AND\n ";               
            }
            else
            {
                $joins =  implode( " AND\n ", $joins ) . " AND\n ";
            }

        }

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

This extended attribute filter produces this string :

eor_link_126.from_contentobject_id = ezcontentobject.id AND eor_link_126.from_contentobject_version = ezcontentobject.current_version AND eor_link_126.contentclassattribute_id = 537 AND eor_link_126.to_contentobject_id = 126 OR (eor_link_127.from_contentobject_id = ezcontentobject.id AND eor_link_127.from_contentobject_version = ezcontentobject.current_version AND eor_link_127.contentclassattribute_id = 537 AND eor_link_127.to_contentobject_id = 127 AND

I know it misses some parenthesis so I edited the string to test it directly in phpMyAdmin. This is the query:

SELECT DISTINCT
                       ezcontentobject.*,
                       ezcontentobject_tree.*,
                       ezcontentclass.serialized_name_list as class_serialized_name_list,
                       ezcontentclass.identifier as class_identifier,
                       ezcontentclass.is_container as is_container

                       , ezcontentobject_name.name as name,  ezcontentobject_name.real_translation 
                       , a0.sort_key_int

                   FROM
                      ezcontentobject_tree,
                      ezcontentobject,ezcontentclass
                      , ezcontentobject_name 
                      , ezcontentobject_attribute a0


, ezcontentobject_link eor_link_126
, ezcontentobject_link eor_link_127

                   WHERE
                      ezcontentobject_tree.parent_node_id = 1443 and
                      ((eor_link_126.from_contentobject_id = ezcontentobject.id AND
 eor_link_126.from_contentobject_version = ezcontentobject.current_version AND
 eor_link_126.contentclassattribute_id = 537 AND
 eor_link_126.to_contentobject_id = 126) OR
 (eor_link_127.from_contentobject_id = ezcontentobject.id AND
 eor_link_127.from_contentobject_version = ezcontentobject.current_version AND
 eor_link_127.contentclassattribute_id = 537 AND
 eor_link_127.to_contentobject_id = 127)) AND


                                   a0.contentobject_id = ezcontentobject.id AND
                                   a0.contentclassattribute_id = 191 AND
                                   a0.version = ezcontentobject_name.content_version AND
 ( a0.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 2 ) )
   <
     ( a0.language_id & 1 )
   + ( ( a0.language_id & 2 ) )
 ) 
 AND 

                      ezcontentclass.version=0 AND

                      ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
                      ezcontentclass.id = ezcontentobject.contentclass_id AND

                         ezcontentobject.contentclass_id  IN  ( 17 ) AND
                       ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
                                   ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and 
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) )
 ) 

                      AND ezcontentobject_tree.is_invisible = 0


                       AND 
 ezcontentobject.language_mask & 3 > 0 

                 ORDER BY a0.sort_key_int DESC
LIMIT 0, 10

The thing is, when I execute the query above, nothing is returned because the query never stops. The CPU hits 100% and I have to restart mysql which means it's not a syntax issue.

Here is the explain of the query :

If anyone has any clue about this it will be very helpful.

Answer 1

I've solved it by rewriting everything using eZPersistentObject::fetchObjectList().