WordPress request fiter order by related post's post_title

Question

WordPress request fiter order by related post's post_title

gwc

2017年11月14日 17:05

I have created two WordPresss custom post types (school, person). Each person is related to a school and the related school's post ID is stored in wp_postmeta under the person's post ID with meta_key = 'person-school-id' and meta_value = the related school's post ID.

I have added a person-type (Student, Teacher, etc. ) and the related School's name (by extracting the related school's post_title) to the Person WP edit.php list via WP manage_person_posts_columns and manage_person_posts_custom_column actions.

I have added filter capabilities for both person-type and person-school-id to the Person WP edit.php list via the WP restrict_manage_posts action.

And, I have added sort capabilities for the person-type to the Person WP edit.php list via the WP request filter using something like:

$vars = array_merge(
         $vars
        ,array(
             'meta_key' = 'person-type',
            ,'orderby' = 'meta_value'
        )
    );

So far, so good. All of this works as expected.

But, I also need to add sort capabilities for the related School's name (post_title). I could easily sort on the related Schools post ID (because that is what's stored in the person's wp_postmeta person-school-id meta_value field) by adding something like the following during the WP request filter:

$vars = array_merge(
         $vars
        ,array(
             'meta_key' = 'person-school-id',
            ,'orderby' = 'meta_value'
        )
    );

But I really need to order by the post_title (not post ID) of the post whose post ID is in the person's meta_value field for the person-school-id meta_key. Something like:

,'orderby' = 'select post_title from wp_posts where ID = ' . meta_value

Obviously, the above doesn't work.

All this to ask: How can I order by the post_title of a related post whose post ID is in a different post's wp_postmeta meta_value field while processing the WordPress request filter?

Topic request-filter post-meta wp-query custom-post-types Wordpress

Category Web

gwc · Accepted Answer · 2017年11月14日 17:05

After a short sleepless night with this running through my head, I have a solution that works. I'm not sure if it's the best solution but for those that may have a similar problem, here's what I did.

First, I abandoned trying to use the WordPress request filter for this particular ordering. Instead, I used the WP posts_clauses filter as follows:

add_filter( 'posts_clauses', 'my_posts_clauses_filter', 20, 1 );
function my_posts_clauses_filter( $pieces )
{
    global $wpdb;
    if ( isset( $_GET[ 'post_type' ] )
     and isset( $_GET[ 'orderby' ] )
     and 'person' == $_GET[ 'post_type' ]
     and 'person_school' == $_GET[ 'orderby' ]
     and ( 'ASC' == $_GET[ 'order' ] or 'DESC' == $_GET[ 'order' ] )
    )
    {
        $pieces[ 'join' ] .= " INNER JOIN {$wpdb->postmeta} my_postmeta1 ON ( {$wpdb->posts}.ID = my_postmeta1.post_id AND my_postmeta1.meta_key = 'person-school-id' )";
        $pieces[ 'join' ] .= " INNER JOIN {$wpdb->posts} my_posts1 ON ( my_posts1.ID = my_postmeta1.meta_value )";
        $pieces[ 'orderby' ] = 'my_posts1.post_title ' . $_GET[ 'order' ];
    }
    return $pieces;
}

That's it.

Once note of caution. This could have unintended consequences. I had another routine that used WP_Query to return a list of all schools for filtering. After adding the above posts_clauses filter, my get all schools routine was not returning anything when the $_GET entries were set. I got around this by surrounding the WP_Query request with a remove_filter and add_filter for the above posts_clauses filter.

WordPress request fiter order by related post's post_title

About