query wordpress posts by polylang language on MySQL

Question

I'm trying to build a MySQL query to select last post by language (Polylang plugin) but I can't find what table and field does Polylang store the language of each post. Any clue?

Karakul

I think the information about the mapping is stored in the wp_term_taxonomy table. Its an array with your locales and the post id. SELECT * FROM `wp_term_taxonomy` WHERE `taxonomy` = 'post_translations' – Tonina

Thanks for pointing me in the right direction. Is there any info available about the codes Polylang uses when building the wp_term_taxonomy.description field values? – Karakul

Answer 1

I know it's too late to answer your question, but I gonna write it, for future users.

you can find everything related to Polylang in wp_term_relationships and wp_term_taxonomy tables.

for example, if I want to get the language of a specific post with id = 157896.

In the wp_term_relationships in object_id column I should search for 157896 and I will get

object_id | term_taxonomy_id
----------------------------
  157896  |     26284
  157896  |     26130
  157896  |      11

and in wp_term_taxonomy we have:

term_taxonomy_id | taxonomy | description
---------------------------------------------
26284 | post_translations | a:2:{s:2:"en";i:157896;s:2:"de";i:157894;}
26130 | category | Description ... ...
11    | language | a:3:{s:6:"locale";s:5:"en_UK";s:3:"rtl";i:0;s:9:"flag_code";s:2:"en";}

26130 is related to the category

26280 means this post translated in de and en languages and the post number for en is 157896 and the post number for de is 157894

11 means the post with id = 157896 is in en(English) language.

wp_term_relationships is a pivot table.

Thoracotomy

Answer 2

The following mysql query will find all published wordpress posts which is in en language of polylang plugin:

SELECT * FROM wp_posts WHERE post_status = 'publish' AND ID IN (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"en";i:', -1), ';',1) FROM `wp_term_taxonomy` WHERE `taxonomy` = 'post_translations' ) order by ID desc

Lemur

Seriously helpfull! – Incogitable

I wish I could upvote this more. – Trangtranquada