CODEWITHSUNDEEP
mysqlsqlwordpressphpmyadmin
Guru Surfer
Guru Surfer

Reputation: 121

SQL for WP to delete users with multiple meta keys and comments

I need help putting together an SQL that can to remove users if they don't have (metakey1 or metakey2) and (does not have comments)

I have this SQL which does it for single meta_key

SELECT *
FROM wp_users LEFT JOIN wp_usermeta
ON wp_users.ID = wp_usermeta.user_id
AND wp_usermeta.meta_key = 'metakey1'
WHERE wp_usermeta.user_id IS NULL

How can i extend the above SQL to do that?

Upvotes: 0

Views: 845

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use in in the on clause:

SELECT u.*
FROM wp_users u LEFT JOIN
     wp_usermeta um
     ON u.ID = um.user_id AND
        um.meta_key IN ('metakey1', 'metakey2', 'comments')
WHERE um.user_id IS NULL;

You get no matches only if all the metakeys are missing, which I think is what you are asking for.

EDIT:

You seem to want:

SELECT u.*
FROM wp_users u
WHERE NOT EXISTS (SELECT 1
                  FROM wp_usermeta um
                  WHERE u.ID = um.user_id AND
                        um.meta_key IN ('metakey1', 'metakey2')
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM wp_comments c
                  WHERE u.ID = c.user_id
                 );

I prefer NOT EXISTS if you are going to have multiple comparisons to different tables.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Try doing a delete with an exists clause asserting the requirement of either of two keys:

DELETE
FROM wp_users wp1
WHERE NOT EXISTS (SELECT 1 FROM wp_usermeta wp2
                  WHERE wp1.ID = wp2.user_id AND
                        wp2.meta_key IN ('metakey1', 'metakey2'));

Note that the following where clause is no longer needed in the version of the query I wrote above:

WHERE wp_usermeta.user_id IS NULL

It is no longer needed because NOT EXISTS now handles the job which the exclusion join was handling previously.

Upvotes: 1

Related Questions