WP_Query get other table column values-CodePudding

I am trying to fetch another table column by WP_Query but it does not work.

Note: I can easily do this by $wpdb->get_results( 'custom_sql' ) but I want to do it by WP_Query

My code

function join_my_query( $join, $wp_query ) {
    global $wpdb;
    if ( $wp_query->get( 'custom_logic' ) === true ) {
        $join .= " LEFT JOIN $wpdb->postmeta as pm ON $wpdb->posts.ID = pm.post_id ";
    }

    return $join;
}

add_filter( 'posts_join', 'join_my_query', 10, 2 );
    

$query = new WP_Query(
    array(
        'custom_logic' => true,
        'post_type'    => 'post',
    )
);

It's giving me SQL like below, which does not select any column from the joined table!

Generated SQL

SELECT
    SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM
  wp_posts
  LEFT JOIN wp_postmeta as pm ON wp_posts.ID = pm.post_id
WHERE
  1 = 1
  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'
      )
    )
  )
ORDER BY
  wp_posts.post_date DESC
LIMIT
  0, 10

It does not select any column from my joined table. I have use var_dump($query->posts[0]) which return a WP_Post object and it has no column from my joined table. How I can get columns from my joined table with WP_Query like below

My Goal

$post = $query->posts[0];
$post->meta_key; // from my joined table, not work
$post->pm->meta_key; // pm is alias used in join, still not work

CodePudding user response：

According to your expected result, the posts_join filter is not required. You can do this with the posts_clauses filter easily!

add_filter( 'posts_clauses', 'modify_post_clauses_callback' , 10, 2 );

function modify_post_clauses_callback( $clauses, $wp_query ) {
    if ( $wp_query->get( 'custom_logic' ) === true ) {
        global $wpdb;
        $clauses['fields']  = $clauses['fields'] . ', pm.meta_key';
        $clauses['join']    = "LEFT JOIN {$wpdb->postmeta} as pm ON {$wpdb->posts}.ID = pm.post_id";
    }

    return $clauses;
}

$query = new WP_Query(
    array(
        'custom_logic' => true,
        'post_type'    => 'post',
    )
);

$post = $query->posts[0];
$post->meta_key;