wordpress mysql query for post thumbnail

Question

Hi,

I am trying to make a query to get the post title, link and images of the post. I have what I need with the following query:

SELECT WPPOSTS.post_title,WPPOSTS.guid,WPPOSTS_2.image
FROM wp_posts WPPOSTS
LEFT JOIN
( 
SELECT guid AS image,post_parent
FROM  wp_posts
WHERE post_type = 'attachment'
ORDER BY post_date_gmt DESC
) WPPOSTS_2 ON (WPPOSTS_2.post_parent = WPPOSTS.ID)
WHERE WPPOSTS.ID IN (2439,2395,2355)

Now, what I really want is to get only the last thumbnail inserted in the post, not all of them. That's the reason why I have already considered the order by clause. I have tried putting a "LIMIT 1" inside the LEFT JOIN SELECT but I guess that's wrong.

Any idea? Thanks

Answer 1

You don't want to put a LIMIT on your nested select, because it will only find 1 row - not one row for each post like you intend.

You can use a nested select to find the MAX(post_date_gmt) for each post (I think you would want to GROUP BY post_parent?) and then select the guid for the image that matches the highest post date.

Try something like the following (I have not tested this, so take it with a grain of salt):

SELECT a.post_title,max(c.guid)
FROM wp_posts a

LEFT JOIN
(select post_parent, max(post_date_gmt) as latest_image_date from wp_posts
where post_type='attachment' GROUP BY post_parent) b 
on a.id=b.post_parent

LEFT JOIN wp_posts c
on c.post_parent=a.id 
and c.post_type='attachment' 
and b.latest_image_date = c.post_date_gmt

GROUP BY a.post_title

The final selection of MAX on the guid for each post was just an attempt to guarantee uniqueness in the unlikely event there are multiple images with the exact same timestamp on a single post.