5

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?

Matias Masso
  • 1,670
  • 3
  • 18
  • 28
  • 3
    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'`` – 4lxndr Dec 03 '17 at 13:22
  • 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? – Matias Masso Dec 03 '17 at 16:09

2 Answers2

8

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
xinqiu
  • 795
  • 6
  • 13
4

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.

Raskul
  • 1,674
  • 10
  • 26