I'm attempting to make a question to obtain the publish title, link and pictures from the publish. I've things i need using 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, things i want is to buy just the last thumbnail placed within the publish, not every one of them. For this reason why I've already considered an order by clause. I've attempted placing a "LIMIT 1" within the LEFT JOIN Choose however i guess that's wrong.
Any idea? Thanks
You won't want to put a restriction in your nested choose, since it is only going to find 1 row - not just one row for every publish as if you intend.
Use a nested choose to obtain the MAX(publish_date_gmt) for every publish (I believe you would like to GROUP BY publish_parent?) after which choose the guid for that image that suits the greatest publish date.
Try something similar to the next (I haven't examined this, so go having a touch of suspicion):
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 ultimate choice of MAX around the guid for every publish only agreed to be an effort to ensure originality within the unlikely event you will find multiple images using the identical timestamp on one publish.