从普通WordPress查询sql

Question

免责声明:我不会使用任何其他方式，我有一个特定的要求。

所有我想要的是加入从帖子表到帖子元，这样我就可以得到每个帖子的特色图像。我可以通过where子句post类型为post和published获得帖子，但我不知道如何为MySql编写join来获取每个帖子的特色图像

Answer 1

您可以尝试如下所示：

$sql = "
    SELECT $wpdb->posts.* 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    ORDER BY $wpdb->posts.post_date DESC
";

// Get an array of objects
$posts = $wpdb->get_results($sql, OBJECT);

你可以使用check more here。

Answer 2

如果其他人还在寻找所需的sql，您可以在这里找到

        SET @term = "someterm"
        SELECT DISTINCT
            posts.id,
            posts.post_author,
            posts.post_title, 
            posts.post_date, 
            LEFT(posts.post_content,300) post_content,
            wpmeta.meta_value thumbnail
        FROM wp_posts posts 
            INNER JOIN wp_postmeta wpm ON posts.id = wpm.post_id 
            INNER JOIN wp_postmeta wpmeta ON wpm.meta_value = wpmeta.post_id
            LEFT JOIN wp_term_relationships rel ON rel.object_id = posts.ID
            LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
            LEFT JOIN wp_terms t ON t.term_id = tax.term_id
        WHERE 
            post_status = 'publish' 
            AND posts.post_type = 'post' 
            AND wpm.meta_key = '_thumbnail_id' 
            AND wpmeta.meta_key = '_wp_attached_file'
            AND 1 = 
                CASE
                    WHEN @term IS NULL THEN 1
                    WHEN @term IS NOT NULL AND t.term_id = @term THEN 1
                    ELSE 0
                END
        ORDER BY post_date DESC