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