By Janith Chinthana


2019-03-11 07:59:03 8 Comments

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.

1 comments

@userabuser 2019-03-11 09:17:55

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.

@Janith Chinthana 2019-03-11 09:37:58

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.

@userabuser 2019-03-11 09:48:28

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 2019-03-11 09:50:28

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).

@Janith Chinthana 2019-03-11 09:59:22

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.

@userabuser 2019-03-11 19:17:33

@JanithChinthana observe the addendum...

@Janith Chinthana 2019-03-12 06:39:20

really appreciate your time and try. This was my initial try. However, this will not fulfill the rule number two. let's say that economy and oneworld were tagged in a post. according to your answer it will be in the result set. But I need to exclude that as the post were tagged as economy.

@userabuser 2019-03-12 15:55:16

@JanithChinthana observe the next addendum... hopefully this time I understand you :)

Related Questions

Sponsored Content

1 Answered Questions

2 Answered Questions

[SOLVED] Multiple, nested tax_query relation

  • 2014-03-26 14:58:26
  • Shane
  • 2261 View
  • 1 Score
  • 2 Answer
  • Tags:   tax-query

1 Answered Questions

2 Answered Questions

Custom Query Arguments

1 Answered Questions

[SOLVED] Get product list of given category

2 Answered Questions

[SOLVED] Complex Category selection as per user input

  • 2018-02-11 07:43:54
  • Janith Chinthana
  • 136 View
  • 1 Score
  • 2 Answer
  • Tags:   wp-query categories

1 Answered Questions

[SOLVED] Pagination works in custom query loop but it doesn't work in shortcode

  • 2013-10-31 03:52:45
  • elenakoroleva
  • 1039 View
  • 0 Score
  • 1 Answer
  • Tags:   wp-query pagination

2 Answered Questions

[SOLVED] get posts from 2 categories. (2 posts)

1 Answered Questions

[SOLVED] Ajax and WP_Query/tax_query parameter

  • 2012-12-09 03:55:08
  • jkhedani
  • 2506 View
  • 2 Score
  • 1 Answer
  • Tags:   wp-query ajax

Sponsored Content