How To Exclude Results From Custom WordPress MySQL Query By Taxonomy Term

Question

I only want to show posts which do not have the term 'brand-slug' for the taxonomy 'product-brand'.

My current query doesn't apply the filter:

SELECT DISTINCT * FROM $wpdb->posts AS p
  LEFT JOIN $wpdb->postmeta AS meta ON p.ID = meta.post_id
  LEFT JOIN $wpdb->term_relationships AS rel ON rel.object_id = p.ID
  LEFT JOIN $wpdb->term_taxonomy AS tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
  LEFT JOIN $wpdb->terms AS term ON tax.term_id = term.term_id
WHERE 1=1
    AND p.post_type = 'product' 
    AND p.post_status = 'publish' 
    AND p.post_title LIKE '%$trimmed%' OR (meta.meta_key = 'product_model' AND meta.meta_value LIKE '%$trimmed%') 
    AND (tax.taxonomy = 'product-brand' AND term.slug NOT IN ('$protected'))

Neither taxonomy or slug conditionals seem to be working in the above query.

Any help is appreciated!

Fructificative

Answer 1

Notes:

It looks like you're not using $wpdb->prepare(), so you risk SQL injections.

I also think you're missing parentheses around the relevant OR parts, so you don't end up displaying drafts, for example.

Alternative:

Instead of writing an hardcoded SQL query, we should be able to use the WP_Query class, with some modifications through hooks/filters.

Here's an example (PHP 5.4+):

$args = [ 
    '_meta_or_like_title' => $trimmed,        // Our new custom argument!
    'post_type'           => 'product',
    'post_status'         => 'publish',
    'meta_query'          => [
        [
            'key'     => 'product_model',
            'value'   => $trimmed,            // Your meta value
            'compare' => 'LIKE'
        ]
    ],
    'tax_query'    => [
        [
            'taxonomy'  => 'product-brand',
            'field'     => 'slug',
            'terms'     => $protected,        // Your terms array
            'operator'  => 'NOT IN'
        ]
    ]
];

where the custom _meta_or_like_title argument is supported by a slightly modified plugin I wrote for another question here.

Plugin:

<?php
/**
 *  Plugin Name:   Meta OR LIKE Title query in WP_Query
 *  Description:   Activated through the '_meta_or_like_title' argument of WP_Query 
 *  Plugin URI:    https://mcmap.net/q/880971/-how-to-exclude-results-from-custom-wordpress-mysql-query-by-taxonomy-term
 *  Plugin Author: Birgir Erlendsson (birgire)
 *  Version:       0.0.1
 */

add_action( 'pre_get_posts', function( $q )
{
    if( $title = $q->get( '_meta_or_like_title' ) )
    {
        add_filter( 'get_meta_sql', function( $sql ) use ( $title )
        {
            global $wpdb;

            // Only run once:
            static $nr = 0; 
            if( 0 != $nr++ ) return $sql;

            // Modify WHERE part:
            $sql['where'] = sprintf(
                " AND ( %s OR %s ) ",
                $wpdb->prepare( 
                    "{$wpdb->posts}.post_title LIKE '%%%s%%'", 
                     $wpdb->esc_like( $title ) 
                ),
                mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
            );
            return $sql;
        });
    }
}, PHP_INT_MAX );

Schmaltz

I wrote the same exact approach (which I abandoned halfway through) as I got stuck with the filtering where you search posts by title or custom field. I actually went the posts_* filter way. Like your custom approach better than mine :-) – Monniemono

we need somehow to get our hands around the whole meta query, so the posts_* filters are not as suitable as the get_meta_sql filter. But maybe there's a way with the posts_* filters, by adding one with priority 0 and another one with priority PHP_INT_MAX ? But then we still need to identify the meta query, so we would need something more I guess with that approach ;-) @PieterGoosen – Schmaltz

Might be possible yes, but it gets quite messy after a while. I've tried combining a few approaches which did not work out. As I said, your approach is definitely better, and much less of a hassle. The reuseability is great here. You have one simple function, one parameter extra which you reuse in hundreds of custom queries without having to copy and modify your custom filters – Monniemono

yes it sounds like a more difficult challenge going that road ;-) I used to extend the WP_Query class for extra stuff. But now I prefer to just add new functionality via custom parameters, that will not break the code if the plugin is deleted and by using the prefixes wpse_ or just _, in custom parameter names, we should be able to avoid name collision. But the great thing about this approach is that we can use it in the main queries as well via pre_get_posts ;-) @PieterGoosen – Schmaltz

I must say, you have me hooked on this approach ;-) – Monniemono