Forums / Developer / Fetch nodes NOT in a given language

Fetch nodes NOT in a given language

« 
Previous topic
|
Developer
|
Next topic
 »
Author Message

Hugues Charleux

Friday 10 December 2010 6:52:26 am

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 ?

Hugues Charleux

Friday 10 December 2010 8:52:38 am

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.

Nicolas Pastorino

Friday 10 December 2010 9:11:43 am

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.

--
Nicolas Pastorino
Director Community - eZ
Member of the Community Project Board

eZ Publish Community on twitter: http://twitter.com/ezcommunity

t : http://twitter.com/jeanvoye
G+ : http://plus.tl/jeanvoye

Hugues Charleux

Friday 10 December 2010 9:30:31 am

"

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 |
+----------+

Nicolas Pastorino

Friday 10 December 2010 9:59:46 am

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.'"

--
Nicolas Pastorino
Director Community - eZ
Member of the Community Project Board

eZ Publish Community on twitter: http://twitter.com/ezcommunity

t : http://twitter.com/jeanvoye
G+ : http://plus.tl/jeanvoye

Hugues Charleux

Friday 10 December 2010 1:51:26 pm

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

Hugues Charleux

Tuesday 14 December 2010 6:55:57 am

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

<span>/*.... */</span> <span>$parentNodeID</span> <span>=</span> <span>42</span><span>;</span> <span>$language</span>     <span>=</span> <span>'eng-GB'</span><span>;
// Get the language object to have its ID.
</span>$oLang = eZContentLanguage::fetchByLocale( $language );   <span>/* Fetch parent node to get the path string */</span> <span>$parentNode</span> <span>=</span> eZContentObjectTreeNode<span>::</span><span>fetch</span><span>(</span> <span>$parentNodeID</span><span>,</span> <span>false</span><span>,</span> <span>false</span> <span>)</span><span>;</span>   <span>/*  Get the node ids */</span> <span>$sql</span> <span>=</span> <span>'SELECT t.node_id FROM ezcontentobject_tree t, ezcontentclass c, ezcontentobject o  WHERE      c.identifier NOT IN("'</span><span>.</span> <a href="http://www.php.net/implode" target="ez_no_documentation"><span>implode</span></a><span>(</span><span>'", "'</span><span>,</span> <span>$excludeClasses</span> <span>)</span><span>.</span><span>'" ) 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 "'</span><span>.</span><span>$parentNode</span><span>[</span><span>'path_string'</span><span>]</span><span>.</span><span>'%"     AND NOT ( o.language_mask & '. $oLang->ID .' ) </span><span>'</span><span>;</span>   <span>$db</span> <span>=</span> eZDB<span>::</span><span>instance</span><span>(</span><span>)</span><span>;</span> <span>$result</span> <span>=</span> <span>$db</span><span>-></span><span>arrayQuery</span><span>(</span> <span>$sql</span> <span>)</span><span>;</span>   <span>$nodeIDs</span> <span>=</span> <a href="http://www.php.net/array" target="ez_no_documentation"><span>array</span></a><span>(</span><span>)</span><span>;</span> <span>foreach</span><span>(</span> <span>$result</span> <span>as</span> <span>$row</span> <span>)</span> <span>{</span>     <span>$nodeIDs</span><span>[</span><span>]</span> <span>=</span> <span>$row</span><span>[</span><span>'node_id'</span><span>]</span><span>;</span> <span>}</span>   <span>/* Retrieves eZContentObjectTreeNode objects */</span> <span>$nodes</span> <span>=</span> eZContentObjectTreeNode<span>::</span><span>fetch</span><span>(</span> <span>$nodeIDs</span> <span>)</span><span>;</span>   <span>/*....*/</span>