Support » Plugins and Hacks » Hacks » modify default query for custom_post in admin (list, columns))

  • Hi,

    I’m trieing to extend the deafault query with a left join on an additional table.

    function accommodation_join( $join ) {
    	global $wp_query, $wpdb;
    	if( $_GET['post_type']  == 'accommodation' ) {
        	$join .= " LEFT JOIN accommodation ON " . $wpdb->posts . ".ID = 'accommodation.__idPost' ";
    	return $join;
    add_filter('posts_join', 'accommodation_join' );

    This seems to work but this returns:
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN accommodation ON wp_posts.ID = 'accommodation.__idPost' WHERE 1=1 AND wp_posts.post_type = 'accommodation' 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') ORDER BY wp_posts.post_date DESC LIMIT 0, 20

    It askes for the fields in wp_posts only wp_posts.* but I need information from the accommodation table also.

    Is there any way to achieve this?

    I hope somebody can help!

Viewing 8 replies - 1 through 8 (of 8 total)
  • Add another filter onto posts_fields with similar conditional logic.

    NOTE: You should be able to determine post type by globalising $post_type inside both callbacks.


    global $wp_query, $wpdb, $post_type;
    if( 'accommodation' == $post_type )

    Or alternatively switching the filter to support a second argument, the hook will pass you the WP_query object, which will also contain the post type(unless it’s post, it’s usually empty for that type).

    Hey Mark,

    Thanks for the reply!
    I think my problem is not really clear because I use a table called ‘accommodation’ but also a custom_post type called ‘accommodation’.

    I’m creating a booking system which will have a back-office in Filemaker Pro.
    The website will have a WordPress CMS.

    For the back-office we created a few extra tables, one of them is called ‘accommodation’
    We also have a custom_post type called ‘accommodation’.
    Records in the table ‘accommodation’ should be related to wp_posts by ID.

    I’m trying to create a filter that will LEFT JOIN the ‘accommodation’ table with wp_posts ON ID.

    This works!

    My problem is that the default query returns only those fields in wp_posts because it says; ‘SELECT wp_posts.* ‘
    The content of fields in the ‘accommodation’ table do not appear in the $post array.

    My question is if this is possible.
    I hope you can help me man (-:
    Thanks in advance

    I already gave you the answer above, add the fields you need using the posts_fields hook… 😉

    Sorry, sorry, sorry! You gave me exactly the right hook that I needed. I just was to dumb to understand it immediately. After a little bit of experimenting I’ve got it up and running with the post_fields hook.

    Thank you super very much, I was looking for this for too long!

    I must do something wrong, I can’t get the hook working for the action=edit. It works fine now in the columns but in the edit screen of a post itself it does not return the data.

    Is there something that I’m forgetting? Hope you can help me once again, this time I will read better and write less (-:

    Thanks in advance

    Please explain what you mean, i’m not following.

    Super, I will.

    I have these two filters in my functions. (accommodation and contact are two custom tables):

    function posts_fields( $fields ) {
    	global $wp_query, $wpdb, $post_type;
    	if( $post_type == 'accommodation' ) {
    		$fields .= ',accommodation.*,contact.*';
        return $fields;
    add_filter('posts_fields', 'posts_fields' );
    function accommodation_join( $join ) {
    	global $wp_query, $wpdb, $post_type;
    	if( $post_type == 'accommodation' ) {
        	$join .= " LEFT JOIN accommodation ON " . $wpdb->posts . ".ID = accommodation.__idPost LEFT JOIN  contact ON accommodation.__idHost = contact.__id ";
    	return $join;
    add_filter('posts_join', 'accommodation_join' );

    Then I have some columns in the custom_post_type like this:

    function custom_columns($column) {
    	global $post;
    	if ("ID" == $column) echo $post->ID;
    	elseif ("type" == $column) echo __(SetTypeAccomodation($post->type));
    	elseif ("host" == $column) echo $post->nameFull;
    add_action("manage_posts_custom_column", "custom_columns");

    This works fine but in a metabox I can’t get the results like this:

    [Code moderated as per the Forum Rules. Please use the pastebin]

    Again the fields from the joined tables are not SELECTED in the query.
    Do I need an other, extra filter or should I query again in the havana_data_box function Or do I miss anything?

    THANKS for your support man (-:

    sorry, a little to much code, hope this works:

    I can’t get the results like this:

    function havana_data_box($fields) {
    	global $post;
    	echo ('	<table>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Code: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->code.'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Type: </strong><td style="padding-top:5px;padding-bottom:5px;">'.__(SetTypeAccomodation($post->type)).'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Lowest rate: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->lowestRate.'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Min. guests: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->minGuests.'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Max. guests: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->maxGuests.'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Smoking: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->smokingAllowed.'</td></tr>
    				<tr><td style="padding-top:5px;padding-bottom:5px;"><strong>Host: </strong><td style="padding-top:5px;padding-bottom:5px;">'.$post->nameFull.'</td></tr>

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘modify default query for custom_post in admin (list, columns))’ is closed to new replies.