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;
- However, it should still be in the result set if one of its siblings(
businessorfirst) were tagged. - It should not consider the posts where
allianceor its one of the child were tagged IFeconomyalso tagged in the same posts [but this rule anyway full fill when we exclude theeconomycategory 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 Answer
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.
