Complex Category selection as per user input

Question

This is going to be a big explanation as I don't have any shortcut ways to explain the whole scenario.

Let's say I have following category structure. (category ids are withing bracket in front of the each category)

First Level 1 (100)
    Second Level 1 (104)
        Third Level 1 (108)
        Third Level 2 (109)
        Third Level 3 (110)
        Third Level 4 (111)
    Second Level 2 (105)
        Third Level 1 (112)
        Third Level 2 (113)
First Level 2 (101)
    Second Level 1 (106)
        Third Level 1 (114)
        Third Level 2 (115)
        Third Level 3 (116)
    Second Level 2 (107)
        Third Level 1 (117)
        Third Level 2 (118)
First Level 3 (102)
First Level 4 (103)

I have requirement to give users freedom to select the categories (which I call filters) what they need to see in the blog. So, I have given the same structure for users by pre-selecting all categories, if someone needs to exclude a category then user can untick it.

But I have certain logic to show posts according to filters as bellow.

How filters work:

Second Level is an internal level which has nothing to do with user selection, although it helps to keep the hierarchy. Users can only tick the Third Level categories by ticking their parent First Level. If user untick First Level the children are automatically locked.

LOGIC: (I will consider a post for explanation purposes)

FIRST RULE:

If someone tick First Level category and if it has the children I should go to the second step as bellow.

Post will only be shown if at least one of the Third Level category of the post are also ticked in the filters of the user. But this we need to check under each Second Level sections separately.

E.g. Third Level 1 and Third Level 2 are ticked in post (under the First Level 1 & Second Level 1)

User unticked Third Level 1 but ticked Third Level 2, then it will still be shown (green light)

But user ticked only Third Level 3 (under the First Level 1 & Second Level 1) in filters, then the post would not be shown. (red light)

So, a post will only be shown if ALL Second Level sections are showing a green light. In above example Second Level 1 and Second Level 2 (under the First Level 1) should fulfilled the above logic to show the post.

Eg: User interested in First Level 1 (from filters). Post is also classified as First Level 1

Then I should go to the second step which I need to check all the Second Level sections fulfilling the above logic.

• Second Level 1
• Second Level 2

If one of the Second Level section shows a red light the deal will not be shown.

SECOND RULE:

If one of First Level categories doesn’t have any children, then the tick of it self taking as fulfilling the filter requirements.

THIRD RULE:

When ever any post has fulfilled at least one First Level requirement that considered as the post will be shown on the blog.

So that is the logic to show posts on the blog as per the users filter inputs. Now what I really need is to build a WP_Query. Honestly, I have no clue even how to start it as I am not having much experience in Wordpress. But my initial try was with category__in with all the ticked category ids which is not fulfilling the new logic.

Any help will be highly appreciate as I am stuck on this for few days now.

Thanks in advance.

UPDATE 1:

if someone untick 110 & 111 rest all is considered to be ticked, then the sameple algorithm with tax_query would be like bellow. But in real this is not loading at all.

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'OR',
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 108,109 ),
                'operator' => 'IN',
            ),
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 112,113 ),
                'operator' => 'IN',
            )
        ),
        array(
            'relation' => 'AND',
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 114,115,116 ),
                'operator' => 'IN',
            ),
            array(
                'taxonomy' => 'category',
                'field'    => 'term_id',
                'terms'    => array( 117,118 ),
                'operator' => 'IN',
            )
        ),
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 102, 103 ),
            'operator' => 'IN',
        ),
    ),
);

Answer 1

The query you need to use is not so complicated as you basically just what to get posts from only certain taxonomies. You are right that you should use the tax_query.

The first thing that you should do is collate all your category IDs in only one array as there is no need for multiple ones since you are only using the IN condition (that means posts that belong to at least one category in the list). It makes code error-prone and slow to have multiple tax queries when one would suffice.

The problem with your query is that you are using a AND relation that is never true in your case (ie. you don't have a post that is at the same time in categories 108 or 109, and in categories 112 or 113).

So I would suggest you use WP_Query args like so:

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 108, 109, 112, 113, 114, 115, 116, 117, 118, 102, 103 ),
            'operator' => 'IN',
        ),
    ),
);

I understand that you have a multi-step logic to select the posts to display in certain categories. I have given it some thought and it would be quite cumbersome to implement this logic in a tax query through various AND/OR relationships. I think you should do all of the category selection in PHP and end up with an array of category ids that you would use like above. This will allow you to separate your logic from the actual posts selection.

Answer 2

After a long research, the only way I found was, get the results with custom sql query as bellow.

SELECT SQL_CALC_FOUND_ROWS blog_posts.* FROM (
SELECT blog_posts.* FROM blog_posts 
INNER JOIN blog_term_relationships AS tt0 ON (blog_posts.ID = tt0.object_id AND tt0.term_taxonomy_id IN (108,109)) 
INNER JOIN blog_term_relationships AS tt1 ON (blog_posts.ID = tt1.object_id AND tt1.term_taxonomy_id IN (112,113)) 
WHERE blog_posts.post_type = 'post' AND (blog_posts.post_status = 'publish') 
UNION 
SELECT blog_posts.* FROM blog_posts 
INNER JOIN blog_term_relationships AS tt3 ON (blog_posts.ID = tt3.object_id AND tt3.term_taxonomy_id IN (114,115,116)) 
INNER JOIN blog_term_relationships AS tt4 ON (blog_posts.ID = tt4.object_id AND tt4.term_taxonomy_id IN (117,118)) 
WHERE blog_posts.post_type = 'post' AND (blog_posts.post_status = 'publish') ) blog_posts 
GROUP BY blog_posts.ID ORDER BY blog_posts.post_date DESC LIMIT 0, 12;

following code was used to retrieve the results as per the https://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

$pageposts = $wpdb->get_results($querystr, OBJECT);