Knowledge article-data category filtering not working with translations

Question

I wanted to apply filter to the article type, and filtering criteria was that show only articles with data categories. So, I created the following SOQL after reviewing question:

SELECT ID, Title, UrlName, KnowledgeArticleId 
FROM Troubleshooting__kav 
WHERE Language = 'en_US'  
AND  Id IN (SELECT ParentId FROM Troubleshooting__DataCategorySelection) 
AND  publishStatus = 'Online'  
ORDER BY Title ASC

The above soql works well if the language is English (en_US), but if I change the language to Spanish (es_MX) then it doesn't return any data. I have Spanish translated articles setup in my sandbox. If I remove the following filter

AND  Id IN (SELECT ParentId FROM Troubleshooting__DataCategorySelection) 

Spanish articles then appear fine.

Answer 1

So, unless someone comes up with a solution related exactly to the articleType__DataCategorySelection situation posted above, this is what I have used to filter articles using WITH DATA CATEGORY clause and filtering with the top most data category level:

WITH DATA CATEGORY Topics__c Below All__c

Final SOQL (Article Type= Troubleshooting__kav ) :

SELECT ID, Title, UrlName, KnowledgeArticleId 
FROM Troubleshooting__kav 
WHERE Language = 'en_US'  
AND   publishStatus = 'Online'  
WITH  DATA CATEGORY Topics__c Below All__c
ORDER BY Title ASC

Answer 2

Just getting same issue and above given workaround is not going to help because I need to add NOT IN filter which I am achieving by the datacategoryselection and then filtering (as there is no option for not in data category SOQL- WITH, BELOW and AT only supported).

It seems salesforce issue; if it returns value for Language='en_US' then it should return value for Language='ja' as well.