Custom SQL query to get List of posts with featured image url

by

I am working on a task where i need to retrive 100s posts under single-page/single-request with their featured images.

By using wordpress method of retriving posts and then retrive featured image individually using get_the_post_thumbnail function it takes so much time to load the page.

Can someone provide a faster solution for this like retrive posts and featured image under one single query. That should speed up the proccess.

5 Answers
5

Worked on similar problem recently. Here is the SQL query to get the post with Featured Image. 

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."',"https://wordpress.stackexchange.com/", 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 : You will also get Category details with post details if you need.

P.S : You will need to change the query a bit to match your requirements and get desired fields.

Leave a Comment