MySQL | Left connection
I have something like this:
ID | post_author | post_date | ... | post_title | post_status | ... | post_type
-------------------------------------------------------------------------------
1 | 1 | 2007-08-11| ... | A | publish | ... | post
2 | 3 | 2007-08-12| ... | B | publish | ... | post
3 | 1 | 2007-08-12| ... | C | publish | ... | post
4 | 1 | 2007-08-13| ... | D | publish | ... | post
5 | 3 | 2007-08-13| ... | E | publish | ... | post
What I like to do is get the user's post count and the title and id of the last post. The result should be the following:
AuthorID | TotalPosts | PostID | PostTitle
------------------------------------------
1 | 3 | 5 | E
3 | 2 | 4 | D
I've tried this:
SELECT
p1.post_author AS Author,
count(p1.post_author) AS Posts,
p2.post_title AS Title
FROM
wp_posts AS p1
LEFT JOIN
wp_posts As p2
ON
p1.ID = p2.ID
WHERE
p1.post_type = 'post'
AND
p1.post_status = 'publish'
GROUP BY
p1.post_author
ORDER BY
Posts DESC,
p2.post_date ASC
LIMIT
2
The problem is that I always get the title of the first post, not the title of the last post. Is there a way to get the title of the last inserted post?
respectfully
+3
source to share
4 answers
If you only want the last title for each author, you can use the built-in correlated subquery. It will be relatively fast with appropriate indices - especially if you only want results for the 2 most published authors:
SELECT
post_author AS Author,
COUNT(*) AS Posts,
( SELECT p2.post_title
FROM wp_posts AS p2
WHERE p2.post_author = p1.post_author
AND p2.post_type = 'post'
AND p2.post_status = 'publish'
ORDER BY p2.post_date DESC
LIMIT 1
) AS Title
FROM
wp_posts AS p1
WHERE
post_type = 'post'
AND
post_status = 'publish'
GROUP BY
post_author
ORDER BY
Posts DESC
LIMIT
2 ;
+4
source to share
Something like this should work:
SELECT p.post_author, p3.id, p3.post_title, COUNT(DISTINCT p.id) PostCount
FROM wp_posts p
JOIN (
SELECT Max(Post_Date) max_post_date, post_author
FROM wp_posts
GROUP BY post_author) p2
ON p.post_author = p2.post_author
JOIN wp_posts p3 on p.post_author = p3.post_author
AND p2.max_post_date = p3.post_date
GROUP BY p.post_author, p3.id, p3.post_title
This assumes only one date per post. If it isn't, will your id field be a max field as well?
+2
source to share
You can check if this option is faster or not:
SELECT t1.*, t2.post_title FROM
(SELECT
post_author AS Author,
COUNT(*) AS Posts,
Max(ID) AS MaxID
FROM
wp_posts AS p
WHERE
post_type = 'post' AND
post_status = 'publish'
GROUP BY post_author
ORDER BY Posts DESC
LIMIT 2) t1 LEFT JOIN wp_posts t2
ON t1.MaxID= t2.ID
+1
source to share