Finding the Latest Post by Author

Question

I'm building an "authors" page for my Wordpress blog that lists all the current site contributors along with various other information such as their number of published posts, the date of their last post, etc.

Google and the Wordpress Codex has pointed me toward using subqueries in MySQL to pull all the data I need in one query, and it's working wonderfully for getting the number of published posts per author.

What I can't get working is finding the post ID for the latest post, per author.

Current query that works, without latest post:

SELECT users.ID, (SELECT count(*) FROM posts, users WHERE users.ID = posts.post_author AND posts.post_type = 'post' AND posts.post_status = 'publish') AS post_count FROM users ORDER BY post_count DESC

My attempt to get the latest post ID for each author ('latest_post_ID'):

SELECT users.ID, (SELECT count(*) FROM posts, users WHERE users.ID = posts.post_author AND posts.post_type = 'post' AND posts.post_status = 'publish') AS post_count, (SELECT posts.ID FROM posts, users WHERE users.ID = posts.post_author AND posts.post_type = 'post' AND posts.post_status = 'publish' ORDER BY posts.post_date DESC LIMIT 1) AS latest_post_ID FROM users ORDER BY post_count DESC

The problem being with the added subquery - the query will find any posts that are published that match any author, rather than what I'm intending ('users.ID = posts.post_author').

I'd much appreciate it if someone with some SQL-fu could point out where I'm misusing and/or abusing MySQL subqueries.

Answer 1

Try this.

SELECT a.ID, 

(SELECT count(*) 
FROM posts 
WHERE a.ID = posts.post_author 
AND posts.post_type = 'post' 
AND posts.post_status = 'publish') AS post_count, 

(SELECT posts.ID 
FROM posts 
WHERE a.ID = posts.post_author 
AND posts.post_type = 'post' 
AND posts.post_status = 'publish' 
ORDER BY posts.post_date DESC LIMIT 1) AS latest_post_ID 

FROM users As a 
ORDER BY post_count DESC

Answer 2

The following query should work in MySQL:

SELECT posts.ID FROM posts,
(SELECT MAX(posts.post_date) AS max_date,
posts.post_author AS author

FROM posts 

WHERE users.ID = posts.post_author 

GROUP BY posts.post_author) AS max_date_table

WHERE posts.post_date = max_date_table.max_date AND
posts.post_author = max_date_table.author

Answer 3

  SELECT u.id,
         COUNT(*) AS post_count,
         MAX(p.id) AS latest_post_id
    FROM POSTS p
    JOIN USERS u ON u.ID = p.post_author 
   WHERE p.post_type = 'post' 
     AND p.post_status = 'publish'
GROUP BY u.id
ORDER BY post_count DESC

I don't recommend the use of SELECTS within the SELECT clause. While they work, they will provide the worst performance.