I have a problem in a plugin of mine which uses post meta to store ownership of a post. Basically, my issue is that the WP_Meta_Query class generates one JOIN per meta_query query. I don’t understand why we cannot us the same JOIN for all queries.
Of course, when using more than 5 or 6 queries, the query takes way too much time to complete and plugin fails to get the posts.
E.g.: this is what I would do to get the posts that belong to either users 5, 6, 8 and 10 (In the real plugin, the compare is always LIKE and value looks like ‘%|usr_5|%’ to solve some cases for advanced ownership control):
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'owner',
'value' => 5,
'compare' => '='
),
array(
'key' => 'owner',
'value' => 6,
'compare' => '='
),
array(
'key' => 'owner',
'value' => 8,
'compare' => '='
),
array(
'key' => 'owner',
'value' => 10,
'compare' => '='
)
)
That above generates a SQL query that has 4 JOIN statements (mt1 to mt4) and in the WHERE clause, is using each JOIN for a comparison. Something like:
mt1.meta_key = 5 OR mt2.meta_key = 6 OR mt3.meta_key = 8 OR mt4.meta_key = 10
What is the point of that??! All of this could be done using a single JOIN and a WHERE clause like:
mt1.meta_key = 5 OR mt1.meta_key = 6 OR mt1.meta_key = 8 OR mt1.meta_key = 10
Is there something I am missing? Is that multiple JOIN there to take care of some comparison types?