Fetch nodes NOT in a given language

Friday 10 December 2010 6:52:26 am - 6 replies

6 replies

Author	Message
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>

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