eZ Community » Forums » Developer » Fetch nodes NOT in a given language
expandshrink

Fetch nodes NOT in a given language

Fetch nodes NOT in a given language

Friday 10 December 2010 3:52:26 pm - 6 replies

Hi eZ Community.

I am building a friendly UI in administration for redactors to see which objects have not yet been translated in a given language / subtree.

To do so I want to fetch all objects in the subtree that have no xxx-YY language version.

I can't find a way to achieve this with eZContentObjectTreeNode::subTreeByNodeID() params :

Language related SQL conditions are generated by default, so I think even an extendedAttributeFilter won't do the job.

I think I am going to try to achieve this with a "hand made" SQL Query to get nodes ids and then fetch them individually but that looks awfull...

Does any one has a another lead ?

Friday 10 December 2010 5:52:38 pm

Ok for the record this is how I finally did it :

/*.... */
$parentNodeID = 42;
$language     = 'eng-GB';
 
/* Fetch parent node to get the path string */
$parentNode = eZContentObjectTreeNode::fetch( $parentNodeID, false, false );
 
/*
 Get the node ids
 Query relying on ezcontentobject_name.real_translation with crappy IN( SELECT... ) statement 
 to avoid playing with language_mask (too many combinations)
*/
$sql = 'SELECT t.node_id FROM ezcontentobject_tree t, ezcontentclass c, ezcontentobject o 
WHERE 
    c.identifier NOT IN("'. implode('", "', $excludeClasses ).'" )
AND c.id = o.contentclass_id        
AND o.id = t.contentobject_id    
AND t.node_id = t.main_node_id    
AND t.path_string like "'.$parentNode['path_string'].'%"    
AND o.id NOT IN( 
      SELECT contentobject_id FROM ezcontentobject_name WHERE real_translation = "'.$language.'"
    )
    ';
 
$db = eZDB::instance();
$result = $db->arrayQuery( $sql );
 
$nodeIDs = array();
foreach( $result as $row )
{
    $nodeIDs[] = $row['node_id'];
}
 
/* Retrieves eZContentObjectTreeNode objects */
$nodes = eZContentObjectTreeNode::fetch( $nodeIDs );
 
/*....*/

This will get you all main nodes for objects that don't have an english version under the node whose id is 42.

Modified on Friday 10 December 2010 5:54:22 pm by Hugues Charleux

Friday 10 December 2010 6:11:43 pm

Hi Hugues, and thanks for sharing the solution,

From the top of my head and without having at hand, right now another solution, i can only tell that this

...
AND o.id NOT IN(       
   SELECT contentobject_id FROM ezcontentobject_name WHERE real_translation = "'.$language.'"    )    
';

can be of an issue in case of a large content base, perf-wise.

Friday 10 December 2010 6:30:31 pm

Hi Hugues, and thanks for sharing the solution,

From the top of my head and without having at hand, right now another solution, i can only tell that this

...
AND o.id NOT IN(       
   SELECT contentobject_id FROM ezcontentobject_name WHERE real_translation = "'.$language.'"    )    
';

can be of an issue in case of a large content base, perf-wise.

Exactly ! I should have warn about this potential issue

I was convinced there was a way to make a SQL query that gets records not matching a join but can't figure out how to do this with MySQL.

It's runing fine so far with about 22 000 record in ezcontentobject_name

Another point about ezcontentobject_name's real_translation and content_translation fields : Those two fields looks like storing the same data so I just took one of them... Not a very scientific approach I admit.

mysql> SELECT COUNT(*) FROM ezcontentobject_name WHERE content_translation != real_translation ;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+

Friday 10 December 2010 6:59:46 pm

Why not joining on the ezcontentobject_name table ?

SELECT t.node_id FROM ezcontentobject_tree t, ezcontentclass c, ezcontentobject o, ezcontentobject_name oname 
WHERE c.identifier NOT IN("'. implode('", "', $excludeClasses ).'" )
AND c.id = o.contentclass_id        
AND o.id = t.contentobject_id    
AND t.contentobject_id = oname.contentobject_id
AND t.node_id = t.main_node_id    
AND t.path_string LIKE "'.$parentNode['path_string'].'%"    
AND oname.real_translation != "'.$language.'"

Modified on Friday 10 December 2010 7:00:32 pm by Nicolas Pastorino

Friday 10 December 2010 10:51:26 pm

With this one objects with another locale than $language will be returned, but even if they also have a $language version.

Tuesday 14 December 2010 3:55:57 pm

Ok here is the right way to query, using this time the language mask and no mass destruction sub select.

 /*.... */
$parentNodeID = 42;
$language     = 'eng-GB';
 
// Get the language object to have its ID.
 
$oLang = eZContentLanguage::fetchByLocale( $language );
 
/* Fetch parent node to get the path string */
$parentNode = eZContentObjectTreeNode::fetch( $parentNodeID, false, false );
/*  Get the node ids */ 
$sql = 'SELECT t.node_id     
    FROM ezcontentobject_tree t, ezcontentclass c, ezcontentobject o
    WHERE    c.identifier NOT IN("'. implode('", "', $excludeClasses ).'" )
    AND c.id = o.contentclass_id
    AND o.id = t.contentobject_id
    AND t.node_id = t.main_node_id
    AND t.path_string like "'.$parentNode['path_string'].'%" 
    AND NOT ( o.language_mask & '. $oLang->ID .' )
     ';  
 
$db = eZDB::instance(); $result = $db->arrayQuery( $sql );
 
$nodeIDs = array(); 
foreach( $result as $row ) 
{
    $nodeIDs[] = $row['node_id'];
}
 
/* Retrieves eZContentObjectTreeNode objects */ 
$nodes = eZContentObjectTreeNode::fetch( $nodeIDs );  
 
/*....*/

Modified on Friday 30 December 2011 3:24:50 pm by Hugues Charleux

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from