The desire is to return a result set JOINing authors to posts, and only returning posts from authors who have written at least 3 articles.
The relationship is like this:
wp_posts
post_author = wp_users.ID
wp_users
ID = wp_posts.post_author
ID = wp_usermeta.user_id
wp_usermeta
user_id = wp_users.ID, meta_key = “first_name” (actual “first_name” is the meta_value in this row, same with “last_name”)
So I JOIN wp_usermeta onto wp_users on the user_id/ID to get the “first_name” and “last_name” of the author. Then with that author list I JOIN wp_posts by the author ID’s - doing so only to get posts by named authors (this is for a “Contributor’s List”).
Now what I’d like to do is, in my final result set, only have a list of articles by authors who have written at least 3 articles. I think I can do this somehow with COUNT(*), but I’m not sure.
SELECT wp_users.ID, wp_posts.post_title, name1.meta_value as first_name, name2.meta_value as last_name FROM wp_users INNER JOIN wp_usermeta AS name1 ON name1.user_id=wp_users.ID AND name1.meta_key = ‘first_name’ INNER JOIN wp_usermeta AS name2 ON name2.user_id=wp_users.ID AND name2.meta_key = ‘last_name’ INNER JOIN wp_posts ON wp_posts.post_author=name1.user_id AND wp_posts.post_status=‘publish’ GROUP BY first_name
and then “HAVING COUNT(*)>2”?
How about formatting the query so others can read (and understand) it?
SELECT
wp_users.ID
, wp_posts.post_title
, name1.meta_value as first_name
, name2.meta_value as last_name
FROM wp_users
INNER JOIN wp_usermeta AS name1
ON name1.user_id=wp_users.ID
AND name1.meta_key = 'first_name'
INNER JOIN wp_usermeta AS name2
ON name2.user_id=wp_users.ID
AND name2.meta_key = 'last_name'
INNER JOIN wp_posts
ON wp_posts.post_author=name1.user_id
AND wp_posts.post_status='publish'
GROUP BY first_name
Try this (see the part in red I added)
SELECT
wp_users.ID
, wp_posts.post_title
, name1.meta_value as first_name
, name2.meta_value as last_name
FROM wp_users
INNER JOIN wp_usermeta AS name1
ON name1.user_id=wp_users.ID
AND name1.meta_key = 'first_name'
INNER JOIN wp_usermeta AS name2
ON name2.user_id=wp_users.ID
AND name2.meta_key = 'last_name'
INNER JOIN wp_posts
ON wp_posts.post_author=name1.user_id
AND wp_posts.post_status='publish'
[B][COLOR="Red"]INNER JOIN
(SELECT wp_users.ID
FROM wp_users
INNER JOIN wp_posts
ON wp_posts.post_author = wp_users.ID
AND wp_posts.post_status='publish'
GROUP BY wp_users.ID
HAVING COUNT(*) > 2
) AS a
ON wp_users.ID = a.ID[/COLOR][/B]
Guido,
This worked, thank you. To me it looks complicated, so I appreciate your help.
The last part of this is that I’m trying to sort it by what I’m calling “food_chain_number”. It’s just the level of user priveleges. 10 is highest, 1 is lowest. The query that I’m using is not working. For some reason, it puts the 2’s at the top, then the sole 10, then all of the 1’s. The 10 should be at the top. I’d appreciate any help/insight.
SELECT
wp_users.ID
, wp_users.user_nicename
, name1.meta_value as first_name
, name2.meta_value as last_name
, food_chain.meta_value as food_chain_position
, food_chain_level.meta_value as food_chain_number
FROM wp_users
INNER JOIN wp_usermeta AS name1
ON name1.user_id=wp_users.ID
AND name1.meta_key = 'first_name'
INNER JOIN wp_usermeta AS name2
ON name2.user_id=wp_users.ID
AND name2.meta_key = 'last_name'
INNER JOIN wp_usermeta AS food_chain
ON food_chain.user_id=wp_users.ID
AND food_chain.meta_key = 'wp_capabilities'
INNER JOIN wp_usermeta AS food_chain_level
ON food_chain_level.user_id=wp_users.ID
AND food_chain_level.meta_key = 'wp_user_level'
INNER JOIN
(SELECT wp_users.ID
FROM wp_users
INNER JOIN wp_posts
ON wp_posts.post_author = wp_users.ID
AND wp_posts.post_status='publish'
GROUP BY wp_users.ID
HAVING COUNT(*) > 2
) AS a
ON wp_users.ID = a.ID
ORDER BY food_chain_number DESC
Let me guess, that column is a (var)char?
Change its type into an appropriate numeric type and the sort will work ok.