WordPress query adds INNER/LEFT JOIN. Why or how to remove/prevent INNER/LEFT JOIN?

I am working on a filter for posts in wordpress wich uses data from postsmeta:
My date filter is working and gives me this query:

Working query result:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 AND YEAR(wp_posts.post_date)=2017 
AND MONTH(wp_posts.post_date)=03 AND wp_posts.post_type = 'projects' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 20

When I filter by postmeta.meta_value I get an extra INNER JOIN which
is causing issues:

Filter (not exactly working):

$query->set('meta_query', array(
   array(
      'key' => 'project_cust_id',
      'value' => $project_cust_id,
      'compare' => '=' 
   )
));

Query (not working result):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'project_cust_id' 
AND wp_postmeta.meta_value = '12345' ) ) AND wp_posts.post_type = 'projects' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 20

When I query this straight into my DB I get no results, but when I remove the extra line: INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) or when I remove the line: LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) I do get the results as expected.

My question is:

  • Why does my code adds another INNER JOIN?
  • How can I remove/prevent this extra INNER JOIN or LEFT JOIN?

Any help would be appreciated.

Read more here: WordPress query adds INNER/LEFT JOIN. Why or how to remove/prevent INNER/LEFT JOIN?

Leave a Reply

Your email address will not be published. Required fields are marked *