Custom query with category exclusion and post-meta "whitelist"

Question

I have two widgets, one shows News while the other shows everything except News (We'll call the second one Blog). The blog portion is currently working correctly.

The News posts are either categorized as In the News OR have the custom post meta in-the-news, set to 1. It's important to note that not all posts will have the custom post meta defined.

The main problem I am facing is that I need to compare post meta based on whether the post is categorized one way or another.

I may need to do a custom database query for this behavior, at least for the News. Is it possible to do a custom query and convert it to a standard WP_Query object?

---

What I have tried

Query for News.

Problem: Only shows posts within category 75 that have in-the-news = 1.

Desired result: Show all posts from category 75 regardless of the value of in-the-news, and any additional posts from outside the category which have in-the-news = 1

query_posts(array(
  'ignore_sticky_posts' => true,
  'posts_per_page' => 3,
  'cat' => "75", // Only posts within category 75 (News)

  // Including posts tagged to show "In the News"
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key' => 'in-the-news',
      'value' => '1',
      'compare' => '=',
    ),
    array( 
      'key' => 'in-the-news',
      'compare' => 'NOT EXISTS',
      'value' => '',
    ),
  )
));

Here is the query for the Blog:

This query appears to be working correctly, showing all posts outside of category 75, and hiding posts with in-the-news = 1.

query_posts(array(
  'ignore_sticky_posts' => true,
  'posts_per_page' => 3,
  'cat' => "-75", // All posts EXCLUDING those in category 75 (News)

  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key' => 'in-the-news',
      'value' => '0',
      'compare' => '=',
    ),
    array( 
      'key' => 'in-the-news',
      'compare' => 'NOT EXISTS',
      'value' => '',
    ),
  )
));

---

Solution

Thanks to s_ha_dum for the amazing examples, here is the final query I am using. The logic here is that we do a custom MySQL query for an array of post IDs, then do a standard WP_Query using the returned IDs.

Note: The logic here was a bit more complicated than the original post. Basically, we originally had "News" and "Blog". But now we also have "Risk". Also, the in-the-news post meta was changed to show-in-blog as we reverse the functionality. I can't expect anyone else to ever understand the reasoning for this in the first place, as even I think it is ridiculous (But the client wants it, so be it!)

Here is my query, for the "Blog" widget. Slight variations are made for the "News" area. I'm not sure if the query could be cleaned up. I'm also not sure if it's a bad idea to perform so many SELECTs within the same query.

global $wpdb;

$query = // Splitting the SQL query to it's own code block for syntax highlighting!

---

  SELECT `ID`
  FROM {$wpdb->posts} posts
  WHERE (

    /* Exclude posts in News (94) 
       Ignore show-in-blog option for News here, 
       as News is displayed on the same page */
    94 NOT IN ( 
      SELECT `term_taxonomy_id` 
      FROM {$wpdb->term_relationships}
      WHERE `object_id` = posts.`ID`
    )

    AND

    /* Exclude posts in Risk (96), 
       But show Risk posts that have the postmeta "show-in-blog" */
    (

      96 NOT IN ( 
        SELECT `term_taxonomy_id` 
        FROM {$wpdb->term_relationships}
        WHERE `object_id` = posts.`ID`
      )
      OR
      (
        96 IN ( 
          SELECT `term_taxonomy_id` 
          FROM {$wpdb->term_relationships}
          WHERE `object_id` = posts.`ID`
        )
        AND
        1 IN (
          SELECT `meta_value` 
          FROM {$wpdb->postmeta}
          WHERE 
          `meta_key` = 'show-in-blog'
          AND `post_id` = posts.`ID`
        )
      ) /* OR */

    ) /* AND */

  ) /* WHERE */

  ORDER BY `post_date` DESC;

---

$args = array(
  // Only include IDs returned by the above query
  'post__in' => $wpdb->get_col( $query ),
  'posts_per_page' => 10,
);

$wp_query = new WP_Query( $args );

get_template_part('loop', 'frontpage');

Answer 1

Is it possible to do a custom query and convert it to a standard WP_Query object?

Yes. You can do something like...

$post_ids = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} WHERE ....");
$posts_qry = new WP_Query(array('post__in'=> $posts_ids,'orderby' => 'post__in'));

The second query will preserve the order of results from the first.

I am fairly sure that you cannot make that first query with WP_Query, or any other function. The logic is too complicated. You'd need a JOIN or a WHERE clause that WP_Query does not support. Unfortunately, writing that SQL is going to be complicated too, just to get the category.

Completely hand-writing the query is of course your first option. The best I can do without hand-writing any SQL would be:

$post_ids = new WP_Query(array(
  'fields' => 'ids',
  'ignore_sticky_posts' => true,
  'posts_per_page' => 3,
  'cat' => "75", // Only posts within category 75 (News)
));

$post_ids_2 = new WP_Query(array(
  'fields' => 'ids',
  'ignore_sticky_posts' => true,
  'posts_per_page' => 3,
  'post__not_in' => $post_ids->posts,
  'meta_query' => array(
    array(
      'key' => '_edit_lock',
      'value' => '0',
      'compare' => '=',
    ),
  )
));
$post_ids = $post_ids->posts + $post_ids_2->posts;
$posts_qry = new WP_Query(array('post__in'=> $posts_ids,'orderby' => 'post__in'));

That is 3 queries (ouch) and ordering could be an issue if you are worried about that.

Your other option is alter the query via a couple of filters. The WP_Query arguments are going to deal with the in-the-news = 1 part.

$post_ids_v3 = new WP_Query(array(
  'ignore_sticky_posts' => true,
  'posts_per_page' => 20,
  'my_variable' => true,
  // Including posts tagged to show "In the News"
  'meta_query' => array(
    array(
      'key' => 'in-the-news',
      'value' => '1',
      'compare' => '=',
    )
  )
));

Notice that you are leaving out your category component and passing an extra unofficial parameter named my_variable. I do not know if passing additional variables like that is by design, so be aware that that may be unofficial behavior. You are going to use that to add your category component into the query with two filters.

function posts_join_wpse_98652($join,$qry) {
  global $wpdb;
  if (true === $qry->get('my_variable')) {
    $join .= " JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)";
  }
  return $join;
}
add_filter('posts_join','posts_join_wpse_98652',1,2);

function posts_where_wpse_98652($where,$qry) {
  global $wpdb;
  if (true === $qry->get('my_variable')) {
    $where .= " OR {$wpdb->term_relationships}.term_taxonomy_id IN (75)";
  }
  return $where;
}
add_filter('posts_where','posts_where_wpse_98652',1,2);

That is not even close to well tested, but it appears to be working when I try. Still, these things can be hard to get right, maybe I've made a mistake. Barely tested. Possibly buggy. Caveat emptor. No refunds.