Hello I’m struggling to find an answer talking specifically about counting post and changing roles but on mysql language not php in the wordpress source. I’m not creating a plugin neither
I want to see all the users that has "simbiosis" roles with zero posts so i can change their role to another one.
I understand that roles are stored in user_meta table when meta_key column says capabilities and then in meta_value equals to the rol I’m looking for. So based on this here is what i got so far:
SELECT u.ID
, u.user_nicename
, um.meta_key
, um.meta_value
, p.post_author
, p.ID AS postid
, COUNT(p.ID)
FROM wps_users AS u
JOIN wps_usermeta AS um ON u.ID = um.user_id
JOIN wps_posts AS p ON p.post_author = u.ID
WHERE p.post_type='post' AND p.post_status='publish' AND p.post_author = u.ID
AND um.meta_key LIKE '%capabilities%'
AND um.meta_value LIKE '%simbiosis%'
GROUP BY p.post_author
It seems like the COUNT(p.ID) is not really counting post ID rows because that number does not match with the number of post the users has in wordpress dashboard. What am I missing?
I’m counting that column because if an user has several posts those posts will have different ID but i don’t get why it does not match user real posts.
After this, my idea will be to replace the meta_value data with a new value.
Thanks in advance on your guidance.
UPDATE
I realized the query is ok since is only counting published posts and WordPress user panel includes draft as well. That’s why it does not match however still having issues when trying to find users with zero published posts.
If I add:
HAVING COUNT(p.ID) = 0
I get no results. In fact query is not showing any 0 results. Not sure if the COUNT command as anything to do with it.