Product Filtering by Attribute | WordPress.org

Hello

We have implemented some custom code so that when the user selects an attribute term on the front-end, it also checks for other terms we have mapped via an array too.

For example, the query below is the query which is generated now as a result of our code. You can see it checks for multiple terms 1225 and 413 in wp_term_relationships.term_taxonomy_id. 

1225 is the term they selected on the front-end, and 413 is mapped to it via our code.

The bit we’re really struggling with though is the bit which says ‘WHERE term_id in (1225)’. We want this to update to WHERE term_id in (1225,413)’ – but there’s nothing we can do to hook into to amend this.

Does anyone have any recommendations of which filters/hook to use to amend this as everything we’ve tried doesn’t work. The only part we can seem to change is the bit which is already changed , which is this bit “wp_term_relationships.term_taxonomy_id IN (1225) OR wp_term_relationships.term_taxonomy_id IN (413)”

Thanks

Our Current Query:

       SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (1225)
OR
wp_term_relationships.term_taxonomy_id IN (413)
)   AND ((wp_posts.post_type="product" AND (wp_posts.post_status="publish" OR wp_posts.post_status="wfob-disabled" OR wp_posts.post_status="wfocu-disabled" OR wp_posts.post_status="open" OR wp_posts.post_status="closed" 
    OR wp_posts.post_status="private"))) AND (
                         wp_posts.ID IN ( SELECT product_or_parent_id FROM (
                        SELECT product_or_parent_id
                        FROM wp_wc_product_attributes_lookup lt
                        WHERE term_id in (1225)
                         AND in_stock = 1
                    ) temp ))
        GROUP BY wp_posts.ID
        ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC
        LIMIT 0, 20;

What we want it to be (very minor change in term_id section)

     SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (1225)
OR
wp_term_relationships.term_taxonomy_id IN (413)
)   AND ((wp_posts.post_type="product" AND (wp_posts.post_status="publish" OR wp_posts.post_status="wfob-disabled" OR wp_posts.post_status="wfocu-disabled" OR wp_posts.post_status="open" OR wp_posts.post_status="closed" 
    OR wp_posts.post_status="private"))) AND (
                         wp_posts.ID IN ( SELECT product_or_parent_id FROM (
                        SELECT product_or_parent_id
                        FROM wp_wc_product_attributes_lookup lt
                        WHERE term_id in (1225,413)
                         AND in_stock = 1
                    ) temp ))
        GROUP BY wp_posts.ID
        ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC
        LIMIT 0, 20;