无法嵌套SQL查询获取WordPress多语言文章数据求助

我懂你之前的困扰——想把WPML的语言筛选查询和获取文章详情（含裁剪后特色图）的查询结合起来，却一直没搞定嵌套逻辑。好在你已经找到了解决方案，下面把整个过程整理清楚，方便大家参考：

问题背景 #

需要从WordPress数据库中完成两个核心需求：

1. 借助WPML插件筛选出指定语言（这里是英文en）的已发布文章
2. 同时获取文章的标题、post_name、发布日期、内容，以及裁剪为150x150尺寸的特色图片URL

原始独立查询 #

查询1：WPML语言筛选文章 #

这个查询通过关联wp_icl_translations表，筛选出英文的已发布文章，返回结构和wp_posts表完全一致：

SELECT * 
FROM wp_posts 
JOIN wp_icl_translations t ON wp_posts.ID = t.element_id AND t.element_type = CONCAT('post_', wp_posts.post_type) 
WHERE wp_posts.post_type = 'post' 
  AND wp_posts.post_status = 'publish' 
  AND ( t.language_code = 'en' AND wp_posts.post_type = 'post' )

查询2：获取文章详情及特色图 #

这个查询负责提取文章基本信息，并对特色图URL进行字符串处理，生成150x150尺寸的缩略图地址：

SELECT title, post_name, date, content, 
       CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-150x150.',SUBSTRING_INDEX(image, '.', -1)) AS image 
FROM ( 
    SELECT p.post_title AS title, p.post_status AS 'status', p.post_date AS date, p.post_content AS content, p.post_name AS post_name, 
           (SELECT `guid` FROM wp_posts WHERE id = m.meta_value) AS image 
    FROM wp_posts p, wp_postmeta m 
    WHERE p.post_type = 'post' 
      AND p.post_status = 'publish' 
      AND p.id = m.post_id 
      AND m.meta_key = '_thumbnail_id' 
    ORDER BY date DESC LIMIT 5 
) TT

最终可用嵌套查询 #

把查询1作为子查询替代查询2中的wp_posts p，就能实现同时筛选语言和获取文章详情的需求：

SELECT title, post_name, date, content, 
       CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-150x150.',SUBSTRING_INDEX(image, '.', -1)) AS image 
FROM ( 
    SELECT p.post_title AS title, p.post_status AS 'status', p.post_date AS date, p.post_content AS content, p.post_name AS post_name, 
           (SELECT `guid` FROM wp_posts WHERE id = m.meta_value) AS image 
    FROM ( 
        SELECT * 
        FROM wp_posts 
        JOIN wp_icl_translations t ON wp_posts.ID = t.element_id AND t.element_type = CONCAT('post_', wp_posts.post_type) 
        WHERE wp_posts.post_type = 'post' 
          AND wp_posts.post_status = 'publish' 
          AND ( t.language_code = 'en' AND wp_posts.post_type = 'post' )
    ) p, wp_postmeta m 
    WHERE p.post_type = 'post' 
      AND p.post_status = 'publish' 
      AND p.id = m.post_id 
      AND m.meta_key = '_thumbnail_id' 
    ORDER BY date DESC LIMIT 4 
) TT

逻辑说明 #

1. 最内层子查询：先通过WPML的翻译表筛选出符合语言条件的已发布文章，得到一个过滤后的文章数据集p
2. 中间层：将过滤后的数据集与wp_postmeta关联，通过_thumbnail_id元字段获取特色图的原始URL
3. 最外层：对原始图片URL进行字符串切割拼接，生成指定尺寸的缩略图地址

内容的提问来源于stack exchange，提问作者delaticoalsotano