Mysql SELECT posts

Question

Mysql SELECT posts

Valeriy Donika

2021年4月3日 00:02

how i can get posts via MYSQL from posts where language is 'en' and 'ru'

I have this query

$sql = "SELECT p1.*, wm2.meta_value 
  FROM wp_posts p1 
  LEFT JOIN wp_postmeta wm1 ON ( wm1.post_id = p1.ID 
  AND wm1.meta_value IS NOT NULL 
  AND wm1.meta_key = '_thumbnail_id' ) 
  LEFT JOIN wp_postmeta wm2 ON ( wm1.meta_value = wm2.post_id 
  AND wm2.meta_key = '_wp_attached_file' 
  AND wm2.meta_value IS NOT NULL ) 
  WHERE p1.post_status= 'publish' AND p1.post_type='post' ORDER BY p1.post_date DESC Limit 3";

I use WPML plugin.

Topic mysql plugin-wpml Wordpress

Category Web

Jose Carlos Ramos Carmenates · Accepted Answer · 2017年2月2日 13:40

I resolved creating SQL query:

SELECT posts.post_title, posts.post_content, trans.element_type,
trans.language_code
FROM wp_posts AS posts

INNER JOIN wp_icl_translations AS trans
ON posts.ID = trans.element_id

INNER JOIN wp_postmeta AS meta
ON posts.ID = meta.post_id

WHERE trans.element_type = 'post_product'
AND trans.language_code = 'de'

This is added only if you need something on post_meta:

...
INNER JOIN wp_postmeta AS meta
ON posts.ID = meta.post_id
...

It was my solutions to get all post by language trans.language_code = 'de', trans.language_code = 'es', trans.language_code = 'en' ...

Valeriy Donika · Accepted Answer · 2015年8月8日 12:44

$sql = "SELECT p1.*, wm2.meta_value 
                        FROM wp_posts p1 
                        JOIN wp_icl_translations t
                        ON p1.ID = t.element_id 
                        LEFT JOIN wp_postmeta wm1 ON ( wm1.post_id = p1.ID 
                        AND wm1.meta_value IS NOT NULL 
                        AND wm1.meta_key = '_thumbnail_id' ) 
                        LEFT JOIN wp_postmeta wm2 ON ( wm1.meta_value = wm2.post_id 
                        AND wm2.meta_key = '_wp_attached_file' 
                        AND wm2.meta_value IS NOT NULL ) 
                        WHERE p1.post_status= 'publish' 
                        AND p1.post_type='post' 
                        AND t.element_type='post_post'
                        AND t.language_code='ru' 
                        ORDER BY p1.post_date DESC 
                        Limit 0, 3";

get last 3 posts with thumb where lang is ru

Mysql SELECT posts

About