Hi, Matthew,
Here is some additional information about how we might use the WP database structure combined with the WPML database structure to count the term-taxonomy entries for a specific language. I am neither a programmer nor a database expert, but hopefully this information will be enough to point you in a productive direction. Please let me know how I can help, or what I might try next.
When using the WPML Multilingual Plugin, the “normal” term-taxonomy count includes posts in all languages. There are no separate counts for posts in each language separately. We are looking for a way to get separate counts by language.
The basic WordPress database structure uses three tables to process and track taxonomy data:
- wp_terms
- wp_term_taxonomy
- wp_term_relationships
(Matthew: I am sure you are very familiar with these tables, given your work with your MCTL plugin. The details are well explained in the WP Codex for WordPress Taxonomy.)
The MCTL plugin probably returns the “count” value from the “wp_term_taxonomy” table, which tracks how many posts are associated with a particular term in a particular custom taxonomy (i.e., a unique term+taxonomy combination). In my own multilingual website example, the “count” variable would include the combined number of posts in English and in Dutch.
In order to identify the “count” for a specific language, there seems to be two additional steps required, as follows:
Step 1: First, query the “wp_term_relationships” table, using the “term_taxonomy_id” for the desired term+taxonomy combination. The “wp_term_relationships” table contains an index called “object_id”, which is the ID of each post that matches the value of “term_taxonomy_id”. The output of the query should be list of “object_id” values (i.e., post ID’s), each of which matches the desired “term_taxonomy_id”.
Step 2: Armed with a list of “object_id” values (post ID’s), the next step is to query a custom data table called “wp_ici_translations”, which is a table created by the WPML Multilingual plugin. The “wp_ici_translations” table has the following structure:
- translation_id: Primary index uniquely identifying each record in the table
- element_type: post_page, tax_category, tax_post_tag, etc.
- element_id: A value corresponding to the “object_id” from the “wp_term_relationships” table (which corresponds to the particular post ID being examined.)
- trid: A group identifier which has the same numeric value for each “element_id” that is a translation of a single post; i.e., a post written in English and its Dutch translation would have two separate “element_id” (post ID) values, but both would have the same “trid” value, thereby connecting the two posts together as a single group.
- language_code: A code identifying the language of the post; e.g., “en” = English; “nl” = Dutch
- source_language_code: A code identifying the source language of the initial post; e.g., “en” = English; “nl” = Dutch
For each “object_id” value selected from the “wp_term_relationships” table, a query would locate the record in the “wp_ici_translations” table where “element_id” = “object_id”. If that record contained a “language_code” value that matched the language desired in the taxonomy count, then the counter would be increased by one. Continue the query until all the “object_id” records had been examined. This process should result in an accurate count of all posts in a single language that match the term+taxonomy criterion.
Thanks, Matthew, for your help. I’ll look forward to your input.
