Joining tables not working in the post editor page

Question

I am developing a plug-in which stores data in a custom table and is joined onto the wp_posts by the post's the id. I then use the posts_join and post_fields filters to join my table and be able retrieve data from it. For example:

    add_filter('posts_join', 'my_join' );
    function my_join( $join ){
        global $wpdb; 
        $join .= "LEFT JOIN wp_my_table ON $wpdb->posts.ID = wp_my_table.postID "; 

        return $join;
    }

This works well on the front end (and even most of the admin), but when a post is being edited, the join doesn't seem to happen. For instance, in my plugin's custom metabox on the edit page, var_dump($post) reveals all the standard fields, but none of my plugin's.

When a post is being edited, does it skip the posts_join, posts_fields filters? Is there another hook that can be used?

---

Update

I have done some further tests. Let's suppose I am on; ../post.php?post=703&action=edit. As in my comment to Chris' response, the the_post hook reveals that the table has been successfully joined at that point (and the post is 703).

On the other hand there are two calls to posts_selection and both are for revision posts that are the children of 703. (I have attempted joining to their parent, but this does not work - even after ensuring filters are not suppressed).

I can, of course, just retrieve my data inside the metabox - but I would like to know if it would be possible to avoid having to do this. (And quite what goes on when click to edit a post :D!)

Answer 1

The easy way in the admin is to do a 2nd custom query using the post_id from the post object.

function custom_posts_data( $posts, $query ) {
       global $wpdb;  
       if ( !count( $posts ) ) 
           return $posts;  

       while ( $posts as $post ) {        
           $query = "SELECT * FROM {$wpdb->prefix}my_plugin_table WHERE post_id={$post->ID}";
           $results = $wpdb->get_results( $query );
       }
       return $posts;
   }
   add_filter( 'the_posts', 'custom_posts_data' );

If you need to do the full joins have a look at scibu's sortable columns example or Mitcho's orderby joins filter examples.

Answer 2

Well, I have found my answer. Digging through posts.php in admin, in line 182, just before WordPress includes ./edit-form-advanced.php, we have;

$post = get_post_to_edit($post_id);

(see, here). If we look up get_post_to_edit, (it's buried deep, see here, line 475), we find that this uses get_post() to retrieve the post. get_post() retrieves posts by id and does not offer any way to alter the query (and in particular join other tables.). get_post lives here by the way, line 370).

This is the root of the problem. And as it stands, you can't return all the custom data with the post - you'll have to do it by using the id of the post and query the database again (which I know is no big deal..).