Forums / Developer / Fetch nodes NOT in a given language

Fetch nodes NOT in a given language

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>

eZ debug

Timing: Jan 17 2025 23:49:19
Script start
Timing: Jan 17 2025 23:49:19
Module start 'content'
Timing: Jan 17 2025 23:49:20
Module end 'content'
Timing: Jan 17 2025 23:49:20
Script end

Main resources:

Total runtime1.1177 sec
Peak memory usage4,096.0000 KB
Database Queries207

Timing points:

CheckpointStart (sec)Duration (sec)Memory at start (KB)Memory used (KB)
Script start 0.00000.0059 588.8516180.8359
Module start 'content' 0.00590.9878 769.6875617.3672
Module end 'content' 0.99370.1240 1,387.0547345.0547
Script end 1.1177  1,732.1094 

Time accumulators:

 Accumulator Duration (sec) Duration (%) Count Average (sec)
Ini load
Load cache0.00390.3466210.0002
Check MTime0.00140.1279210.0001
Mysql Total
Database connection0.00070.060910.0007
Mysqli_queries1.031392.26192070.0050
Looping result0.00250.22362050.0000
Template Total1.089497.520.5447
Template load0.00200.176420.0010
Template processing1.087497.284820.5437
Template load and register function0.00010.009210.0001
states
state_id_array0.00100.086010.0010
state_identifier_array0.00070.065320.0004
Override
Cache load0.00180.1588510.0000
Sytem overhead
Fetch class attribute can translate value0.00130.118630.0004
Fetch class attribute name0.00100.090590.0001
XML
Image XML parsing0.00130.114930.0004
class_abstraction
Instantiating content class attribute0.00000.0030140.0000
General
dbfile0.00210.1851330.0001
String conversion0.00000.000530.0000
Note: percentages do not add up to 100% because some accumulators overlap

CSS/JS files loaded with "ezjscPacker" during request:

CacheTypePacklevelSourceFiles
CSS0extension/community/design/community/stylesheets/ext/jquery.autocomplete.css
extension/community_design/design/suncana/stylesheets/scrollbars.css
extension/community_design/design/suncana/stylesheets/tabs.css
extension/community_design/design/suncana/stylesheets/roadmap.css
extension/community_design/design/suncana/stylesheets/content.css
extension/community_design/design/suncana/stylesheets/star-rating.css
extension/community_design/design/suncana/stylesheets/syntax_and_custom_tags.css
extension/community_design/design/suncana/stylesheets/buttons.css
extension/community_design/design/suncana/stylesheets/tweetbox.css
extension/community_design/design/suncana/stylesheets/jquery.fancybox-1.3.4.css
extension/bcsmoothgallery/design/standard/stylesheets/magnific-popup.css
extension/sevenx/design/simple/stylesheets/star_rating.css
extension/sevenx/design/simple/stylesheets/libs/fontawesome/css/all.min.css
extension/sevenx/design/simple/stylesheets/main.v02.css
extension/sevenx/design/simple/stylesheets/main.v02.res.css
JS0extension/ezjscore/design/standard/lib/yui/3.17.2/build/yui/yui-min.js
extension/ezjscore/design/standard/javascript/jquery-3.7.0.min.js
extension/community_design/design/suncana/javascript/jquery.ui.core.min.js
extension/community_design/design/suncana/javascript/jquery.ui.widget.min.js
extension/community_design/design/suncana/javascript/jquery.easing.1.3.js
extension/community_design/design/suncana/javascript/jquery.ui.tabs.js
extension/community_design/design/suncana/javascript/jquery.hoverIntent.min.js
extension/community_design/design/suncana/javascript/jquery.popmenu.js
extension/community_design/design/suncana/javascript/jScrollPane.js
extension/community_design/design/suncana/javascript/jquery.mousewheel.js
extension/community_design/design/suncana/javascript/jquery.cycle.all.js
extension/sevenx/design/simple/javascript/jquery.scrollTo.js
extension/community_design/design/suncana/javascript/jquery.cookie.js
extension/community_design/design/suncana/javascript/ezstarrating_jquery.js
extension/community_design/design/suncana/javascript/jquery.initboxes.js
extension/community_design/design/suncana/javascript/app.js
extension/community_design/design/suncana/javascript/twitterwidget.js
extension/community_design/design/suncana/javascript/community.js
extension/community_design/design/suncana/javascript/roadmap.js
extension/community_design/design/suncana/javascript/ez.js
extension/community_design/design/suncana/javascript/ezshareevents.js
extension/sevenx/design/simple/javascript/main.js

Templates used to render the page:

UsageRequested templateTemplateTemplate loadedEditOverride
1node/view/full.tplfull/forum_topic.tplextension/sevenx/design/simple/override/templates/full/forum_topic.tplEdit templateOverride template
7content/datatype/view/ezimage.tpl<No override>extension/sevenx/design/simple/templates/content/datatype/view/ezimage.tplEdit templateOverride template
7content/datatype/view/ezxmltext.tpl<No override>extension/community_design/design/suncana/templates/content/datatype/view/ezxmltext.tplEdit templateOverride template
13content/datatype/view/ezxmltags/paragraph.tpl<No override>extension/ezwebin/design/ezwebin/templates/content/datatype/view/ezxmltags/paragraph.tplEdit templateOverride template
1content/datatype/view/ezxmltags/link.tpl<No override>design/standard/templates/content/datatype/view/ezxmltags/link.tplEdit templateOverride template
5content/datatype/view/ezxmltags/literal.tpl<No override>extension/community/design/standard/templates/content/datatype/view/ezxmltags/literal.tplEdit templateOverride template
1content/datatype/view/ezxmltags/quote.tpldatatype/ezxmltext/quote.tplextension/ezwebin/design/ezwebin/override/templates/datatype/ezxmltext/quote.tplEdit templateOverride template
1content/datatype/view/ezxmltags/line.tpl<No override>design/standard/templates/content/datatype/view/ezxmltags/line.tplEdit templateOverride template
1pagelayout.tpl<No override>extension/sevenx/design/simple/templates/pagelayout.tplEdit templateOverride template
 Number of times templates used: 37
 Number of unique templates used: 9

Time used to render debug report: 0.0001 secs