Set Alias for meta_query arguments in get_posts()

Have a look at the meta_query_find_compatible_table_alias filter defined in wp-includes/class-wp-meta-query.php. This filter’s documentation:

/**
 * Filters the table alias identified as compatible with the current clause.
 *
 * @since 4.1.0
 *
 * @param string|bool $alias        Table alias, or false if none was found.
 * @param array       $clause       First-order query clause.
 * @param array       $parent_query Parent of $clause.
 * @param object      $this         WP_Meta_Query object.
 */
return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this );

It’s likely that the calling function, find_compatible_table_alias, is returning false and thus the query creates the mt* aliases. Here is some sample code using this filter, although I would personally advocate for something that’s a little easier to understand. Modifying queries like this can lead to tons of headaches down the road and it may not be apparent at all where the query is getting messed up, especially if you bring in other developers in the future. That said…

// Reuse the same alias for the abc_type meta key.
function pets_modify_meta_query( $alias, $meta_query ) {
    if ( 'abc_type' === $meta_query['key'] ) {
        return 'mt1';
    }

    return $alias;
}

// Filter the query.
add_filter( 'meta_query_find_compatible_table_alias', 'pets_modify_meta_query', 10, 2 );

$args = array(
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'abc_type',
            'value' => array('puppy', 'kitten'),
            'compare' => 'IN',
        ),
        array(
            'relation' => 'OR',
            array(
                'relation' => 'AND',
                array(
                    'key' => 'abc_type',
                    'value' => 'puppy',
                    'compare' => '=',
                ),
                array(
                    'key' => 'abc_color',
                    'value' => 'pink',
                    'compare' => '=',
                ),
            ),
            array(
                'relation' => 'AND',
                array(
                    'key' => 'abc_type',
                    'value' => 'kitten',
                    'compare' => '=',
                ),
                array(
                    'key' => 'abc_size',
                    'value' => 'large',
                    'compare' => '=',
                ),
            ),
        ),
    )
);

$q = new WP_Query($args);
echo '<pre>', print_r($q->request, true); die;

This results in a query like

SELECT SQL_CALC_FOUND_ROWS
    wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE
    1=1
AND
(
    ( mt1.meta_key = 'abc_type' AND mt1.meta_value IN ('puppy','kitten') )
    AND
    (
        (
            ( mt1.meta_key = 'abc_type' AND mt1.meta_value="puppy" )
            AND
            ( wp_postmeta.meta_key = 'abc_color' AND wp_postmeta.meta_value="pink" )
        )
        OR
        (
            ( mt1.meta_key = 'abc_type' AND mt1.meta_value="kitten" )
            AND
            ( mt1.meta_key = 'abc_size' AND mt1.meta_value="large" )
        )
    )
)
AND
    wp_posts.post_type="post"
AND (
    wp_posts.post_status="publish"
    OR
    wp_posts.post_status="future"
    OR
    wp_posts.post_status="draft"
    OR wp_posts.post_status="pending"
)
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Leave a Comment