Retrieve featured image (thumbnail) url from multiple posts with one query

Question

I have a list of post ids and need to get all the standard data plus the thumbnail of the posts. As there will be several hundred posts i need an efficient way to retrieve the data, just looping over all the ids and using has_thummbnail... and all the other versions of the same 'one by one' approach will just not work fast enough.

So the question is:

how can i retrieve multiple post with their thumbnail url without making one query for each id

UPDATE:

As cybmeta pointed out with the linked posts, get_post_meta does not create an additional DB Query and with that function it is possible to retrieve a post's custom fields.

BUT that still doesn't give me the URL of the thumbnail, just the thumbnail's ID. When retrieving the URL by one of the possible methods (wp_get_attachment_image_src, wp_get_attachment_metadata, ...) i create an additional DB Query for each thumbnail as those functions do not take multiple IDs!

More specific question:

How can i retrieve the url of multiple thumbnails via their id in one query

Answer 1

EXPLANATION

As mentioned in my update above, to retrieve the ID of a thumbnail the get_post_meta function can be used without any additional DB overhead. The 'normal' way to get the url to a thumbnail id is with wp_get_attachment_image_src or similar functions, but those do not accept an array of IDs.

What i noticed using wp_get_attachment_image_src was that it created even TWO querys per call (ergo per thumbnail), one to get the post data and then another one for the actual url.

SOLUTION

The only solution i found to retrieve more than one url at the same time was to directly query the DB postmeta table via wpdb.

There is a postmeta entry for the relation post->thumbnail with the meta_key _thumbnail_id and the thumbnails id as the meta_value and then two more entries per thumbnail with the meta_tags _wp_attachment_metadata and _wp_attached_file and the thumbnail's id as the post_id.

So the interesting part of the postmeta table looks like this:

meta_id  post_id    meta_key                meta_value
328      136       _wp_attached_file        2015/01/Dog-w-Glasses.jpg
329      136       _wp_attachment_metadata  a:5{s:5:"width";i:...
...
335      138       _thumbnail_id            136

with 138 being the post's id and the meta_id being irrelevant for this example.

As the _thumbnail_id was linked to the post_id it could be retrieved via the get_post_meta, but as the actual url was stored under the thumbnails id it had to be retrieved seperately.

So what i did was retrieving all the thumbnail ids for each post via get_post_meta and then creating a query to the postmeta table of the WP Database:

$thumb_ids = '( 23, 89, 24, 69 )'; // just an example
global $wpdb;
$qstr =
    "SELECT post_id, meta_value
        FROM $wpdb->postmeta
        WHERE post_id IN " . $thumb_ids. ' AND meta_key = "_wp_attached_file"';
$results = $wpdb->get_results( $qstr, ARRAY_N );

this will return the results in the form of a non-associative array, see the wpdb page for more info on the parameters of that function.

This way only one extra DB call is being used to retrieve all the urls for all the posts.

p.s.: I used Query Monitor and Debug Bar to analyse the DB queries.

Answer 2

More clear code to achieve post thumbnail full URL. After getting post thumbnail you just has to use word-press built in method wp_get_attachment_url( get_post_thumbnail_id( $id ) ); to achieve full URL.

global $wpdb;

            $to = "2017/10/25";
            $from = "2017/10/27";
            $tablename = $wpdb->prefix . "posts";
            $sql = $wpdb->prepare( "SELECT * FROM $tablename 
            /*JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
            JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
            JOIN $wpdb->terms t ON (tt.term_id = t.term_id)*/
            Where post_type =%s AND  post_date BETWEEN %s AND %s  ",'post',$to, $from);
            $results = $wpdb->get_results( $sql , ARRAY_A );
            //print_r($results);
            foreach ( $results as $post )
            {

                $id = $post['ID'];
                //echo  $post['parent'].'<br/>';
                echo wp_get_attachment_url( get_post_thumbnail_id( $id ) );


            }