在一个sql查询中检索带有特色图像/缩略图的Wordpress帖子

Question

这是我的代码：

SELECT p.ID, p.post_title,
MAX(IF(pm.meta_key = 'featured_image', pm.meta_value, NULL)) AS event_imgID
FROM wpgp_posts AS p
LEFT JOIN wpgp_postmeta AS pm on pm.post_id = p.ID
WHERE p.post_type = 'product' and p.post_status = 'publish'
GROUP BY p.ID

event_imgID从 postmeta 检索 ID，但 img URL 存储在 guid 列的wpgp_posts中

如何实现这一点？

Answer 1

您可以加入表格以获取有关图像的更多信息：

SELECT . . .
FROM (SELECT p.ID, p.post_title,
             MAX(CASE WHEN pm.meta_key = 'featured_image' THEN pm.meta_value END) AS event_imgID
      FROM wpgp_posts p LEFT JOIN
           wpgp_postmeta pm 
           ON pm.post_id = p.ID
      WHERE p.post_type = 'product' and p.post_status = 'publish'
      GROUP BY p.ID
     ) pi LEFT JOIN
     wpgp_posts wp
     ON pi.event_imgID = wp.guid

Answer 2

我终于找到了方法。我不得不搜索thumbnail_id而不是 guid ：

SELECT
        p1.ID, p1.post_title,
         MAX(IF(pm.meta_key = 'listing_event_date', pm.meta_value, NULL)) AS event_date,
        wm2.meta_value as event_img
    FROM 
        wpgp_posts p1
        LEFT JOIN wpgp_postmeta AS pm on (pm.post_id = p1.ID)
    LEFT JOIN 
        wpgp_postmeta wm1
        ON (
            wm1.post_id = p1.id 
            AND wm1.meta_value IS NOT NULL
            AND wm1.meta_key = '_thumbnail_id'              
        )
    LEFT JOIN 
        wpgp_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='product'
        GROUP BY p1.ID, wm2.meta_id
    ORDER BY 
        event_date ASC

谢谢