2

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

  • See this and this. They are mostly the same issue of your question with interesting and helpful answers. – cybmeta Jan 8 '15 at 19:18
  • Thanks, i did search for a couple of hours but did not find those two :) Did not know that the meta data is retrieved by WP_Query but just not added to the result. Will do some performance tests on that one. – Larzan Jan 9 '15 at 11:08
  • Unfortunately that didn't help, you were right about the get_post_meta not needing an additional DB Query, but with that i can only retrieve the ID of the thumbnail. To get its URL i have to use one of the various helper functions which all create another DB query... – Larzan Jan 9 '15 at 13:27
  • I've edited the title of your question, I think now it describes the question better. – cybmeta Jan 10 '15 at 11:07
3

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.

-1

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 ) );


            }
  • Again, it is not clear what the answer is here. Why using the date range? – Milan Petrovic Oct 28 '17 at 15:03
  • Sorry about it i was working on another project so the code belong to it. you just has to query to retrieve posts from database and use foreach in foreach you'll get list of ids as you have list of thumbnail. Here no need to have list of thumbnail ids here you can direct print post thumbnails by just using wordpress method wp_get_attachment_url . – Zaheer Abbas Aghani Oct 28 '17 at 16:17
  • Did you verify that this does not generate additional DB Queries? This is not only about how to get the information, but also how to get it as efficiently as possible – Larzan Nov 1 '17 at 17:08
  • yes no additional staff i used it in my project it is working like a charm bro – Zaheer Abbas Aghani Nov 2 '17 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.