2. Algorithm
  3. wp query - get complex results set according to category structure

wp query - get complex results set according to category structure

admin2025-06-02  1

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.

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.

  • wp-query
  • categories
  • tax-query
  • advanced-taxonomy-queries
Share Improve this question edited Mar 11, 2019 at 9:09 Gufran Hasan 6918 silver badges20 bronze badges asked Mar 11, 2019 at 7:59 Janith ChinthanaJanith Chinthana 4082 gold badges8 silver badges26 bronze badges 5
  • Is there ever a circumstance where a post is tagged with flight but with no child category? – Adam Commented Mar 11, 2019 at 9:11
  • only IF content writer make a mistake. ideally it should always tagged the children as well. – Janith Chinthana Commented Mar 11, 2019 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. – Adam Commented Mar 11, 2019 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' – Adam Commented Mar 11, 2019 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 Commented Mar 11, 2019 at 9:39
Add a comment  | 

1 Answer 1

Reset to default 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.

转载请注明原文地址:http://conceptsofalgorithm.com/Algorithm/1748814724a313947.html

wp queryget complex results set according to category structure

最新回复(0)