So far I'm not aware of any new posts of post_type Nanny or Family which have not been duplicated correctly. Perhaps this is due to the latest release of the Forms plugin which cured some bug or something.
However, our Nanny screen is reporting we have 1673 EN Nannies and 1671 DE Nannies. I've been trying different queries on the database to determine this difference and it's outwitting me. What query is producing this number on the front end? I suspect it MAY be coming from the translation table:
select language_code, count(*)
from wp_icl_translations
where element_type = 'post_nanny'
group by language_code
language_code count(*)
de 1671
en 1673
However I cannot find a query to identify the 2 EN nannies which don't have a DE translation. I can see that there are only 1671 records with the '_icl_lang_duplicate_of' in the postmeta
select count(*)
from wp_posts p
left join wp_postmeta pm on p.id = pm.post_id
where p.post_type = 'nanny'
and pm.meta_key = '_icl_lang_duplicate_of'
count(*)
1671
Is there any clever way to identify this difference?! The translation table shows that there are 3344 Nanny translations, which matches 1671 + 1673 = 3344. The two numbers should always be in sync since we should never have a case when a Nanny was created and didn't get translated. My feeling is that there are two Nanny records lurking in the database that have a translation, but don't have the metadata to indicate this.
select count(*)
from wp_icl_translations
where element_type = 'post_nanny'
count(*)
3344
I think you are better at SQL than I am, it might be simple for you to compose a query if you know what you are aiming for.
We are trying to identify the two en posts that do not have a translation to de, correct?
In the icl_translations table the translations are linked via the trid column.
So you would see pairs of rows for translated posts that look a bit like the first rows here, while the last row is an example of a post where the trid is unique because there is only an entry with the en original and no corresponding de translation:
element_id trid language_code source_language
123 555 en null
124 555 de en
345 777 en null
Perhaps you can craft a SQL query to capture those two cases, if not I should be able to propose a PHP solution using the API easily enough.
OK, but can you tell me how the count of posts in the screenshot is determined in my previous update? Was my suspicion correct? (I'm trying to determine if there are are just 2 EN nannies which don't have the _icl_lang_duplicate_of correctly set, or whether we have variations in both directions EN > DE and DE > EN.)
OK, this is bidirectional, the original posts could be 'en' which are duplicated to 'de' or vice versa.
To hone in on what appears to be two results that don't have a duplicate (or show that there are more, which cancel each other out in the totals), I think it is enough to identify rows for the element_type = post_nanny where there is a unique trid (which means an original post exists without a translation/duplicate).
ChatGPT recommends:
SELECT it.element_type, it.element_id, it.trid, it.language_code, it.source_language
FROM wp_icl_translations it
INNER JOIN (
SELECT trid
FROM wp_icl_translations
WHERE element_type = 'post_nanny'
GROUP BY trid
HAVING COUNT(trid) = 1
) AS unique_trids ON it.trid = unique_trids.trid
WHERE it.element_type = 'post_nanny';
If you have the element_id's that correspond to these trid's you can then check the post meta for those post id's.
Bingo! That did the trick - thanks! - and indeed there were only 2 EN Nannies which didn't have DE translations.
There was just a slight mistake in the last column name, source_language should be source_language_code, so the correct SQL to find untranslated items is:
SELECT it.element_type, it.element_id, it.trid, it.language_code, it.source_language_code
FROM wp_icl_translations it
INNER JOIN (
SELECT trid
FROM wp_icl_translations
WHERE element_type = 'post_nanny'
GROUP BY trid
HAVING COUNT(trid) = 1
) AS unique_trids ON it.trid = unique_trids.trid
WHERE it.element_type = 'post_nanny';
OK I think this ticket is solved, but as I mentioned it before I'd like to give it just 2 weeks more time to be sure. You can set it back to "Awaiting user feedback" to get it out of your queue.
I'm happy for this one to be closed. We haven't had any more cases of duplicates not being created since.
If it should happen in the future, I will open a new ticket and refer to this one.
Kind regards
Simon
Manage Cookie Consent
We use cookies to optimize our website and services. Your consent allows us to process data such as browsing behavior. Not consenting may affect some features.
Functional
Always active
Required for our website to operate and communicate correctly.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
We use these to analyze the statistics of our site. Collected information is completely anonymous.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
These cookies track your browsing to provide ads relevant to you.
