Wordpress - retrieve all post by one or more categories with a SQL query

Today a client asked me to remove all the posts from a given category to the auto-generated XML Sitemap of his WordPress blog and also redirect these posts to a new website (same slug, different host): here's a quick report of how I managed to do that, hoping that it would also help other System Administration that will be given these same tasks.

Get all posts by one or more categories

Since the sitemaps were generated by the Yoast SEO plugin, the first thing I did was to look up its configuration settings to see if there already was an option to exclude a specific category. Unfortunately, the plugin doesn't give the chance to do that, at least with the free version: luckily enough there was an "exclude posts by ID" feature that I could use instead, since the blog owner had told me that the category wouldn't been used anymore. All I had to do was retrieve a comma-separated list of post IDs to put into that textbox.

I managed to do that with the following SQL query, which will work in MySQL and MariaDB:

SELECT GROUP_CONCAT(ID) FROM wp_posts WHERE post_type="post" AND post_status = "publish" AND ID IN (
    SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (
	SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = "category" AND term_id IN (
            SELECT t.term_id FROM wp_terms t WHERE t.name IN ("categoryName1","categoryName2")
        )
    )
)

SELECT GROUP_CONCAT(ID) FROM wp_posts WHERE post_type="post" AND post_status = "publish" AND ID IN (

SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (

SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = "category" AND term_id IN (

SELECT t.term_id FROM wp_terms t WHERE t.name IN ("categoryName1","categoryName2")

)

The above query will only fetch published posts: be sure to change that if you want to include drafts and/or post revisions as well. Once done, put the query result into the Yoast SEO XML Sitemap exclusion textbox and you should be good to go.

Redirect all posts by one or more categories

I already explained the technique in this other post, however here's the deal:

open the functions.php file of the current theme.
add the following:

add_action('template_redirect', 'post_redirect_by_categories');
function post_redirect_by_categories() {
    global $post;
    // this array can contain category names, slugs or even IDs.
    $catArray = ['categoryName1', 'categoryName2'];

    if (is_single($post->ID) && has_category($catArray, $post)) {
        $new_url = "http://www.newhost.com/{$post->post_name}/?r=1";
        wp_redirect($new_url, 301);
        exit;
    }
}

add_action('template_redirect', 'post_redirect_by_categories');

function post_redirect_by_categories() {

global $post;

// this array can contain category names, slugs or even IDs.

$catArray = ['categoryName1', 'categoryName2'];

if (is_single($post->ID) && has_category($catArray, $post)) {

$new_url = "http://www.newhost.com/{$post->post_name}/?r=1";

wp_redirect($new_url, 301);

exit;

}

Replace the sample $catArray values with your chosen category (or categories), and you'll be good to go. Notice that I also appended a ?r=1 parameter to the target URL in order to allow the redirects issued here to be easily tracked by Google Analytics or other traffic monitoring tools.

That's it: for additional info regarding the redirection technique by taxonomies, I strongly suggest to read this post.