自定义SQL查询以获取带有特色图像URL的帖子列表

时间：2017-05-11 作者：Tarun modi

我正在做一项任务，我需要在单页/单请求下检索100篇帖子及其特色图片。

使用wordpress方法检索帖子，然后使用get_the_post_thumbnail 函数加载页面需要很长时间。

有没有人能提供一个更快的解决方案，比如在一个查询下检索帖子和特色图片。这应该会加快进程。

5 个回复

最合适的回答,由SO网友:JItendra Rana 整理而成

最近处理过类似的问题。下面是SQL查询，用于获取带有特色图像的帖子。

global $wpdb;

$perpage = 10; 
$page = 1; // Get the current page FROM $wp_query

$counter = $perpage * $page;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir[\'baseurl\'];

$sql = "
SELECT 
    post.ID,
    post.post_title,
    post.post_date,
    post.category_name,
    post.category_slug,
    post.category_id,
    CONCAT( \'".$uploadDir."\',\'/\', thumb.meta_value) as thumbnail,
    post.post_type
FROM (
    SELECT  p.ID,   
          p.post_title, 
          p.post_date,
          p.post_type,
          MAX(CASE WHEN pm.meta_key = \'_thumbnail_id\' then pm.meta_value ELSE NULL END) as thumbnail_id,
      term.name as category_name,
      term.slug as category_slug,
      term.term_id as category_id
    FROM ".$wpdb->prefix."posts as p 
    LEFT JOIN ".$wpdb->prefix."postmeta as pm ON ( pm.post_id = p.ID)
    LEFT JOIN ".$wpdb->prefix."term_relationships as tr ON tr.object_id = p.ID
    LEFT JOIN ".$wpdb->prefix."terms as term ON tr.term_taxonomy_id = term.term_id
    WHERE 1 ".$where." AND p.post_status = \'publish\'
    GROUP BY p.ID ORDER BY p.post_date DESC
  ) as post
  LEFT JOIN ".$wpdb->prefix."postmeta AS thumb 
    ON thumb.meta_key = \'_wp_attached_file\' 
    AND thumb.post_id = post.thumbnail_id
  LIMIT ".$counter.",".$perpage;

$posts = $wpdb->get_results( $sql, ARRAY_A);

Bonus : 如果需要，您还可以获得类别详细信息和帖子详细信息。

P.S : 您需要稍微更改查询以匹配您的需求并获得所需的字段。

SO网友:Krishna Modi

这是一个非常简单的解决方案，不需要任何复杂的连接，

SELECT wp_posts.id,
       wp_posts.post_title,
       wp_terms.name,
       (SELECT guid
        FROM   wp_posts
        WHERE  id = wp_postmeta.meta_value) AS image
FROM   wp_posts,
       wp_postmeta,
       wp_term_relationships,
       wp_terms
WHERE  wp_posts.id = wp_term_relationships.object_id
       AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
       AND wp_terms.name = \'mycat\'
       AND wp_posts.post_status = "publish"
       AND wp_posts.post_type = "post"
       AND wp_postmeta.post_id = wp_posts.id
       AND wp_postmeta.meta_key = \'_thumbnail_id\'
ORDER  BY wp_posts.post_date DESC
LIMIT  5;

此查询将提供帖子id、帖子类别和特色图片。您可以通过更改wp\\U术语来筛选类别。名称=“mycat”和您的类别名称。

SO网友:Vinod Dalvi

帖子和特色图片URL保存在wp\\U posts表中，其关系保存在wp\\U POSTETA表中，因此在任何情况下，您都必须在单个查询中直接查询这两个表，或使用WordPress函数分别查询它们。

我不认为在单个查询中同时查询两个表会提高主要性能，但如果您想这样做，那么可以使用以下自定义代码。

    global $wpdb;
    $results = $wpdb->get_results( "SELECT * FROM $wpdb->posts, $wpdb->postmeta where $wpdb->posts.ID = $wpdb->postmeta.post_id and $wpdb->postmeta.meta_key = \'_thumbnail_id\' and $wpdb->posts.post_type=\'post\' limit 100");

    if ( $results )
    {
        foreach ( $results as $post )
        {       
            setup_postdata( $post );
            ?>
            <h2>
                <a href="<?php the_permalink(); ?>" rel="bookmark" title="Permalink: <?php the_title(); ?>">
                    <?php the_title(); ?>
                </a>
            </h2>
            <?php
            if ( $post->meta_value ) { 
                $image = image_downsize( $post->meta_value );
                ?>
                <img src="<?php echo $image[0]; ?>" />
                <?php
            }
        }   
    }
    else
    {
        ?>
        <h2>Not Found</h2>
        <?php
    }

SO网友:Waqas Ali Shah

您可以使用无限滚动方式来管理页面加载速度。只检索那些显示在折叠和滚动上方的帖子。您可以查询其他帖子。这可以帮助您更快地加载页面。这里有一个教程。

https://code.tutsplus.com/tutorials/how-to-create-infinite-scroll-pagination--wp-24873

有一些无限滚动帖子插件。

https://wordpress.org/plugins/wp-infinite-scrolling/

SO网友:Juvy Cagape

被接受的答案会毁了你的生活哈哈。。。这是最简单的方法。。

global $wpdb;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir[\'baseurl\'];

$page = (!empty($_REQUEST[\'p\'])) ? $_REQUEST[\'p\'] : 1;
$_limit = 50;
$_start = ($page > 1) ? ($page * $_limit) : 0;

$_stmts = "
    SELECT 
        p.ID, 
        p.post_title,
        p.post_name,
        CONCAT( \'".$uploadDir."\', \'/\', thumb.meta_value) as thumbnail
    FROM {$wpdb->prefix}posts AS p
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumbnail_id
            ON thumbnail_id.post_id = p.ID AND thumbnail_id.meta_key = \'_thumbnail_id\'
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumb
            ON thumb.post_id = thumbnail_id.meta_value AND thumb.meta_key = \'_wp_attached_file\'
        
        
    WHERE p.post_status = \'publish\'
    LIMIT {$_start},{$_limit}
";

$get_rs = $wpdb->get_results($_stmts);

结束

小码农CODE

自定义SQL查询以获取带有特色图像URL的帖子列表

相关推荐

AMP-CHANGE REL=“Canonical”from unctions.php