get complex results set according to category structure

Thinking aloud here…

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'OR',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 202, 203 ),
            'include_children' => 1,
        ),
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 100 ),
                'include_children' => false,
            )
        ),
    ),
);
$query = new WP_Query( $args );

Get all posts in taxonomy term IDs 202, 203, etc…

OR

Get all posts in taxonomy term ID 100 only (exclude children)

In fact the above could just be expressed as (NO NESTED QUERY):

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'OR',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 202, 203 ),
            'include_children' => 1,
        ),
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 100 ),
            'include_children' => false,
        )
    ),
);
$query = new WP_Query( $args );

Get all posts in taxonomy term IDs 202, 203, etc…

OR

Get all posts in taxonomy term ID 100 only (exclude children)

And again… alternatively (if you force writers to always set child categories by way of a hook on save_post or similar):

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 202, 203 ),
            'include_children' => 1,
        ),
    ),
);
$query = new WP_Query( $args );

Get all posts in taxonomy term IDs 202, 203, etc…

UPDATE

This would be my quick approach:

$terms = get_terms(array(
        'taxonomy' => 'category',
        'exclude' => 201
));

$term_ids = array_column( $terms, 'term_id' );

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => $term_ids,
            'include_children' => 1,
        ),
    ),
);
$query = new WP_Query( $args );

$post_term_ids = [];

foreach ( $query->posts as $post ) {
    $post_term_ids[$post->ID] = implode( ', ', array_column(
        wp_get_object_terms( $post->ID, 'category' ), 'name', 'term_id'
    ));
}

My sample data set returned is:

array (
  547 => 'Alliance, Business, Class, Flights, Oneworld',
  540 => 'Business, Class, Economy, Flights',
  605 => 'Star',
  524 => 'Alliance, Flights, Oneworld',
  594 => 'Skyteam',
  569 => 'Star',
  551 => 'Flights, Oneworld, Star',
  582 => 'Business',
  528 => 'Business, Class, Economy, Flights',
  565 => 'Skyteam',
)

If I were to remove the 'exclude' => 201 when calling get_terms I would see a result similar to this:

array (
  547 => 'Alliance, Business, Class, Flights, Oneworld',
  540 => 'Business, Class, Economy, Flights',
  605 => 'Star',
  524 => 'Alliance, Flights, Oneworld',
  594 => 'Skyteam',
  569 => 'Star',
  551 => 'Flights, Oneworld, Star',
  582 => 'Business',
  528 => 'Business, Class, Economy, Flights',
  565 => 'Economy', // <-- WHAT WE DO NOT WANT
)

As you can see I am returning all posts except those that have economy alone. Where economy is present, but so too another classification, then that post is returned. The keys are the post IDs.

Note: my example results show what your potential values might be if content writers forget to assign ancestors. As mentioned, that’s solvable in another question.

UPDATE #2

# change the IDs below to match your environment
$class    = 92; // ancestor
$alliance = 96; // ancestor
$economy  = 93; // child

$terms_class = get_terms(array(
    'taxonomy' => 'category',
    'exclude'  => [$alliance, $economy],
    'child_of' => $class,
));

$terms_alliance = get_terms(array(
    'taxonomy' => 'category',
    'child_of' => $alliance,
));

$term_ids_class    = array_column($terms_class, 'term_id');
$term_ids_alliance = array_column($terms_alliance, 'term_id');

$args = array(
    'post_type'      => 'post',
    'posts_per_page' => -1,
    'tax_query'      => array(
        'relation' => 'OR',
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => $term_ids_class,
            ),
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => $term_ids_alliance,
                'operator' => 'NOT IN',
            ),
        ),
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => $term_ids_alliance,
            ),
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => $economy,
                'operator' => 'NOT IN',
            ),
        ),

    ),
);

$query = new WP_Query( $args );

$post_term_ids = [];

foreach ( $query->posts as $post ) {
    $post_term_ids[$post->ID] = implode( ', ', array_column(
        wp_get_object_terms( $post->ID, 'category' ), 'name', 'term_id'
    ));
}

In my sample data set I get a result like this:

array (
  547 => 'Alliance, Business, Class, Flights, Oneworld',
  540 => 'Business, Class, Economy, Flights',
  524 => 'Alliance, Flights, Oneworld',
  594 => 'Skyteam',
  569 => 'Star',
  551 => 'Flights, Oneworld, Star',
  582 => 'Business',
  528 => 'Business, Class, Economy, Flights',
  589 => 'Oneworld',
  603 => 'Oneworld',
  584 => 'Oneworld',
  585 => 'First',
  601 => 'First',
  543 => 'Business, Class, Economy, Flights',
  572 => 'First',
  578 => 'Business',
  592 => 'Alliance, Business, Class, Flights, Star',
  563 => 'Star',
  559 => 'Skyteam',
  575 => 'Star',
  549 => 'Flights, Oneworld, Skyteam',
  596 => 'Star',
  534 => 'Class, First, Flights',
  561 => 'Star',
  556 => 'Star',
  587 => 'Oneworld',
)

And the SQL is as follows:

SELECT 
  wp_posts.* 
FROM 
  wp_posts 
  LEFT JOIN wp_term_relationships ON (
    wp_posts.ID = wp_term_relationships.object_id
  ) 
  LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
WHERE 
  1 = 1 
  AND (
    (
      wp_term_relationships.term_taxonomy_id IN (94, 95) 
      AND wp_posts.ID NOT IN (
        SELECT 
          object_id 
        FROM 
          wp_term_relationships 
        WHERE 
          term_taxonomy_id IN (97, 98, 99)
      )
    ) 
    OR (
      tt1.term_taxonomy_id IN (97, 98, 99) 
      AND wp_posts.ID NOT IN (
        SELECT 
          object_id 
        FROM 
          wp_term_relationships 
        WHERE 
          term_taxonomy_id IN (93)
      )
    )
  ) 
  AND wp_posts.post_type="post" 
  AND (
    wp_posts.post_status="publish" 
    OR wp_posts.post_status="private"
  ) 
GROUP BY 
  wp_posts.ID 
ORDER BY 
  wp_posts.post_date DESC

Not incredibly efficient, but it’s a brute force way of going about it.

Related Posts:

  1. tax_query shows no results if nothing is selected
  2. Get image of latest post from taxonomies/categories
  3. Combining categories (Query posts with multiple taxonomy terms)
  4. Finding WordPress Posts assigned to multiple categories
  5. Tax_Query using WP_Query not working
  6. wp_query with meta_query and tax_query
  7. list all post who have mutual taxonomy as current taxonomy!
  8. Why pagination is not working with tax_query param?
  9. display all posts from category with and without terms in chronological order
  10. In Product Category archives how to show Posts having same/similar prod_cat slug structure?
  11. List posts of terms but exclude one term
  12. Output ACF field dynamicaly within a taxonomy loop [closed]
  13. WordPress tax_query “and” operator not functioning as desired
  14. Exclude or Include category ids in WP_Query
  15. Custom Taxonomy and Tax_Query
  16. How to query only for products with status “in stock” in WooCommerce? [closed]
  17. WP_Query to show post from a category OR custom field
  18. ajax category filter
  19. Display recent posts from the same category as current post in sidebar
  20. WP_query taxonomy + get all posts with two terms from same taxonomy
  21. Counting number of posts with Category B in Category A
  22. SQL Statement generated by WP_Query not producing expected results
  23. Use Transient API to cache queries for all posts in all categories?
  24. WooCommerce: filter by parent product’s taxonomy and product variation’s meta data
  25. How to combine tax_query and date_query in WordPress
  26. Querying by taxonomy vs Querying by Custom fields Speed Comparison
  27. Using tax_query reverses my post_type argument in a custom WP_Query
  28. WP Query for Posts (Products) in Specific Category that has 2 Specific Tags (*AND* both tags not *OR*)
  29. wp_query display posts from same category of the post
  30. WP_Query order by date in meta_value
  31. WP_Query tax_query problem
  32. Searching through different categories on different pages code is not working
  33. How to select posts from one category but exclude posts in another category?
  34. Empty tax_query array returns an empty array
  35. Query Custom Post Type Order By Taxonomy Field
  36. Get Category Archive Template Name Dynamically
  37. How can you get first post, last post and post count in a category?
  38. Shortcode to show thumbnail and link for 3 random posts
  39. How to get count of posts assigned to given category?
  40. Category applied to pages, creates multiple breadcrumb entries after a search query (On the translated site)
  41. category query for pages not working
  42. Query posts from category A, and from either category B or C
  43. Filtering out child category posts from parent category archive not working
  44. Automatically Query Parent Taxonomy
  45. Limiting number of related posts
  46. Category ‘pad_counts’ & ‘parent’ conflict
  47. Custom taxonomy.php not working
  48. Order or Orderby in tax_query (How to define order of terms in WP_Query)
  49. wp_query is showing posts from other categories
  50. WP_Query Taxonomy categories filtering
  51. How to load a script code only in posts?
  52. Get posts in taxonomy randomly
  53. How to search for meta_query LIKE or tax_query LIKE and grab these posts on search results?
  54. How to display a list of posts in same child category as current post
  55. How to exclude a category name from showing?
  56. Get the child category ID of current category
  57. Related Posts Excluding Certain Categories
  58. Use have_posts() with array of post results retrieved by $wpdb->get_results
  59. Post incorrectly excluded when using “category__in”?
  60. Related posts by current posts child category
  61. Any quicker alternative for WP_Query “NOT IN”
  62. Tax Query on product_cat using NOT IN as operator does not exclude that category
  63. Use get_cat_ID to retreive multiple category IDs
  64. query hook parse_tax_query function takes no effect
  65. How can I have sticky posts while ALSO showing posts from a specific category using one WP_Query?
  66. How to use the Term Object from a custom select field in a query
  67. How to display the category dropdown auto search list when key press?
  68. Categories In English version showing not canonical URL, instead shows query search result
  69. What code to use in an array to call the current sub-category?
  70. Get categories within specific term
  71. Pagination for Category does not work
  72. Exclude parent categories from recent posts list
  73. WP_Query showing all posts, except from category X, unless it’s also in Y
  74. Single query for multiple categories
  75. Display 3 levels of categories on page
  76. WordPress tax_query ignoring relation OR
  77. Adding Category in WP_Query Not Working
  78. Combine Tax Archive and Meta_Query in WP_Query
  79. Taxonomy Terms That Don’t Exist Display Results
  80. Help displaying related categories
  81. how to avoid reloading/refresh the page when displaying the post of wp_list_categories
  82. Category Archive not working for pages
  83. How can I get all the posts that are related with a specific taxonomy term?
  84. Show full category tree for a year with all post titles?
  85. How to combine nested tax_query logic with other nested query logic?
  86. tax_query not working for taxonomy slug
  87. Extending WP Query: Custom geolocation meta values work, but tax_query breaks
  88. Custom loop – Isolating post meta output depending on current query taxonomy terms
  89. Display Count of posts
  90. Display post list within category list sorted by name [duplicate]
  91. tax_query showing no results
  92. WP_Query on “property” in the options table
  93. Using tax_query to get single post per category
  94. Related posts with WP_Query
  95. Main loop querying current template’s info only in custom category archive pages, not my posts
  96. query_vars category_name only display one catgory out of multiple categories
  97. Incorrect taxonomy ID assigned to post with custom query
  98. Custom query for certain post type OR another post type with a certain category
  99. Pagination only showed when no category is set in wp_query
  100. Using WP Query, I want to include all posts in category 1 as long as they are not also in category 2