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
2 Answers
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.