Missing filter: Translation missing

I also can't find a way to filter by "untranslated". Much needed in my opinion...

+1 for this feature request - a client just asked the same question (he said it more like "what good is all this if i can't filter this page to show only nodes which have not yet been translated?!" but.. whatever).

I went over the code, and noticed that these 2 existing filters, "up-to-date" and "outdated", are actually checking the value of the "translation" column from the "node" table - and we have there no indication whether this node has any translations or not - we can just tell if it's up-to-date or not, as the module currently does.

I think that the solution for this issue is not only within this great module - since even if code exists here it will have no way to check this info.
I went over the DB tables and could not find a table which actually indicates if a node has translations or not... I think a special query needs to be done in order to find this info from various tables in the DB.

Furthermore, the way his module is build (as stated in the code "This is a fork of node_filters()."), using the translation_overview_build_filter_query() function, it would require some major changes in order to add this option...

The way i see it - the easiest way to get this done is to add another table, or column to an existing table (possibly 'node'), which indicates if a translation exists for that node or not. Another option - add functionality to the translation_overview_build_filter_query() function so that it performs another query and figures that out itself...

In any case - this will require some work. I'll possibly put some energy in it, but would love some feedback before i do ;)

Anyone has an interesting idea about how to solve this?

It is, indeed, much needed functionality.

... or are we just missing something here...? ;)

I think It could check if there is a tnid, if there's not, then it's not translated.
Something like only show nodes where tnid = 0

I don't know how tnids are defined for different languages in the database.

What do you think?

Sadly I'm not actively using the module right now so I don't have time to implement this feature but I'd be happy to review any patches people provide.

As Jolidog noted a translated node is one that has tnid <> 0. The source node is one where tnid = nid.

I've updated the interface to separate the "translation status" from normal "node status".

There are 4 status to select:

  $filters['translation_status'] = array(
    'title' => t('Translation'),
    'options' => array(
      '0' => t('Not translated'),
      '1' => t('Translated'),
      '2' => t('Up-to-date translation'),
      '3' => t('Outdated translation'),
    ),
  );

The patch is attached.

mark #908794: Filter by status is not working a duplicate

exactly my issue. can we bump this into stable/dev release?

Patch is a good start!

I have a similar feature request. Apart from filtering on the status, our client also wishes to filter on "untranslated in language X".

Suppose you have a site with nodes in English, German and French, and you filter on "untranslated in FR", you should see a list of English and German nodes that don't have french translation nodes.

It took me a while to figure this one out, but the SQL should be something like this:

SELECT k.nid, k.title, k.tnid, l.nid, l.tnid, l.language FROM node k
  LEFT OUTER JOIN
    (SELECT n.nid, n.tnid, n.language FROM node n WHERE n.language = 'fr' AND n.tnid != 0) l
  ON k.tnid = l.tnid
WHERE k.language != 'fr' AND l.language IS NULL

In essence, this is the same thing to what happens on the "translation assignments" for a single language, the IS NULL clause is the extra as it will strip out anything that is already translated in that language.

Going to write a patch to make this look a bit nicer.

#7: I've tried your patch. But I'm not sure that the OUTER JOIN for your filters would work. i.e. if I mimic the up-to-date filter (translate = 0) in SQL, my query looks like this:

SELECT k.nid, k.title, k.tnid, d.* FROM node k
  LEFT OUTER JOIN
    node d
  ON 
    d.tnid = k.nid
WHERE (k.nid = k.tnid OR k.tnid = 0) AND k.language <> '' AND k.language IS NOT NULL AND d.translate = 0 AND d.tnid != d.nid

But this will return me duplicate rows: a row for each translation which matches the last two conditions in the where clause.

The original way of doing it isn't right either. It returns a list of source and language neutral nodes which have a status up-to-date or outdated. Yet, we don't want to check the items themselves. We want to filter out source/language neutral nodes which have a outdated/updated translation nodes.

Preferably combinable with my request in #10

Okay. First attempt at a patch.

* I've taken the code from #7 and merged it in. But without the filter logic because it needs revision IMHO.
* Added a missing translation filter per #10.

The biggest issue is the interface. Node filter is great for simple filtering, but theoretically, you could go nuts with translation filter combination. The UI is not designed to cater for complex queries. One could wonder if adding complexity would even be desirable.

So, for one: combining the "missing translation" feature and the "outdated/updated" feature is not possible atm. The original code wouldn't allow you to combine a node which didn't have any translations with the updated/outdate filter (wouldn't make sense). But one could search for all items of which have a missing translation in language X AND outdated translation nodes in all other languages. That's a use case which should be implemented somehow.

As per #11 The query to get all the source nodes where min. 1 translation is "outdated" should look like this:

SELECT k.nid, k.title, k.tnid FROM node k
  WHERE
    k.tnid = k.nid
  AND k.tnid != 0
  AND k.language != 'und'
  AND k.tnid IN (SELECT tnid FROM node WHERE nid != tnid AND translate = 1)

Note 1: I stripped out the language neutral (und) nodes here. Those are not relevant for the query. Should be combinable with my code in #10... I like the idea of putting this in its' own filter since it's an entirely separate property. The filter should only be settable once. I've written a few lines of code that unset the filter as soon as it's set while keeping the data in the session.

Note 2: The "Content in" filter in my previous patch is possibly confusing. It's not entirely clear what you're filtering here: the source nodes or the translated nodes? I don't think we want to show the translation nodes in the overview. Only the source nodes. Yet, the odd time I used this module, I got requests to either explicitly remove them or leave them in.

Setting this to D7 since my code is using the DBTNG API and I'm working on a D7 project.

I realized that the inverse in #13 won't work: return me a list of items which have no out-date translations whatsoever. The query will still return results when you set translate to 0: its the equivalent of saying: fetch me a list of items where at least one translation is complete.

We ended up breaking our heads on this came up with this monster to filter out all content which have zero outdated translation nodes.

SELECT k.nid, k.title, k.tnid, k.translate, k.language FROM node k
  WHERE IF (k.tnid IN (SELECT tnid FROM node WHERE language = 'en'), ( k.tnid IN (SELECT tnid FROM node WHERE language = 'en' AND translate = 0) ), 1)
AND IF (k.tnid IN (SELECT tnid FROM node WHERE language = 'de'), ( k.tnid IN (SELECT tnid FROM node WHERE language = 'de' AND translate = 0) ), 1)
AND IF (k.tnid IN (SELECT tnid FROM node WHERE language = 'fr'), ( k.tnid IN (SELECT tnid FROM node WHERE language = 'fr' AND translate = 0) ), 1)

The idea being that you have to create a new expression in your WHERE clause for each translation. Someone should be able to combine #13 and #14 into something more elegant then this. We've tried to work with OUTER JOIN but failed to get something workable.

We needed a translation overview that works so I quickly made an alternative. It's at http://drupal.org/sandbox/jax/1923664
Or you can download the first version attached to this patch. It still needs some finishing but that's for tomorrow.

Sorry for the spam but there's a big bug in the version above, I had the wrong access callback.