CODEWITHSUNDEEP
phpmysqlwordpressjoinfilter
K. Tromp
K. Tromp

Reputation: 360

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 which 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 and error below, 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.

EDIT: MySQL returns error: Not unique value table/alias: 'wp_postmeta'

My question is:

Any help would be appreciated.

Upvotes: 2

Views: 1584

Answers (3)

edid
edid

Reputation: 359

I found no solution with WP_Query args but one using filter "comments_clauses".

public static function get_comments($query){
    add_filter( 'comments_clauses', array(__CLASS__, 'on_get_comments_clauses_cb' ), 10, 2);
    $the_query = new WP_Comment_Query( $query );
    remove_filter( 'comments_clauses', array(__CLASS__, 'on_get_comments_clauses_cb' ), 10);
        
    return $the_query->comments; 
}
public static function on_get_comments_clauses_cb($clauses, $query){
    global $wpdb;
    $meta_alias = 'commmeta_status';
    $clauses['join'] .= " LEFT JOIN {$wpdb->commentmeta} {$meta_alias}"
        . " ON ( {$wpdb->comments}.comment_ID = {$meta_alias}.comment_id AND {$meta_alias}.meta_key = 'status' )";
    $clauses['where'] .= " AND ({$meta_alias}.comment_id IS NULL OR {$meta_alias}.meta_value != 'ended')";
    return $clauses;
}

Upvotes: 0

K. Tromp
K. Tromp

Reputation: 360

Thanks everyone for the input, but it turned out that there was nothing wrong with my coding, apparently there was some other plugin wich sended a GET parameter when I sended the filter parameters, this was returning FALSE when this parameter was set, I fixed it by adding !EMPTY in that plugin, after that it worked like a charm.

Upvotes: 0

Tom
Tom

Reputation: 6663

Since you are filtering for values in the wp_postmeta table in your where clause, the LEFT JOIN is going to act like an INNER JOIN, since it will only return values that have the matching criteria in the wp_postmeta table. So having a LEFT JOIN or INNER JOIN or both should not make a difference. If you want the join to act like a LEFT JOIN, any criteria for the wp_postmeta table should be added to the JOIN and not the WHERE clause.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 

FROM   wp_posts 

       LEFT JOIN wp_postmeta 
       ON wp_posts.ID = wp_postmeta.post_id
       AND wp_postmeta.meta_key = 'project_cust_id' 
       AND wp_postmeta.meta_value = '12345'

WHERE  1=1 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

Upvotes: 1

Related Questions