Forums / Developer / Fetch nodes NOT in a given language

"Please Note:

At the specific request of Ibexa we are changing this projects name to "Exponential" or "Exponential (CMS)" effective as of August, 11th 2025.
This project is not associated with the original eZ Publish software or its original developer, eZ Systems or Ibexa".

Fetch nodes NOT in a given language

«

Previous topic

|

|

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>