A view of nodes and their translations

Here's a little puzzle: display a table of nodes, each row containing the same content in all available translations.

How would you do it? Stumped, I asked that question on Stack Overflow a few years ago, but never received a satisfactory reply.

Then, a couple of days ago, someone asked me if I had solved it. I hadn't thought of that puzzle since then, but I would have felt bad answering no. So, with 3 years of i18n work under my belt, I decided to give it another go. I did find a solution this time, but it's not optimal, and it required coding. You can find a demo of the solution online. Demo of solution

The basic view

The basic idea is to select the nodes in their source language, then relate each node to all its translations. To do this, the view is built by filtering on Content translation: Source translation, then adding one Content translation: Translations relationship per language. Nodes and translations

Missing untranslated nodes

Now this view works pretty well, except for nodes that are not translated: although they are picked up by the SQL statement, the related nodes in each language are empty, since the tnid is not set for untranslated nodes. That's where I had to write a new join handler that not only joins the source language node to its translation, but also joins it to itself in case there are no translations. The following code silently replaces the standard join handler for Content translation: Translations with this new one:

The resulting query will look like the following - note the JOIN clauses:

SELECT node_node_1.title AS node_node_1_title, 
            node_node_1.nid AS node_node_1_nid, 
            node_node_1.language AS node_node_1_language, 
            node_node_2.title AS node_node_2_title, 
            node_node_2.nid AS node_node_2_nid, 
            node_node_2.language AS node_node_2_language, 
            node_node.title AS node_node_title, 
            node_node.nid AS node_node_nid, 
            node_node.language AS node_node_language, 
            node.created AS node_created
FROM {node} node
LEFT JOIN {node} node_node 
           ON (node.nid = node_node.tnid OR (node_node.tnid = 0 AND node.nid = node_node.nid)) AND node_node.language = 'ar'
LEFT JOIN {node} node_node_1 
           ON (node.nid = node_node_1.tnid OR (node_node_1.tnid = 0 AND node.nid = node_node_1.nid)) AND node_node_1.language = 'en'
LEFT JOIN {node} node_node_2 
           ON (node.nid = node_node_2.tnid OR (node_node_2.tnid = 0 AND node.nid = node_node_2.nid)) AND node_node_2.language = 'fr'
WHERE (( (node.status = '1') AND (node.type IN  ('proverb')) AND (node.tnid = node.nid OR node.tnid = 0) ))

Not optimal: can you do better?

The careful reader will have noticed that there's one extra database JOIN in my solution: the one that joins the source language node to itself. If you have a suggestion to remove it, please let me know!

AttachmentSize
view_of_nodes_and_translations.png104.25 KB
views_of_nodes_and_translations_demo.png22.44 KB

Comments

three left join on node table (the biggest table) will run very very slow . why don't you just fetch the next 10 source nodes (with a pager) and then do another separate query using the the IN operator to fetch other nodes with same tnid and group then together using php .

Not sure if doing N additional queries per page would be faster or slower - needs profiling... But thanks for the suggestion!

you don't do N queries only one query using IN operator . this is why drupal has node_load_multiple and other *_load_multiple api .

Indeed, the query failed for large number of nodes. I optimized the query as detailed in my next post.

Yes, makes sense. I will experiment with this approach and report back here.