We have multiple languages installed on the website. And now we don't need one language. It has duplicated media which is 100k+ and will take a lot of time to delete from the WPML setting.
You can see it here - hidden link
Is it possible to delete the associated content from the database directly using SQL.
Also, is it possible to hide a language from logged in users?
It is better to delete the content from the unwanted language from within the WPML UI rather than by running a SQL query directly, because WPML uses standard WordPress functions to delete content, so that if you delete posts, for example, then all corresponding entries in the postmeta table would also be deleted, taxonomy assignments are updated etc.
If you ran a SQL query to delete posts from wp_posts then you would be left with orphan entries elsewhere in the database.
Regarding hiding languages, you have the option in WPML > Languages to hide languages for guests, but logged-in users are able to see the language (so that they can review translations).
I tried deleting with WPML UI but for nearly 3 hours it only deleted 1k media/images. But we have 140k media. So, I wanted to delete media using SQL queries which will be faster.
And as you said, there would be orphan entries if we delete with SQL, can't we delete ghost entries from DB using WPML troubleshoot setting?
OK, if you want to delete the attachment posts directly using a SQL query you could do that.
It would leave orphan entries in the WPML translation tables. You could fix those by running the ghost entries scan at WPML > Support > Troubleshooting.
It would leave orphan entries in wp_postmeta. You could fix those with a database cleanup plugin.
Naturally, you should 100% take a backup beforehand, in case of unintended results, in which case you can revert.
You will need a query something like this:
DELETE wp_posts
FROM wp_posts
JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
WHERE wp_posts.post_type = 'attachment'
AND wpml_translations.element_type = 'post_attachment'
AND wpml_translations.language_code = 'it';
You would need to edit that depending on your database table prefix ("wp_" in the sample above).
Thank you for providing the SQL. It has removed media from database and I have run "Remove ghost entry". Then I tried to remove other associated content from WPML Language, it removed posts but it's not removing categories.
When I delete remaining contents, it disappears and when I reload page, it's there again.
Recorded video - hidden link
I just tested this on a local site and got the same result. The translated taxonomy terms are not deleted from the database, and if you reload the WPML > Languages page it will still report them as being available for deletion.
I'm reporting that to my second tier colleagues, it's something we need to fix, and I'll see if they propose a workaround.
Unfortunately, the feedback is that it is a known issue that needs to be fixed, deleting the translation terms was somehow not implemented, even though it is offered in the UI.
So you'll also need to use custom SQL to remove the terms from the wp_terms database table (with the usual proviso that you should backup first).
You can try this:
DELETE wp_terms
FROM wp_terms
JOIN wp_icl_translations wpml_translations
ON wp_terms.term_id = wpml_translations.element_id
WHERE wpml_translations.element_type LIKE 'tax_%'
AND wpml_translations.language_code = 'it';
This time the troubleshooting step to remove ghost entries from the WPML tables doesn't work for taxonomy terms, so you'll also need another SQL query to remove those.
You can try this:
DELETE wp_icl_translations
FROM wp_icl_translations
WHERE element_type LIKE 'tax_%'
AND language_code LIKE 'it'
