I need to retrieve the latest 10 images for each post from the Posts table of a WordPress DB (MySQL 5.0) by using a custom MySql query, so I have written this one:
SELECT p.*, COUNT( p.post_parent ) AS counter
FROM wp_posts AS p
LEFT OUTER JOIN wp_posts AS p_temp
ON p.post_parent = p_temp.post_parent AND
p.post_date < p_temp.post_date
WHERE p.post_type = 'attachment' AND
p.post_mime_type LIKE 'image/%' AND
p.post_parent > 0
GROUP BY p.ID
HAVING counter <= 10
ORDER BY p.post_parent, p.post_date DESC
The problem is that I get a counter that doesn't start from 1 for each post_parent group, neither is sequential. So the result isn't reliable.
Some sample data the test database returns me:
ID post_date post_parent post_type counter
---------------------------------------------------------------
502 2020-03-02 17:42:03 463 attachment 2
474 2020-01-28 18:41:55 463 attachment 5
933 2020-10-26 09:28:54 497 attachment 1
932 2020-10-26 09:28:34 497 attachment 2
500 2020-03-02 17:29:08 497 attachment 6
499 2020-03-02 17:28:55 497 attachment 7
498 2020-03-02 17:28:43 497 attachment 8
How could I fix the query so that it does return max 10 results (images) for each value of post_parent (the ID of the post to which images are attached)?
This is tricky, particularly because some posts may not have ten rows. So, the basic idea is similar to GMBs answer, but while taking into account that there are not 9 rows (and using the correct columns):
select p.*
from wp_posts p
where p.post_type = 'attachment' and
p.post_mime_type like 'image/%' and
p.post_parent > 0 and
p.post_date >= coalesce( (select p2.post_date
from wp_posts p2
where p2.post_type = 'attachment' and
p2.post_mime_type like 'image/%' and
p2.post_parent > 0
order by p2.post_date desc
limit 9, 1
), p.post_date
);
EDIT:
If you want this per post_parent use a correlation clause:
p.post_date >= coalesce( (select p2.post_date
from wp_posts p2
where p2.post_type = 'attachment' and
p2.post_mime_type like 'image/%' and
p2.post_parent = p.post_parent
order by p2.post_date desc
limit 9, 1
), p.post_date
);