2

I need to get a complex result set from a WordPress database according to the category structure.

First I will try to explain my category structure. I have three level structure as bellow.

flight [level 1] [ID : 100]
    - class      [level 2] [ID : 200]
        -- economy  [level 3] [ID : 201]
        -- business [level 3] [ID : 202]
        -- first    [level 3] [ID : 203]
    - alliance   [level 2] [ID : 210]
        -- star     [level 3] [ID : 211]
        -- oneworld [level 3] [ID : 212]
        -- skyteam  [level 3] [ID : 213]

Now the algorithm:

I need to get all the posts tagged as flight category or its one of the child with the following rules.

I need to exclude the posts tagged as economy;

  1. However, it should still be in the result set if one of its siblings(business or first) were tagged.
  2. It should not consider the posts where alliance or its one of the child were tagged IF economy also tagged in the same posts [but this rule anyway full fill when we exclude the economy category in general]

My approach so far:

I was trying tax_query with the following type of args

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 100 ),
            'include_children' => 1,
        ),
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 200 ),
                'include_children' => 1,
            ),
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 201 ),
                'operator' => 'NOT IN',
            ),
        ),
    ),
);
$query = new WP_Query( $args );

But the issue here is, it is not fulfilling the rule number 1. Even though I need to have posts with the tag of business and first irrespective of economy, the above query just ignores all the posts tagged with economy.

I would even consider the pure SQL approach as well. Any help would be highly appreciating as I was struggling with this for a couple of days now.

  • Is there ever a circumstance where a post is tagged with flight but with no child category? – userabuser Mar 11 at 9:11
  • only IF content writer make a mistake. ideally it should always tagged the children as well. – Janith Chinthana Mar 11 at 9:15
  • If that is the case or a rare case you could simple get all children IDs from class excluding 201 and express this via one taxonomy query array alone. – userabuser Mar 11 at 9:21
  • Also... You can prevent content writer from mistakenly NOT assigning child terms with hooks. That's another question though. But very doable. In which case I think you can get away with just the one 'operator' => 'IN' – userabuser Mar 11 at 9:23
  • let's say I have a post tagged with economy and star. according to my rules I need to exclude that post from the results set. But when we add alliance category and children it is automatically considering the above example post as well. – Janith Chinthana Mar 11 at 9:39
2

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.

  • these all basically ignore the alliance category. Event though we add them separately, issue would still remain. let's say I have a post tagged with economy and star. according to my rules I need to exclude that post from the results set. But when we add alliance category and children it is automatically considering the above example post as well. Hope you get the point. – Janith Chinthana Mar 11 at 9:37
  • Ok can you clarify because your question did not mention star. You want all posts in 100 except for where the post ONLY has 201 and its ancestor 200 and 100? – userabuser Mar 11 at 9:48
  • The second and third example about should give you what you want and IF not, then repeat the clause for those under alliance bar star. Simplest approach in my opinion is to use an IN clause containing all IDs except for economy and star (if star is not applicable). – userabuser Mar 11 at 9:50
  • please follow the rule number 2. Basically I need all the posts which were tagged flight or its children, except economy. But if post was tagged business or first, that should override the economy exclusion. please note that post can be tagged with any number of categories. – Janith Chinthana Mar 11 at 9:59
  • @JanithChinthana observe the addendum... – userabuser Mar 11 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.