Is there a way to set an alias on the meta_query arguments when running a get_posts()? One of my queries is performing poorly. To optimize I just need to be able to reuse the same joined table instead of joining in 3 tables when only one is needed.
My current example…
$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' => '=',
),
),
),
)
);
get_posts($args);
which basically translates to this in straight SQL…
SELECT posts.* FROM posts
INNER JOIN postmeta ON ( posts.ID = postmeta.post_id )
INNER JOIN postmeta AS mt1 ON ( posts.ID = mt1.post_id )
INNER JOIN postmeta AS mt2 ON ( posts.ID = mt2.post_id )
INNER JOIN postmeta AS mt3 ON ( posts.ID = mt3.post_id )
WHERE 1=1
AND
(
( postmeta.meta_key = 'abc_type' AND postmeta.meta_value IN ('puppy','kitten') )
AND
(
(
( mt1.meta_key = 'abc_type' AND mt1.meta_value="puppy" )
AND
( mt2.meta_key = 'abc_color' AND mt2.meta_value > 'pink' )
)
OR
(
( mt3.meta_key = 'abc_type' AND mt3.meta_value="kitten" )
AND
( mt4.meta_key = 'abc_size' AND mt4.meta_value="large" )
)
)
) AND posts.post_type="abc_mypost" AND ((posts.post_status="publish"))
GROUP BY posts.ID ORDER BY posts.post_title ASC;
However, this is adding 2 extra joins for the custom meta field abc_type and as such performance has taken a big hit. Is there a way to be able to reference the same alias for multiple meta_query arguments? Basically, mt1 and mt3 are totally unnecessary, I should just be able to reference the first postmeta table that is used with the first ( postmeta.meta_key = 'abc_type' AND postmeta.meta_value IN ('puppy','kitten') ). Or at least if I can set a custom alias on each of these I could reference that.
A more optimal query would be…
SELECT posts.* FROM posts
INNER JOIN postmeta ON ( posts.ID = postmeta.post_id )
INNER JOIN postmeta AS mt1 ON ( posts.ID = mt1.post_id )
INNER JOIN postmeta AS mt2 ON ( posts.ID = mt2.post_id )
WHERE 1=1
AND
(
( postmeta.meta_key = 'abc_type' AND postmeta.meta_value IN ('puppy','kitten') )
AND
(
(
( postmeta.meta_key = 'abc_type' AND postmeta.meta_value="puppy" )
AND
( mt1.meta_key = 'abc_color' AND mt1.meta_value > 'pink' )
)
OR
(
( postmeta.meta_key = 'abc_type' AND postmeta.meta_value="kitten" )
AND
( mt2.meta_key = 'abc_color' AND mt2.meta_value="green" )
)
)
) AND posts.post_type="abc_mypost" AND ((posts.post_status="publish"))
GROUP BY posts.ID ORDER BY posts.post_title ASC;
Thoughts?
4 s
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
