MySQL JOIN or UNION with and based on previous resultset, WordPress pre_get_posts and post_meta

2 Views

Trying to recursively select database fields based on parent IDs of current selection in a WordPress environment.

On the WordPress side:

Read More

I’m doing a pre_get_posts filter to retrieve only the posts associated with a user meta. This was simple, until I realized that these posts could have parents and that would break the interface. Answers involving fixing the WP_List_Table sorting algorithm to allow children to be re-sorted hierarchically may be accepted. Without including parents, the table looks like this:

enter image description here

The meta query looks like this: 

// pre_get_posts
$meta_query = $query->get('meta_query');

$meta_query[] = array(
       'key'    => '_my_meta_key',
       'value'  => get_user_meta( $user_id, '_my_meta_key' ),
);

$query->set('meta_query', $meta_query );

On the MySQL side, adding the parents to this query would work just as well. As it stands, the query above works like the first select clause of the SQL fiddle here:

SQL Fiddle

A) In my limited SQL knowledge, I haven’t figured out how to use the first query’s id column as a parameter. Here, I’ve copy and pasted the entire thing to use as an IN parameter, which runs the query twice. Is there a way to alias the first query to use the id field in the second query?

B) How can I also include the parents of the parents (and so on) returned in the second SELECT statement?

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. I ended up manually adding the parents back into the result after wp_query is parsed. So far, I haven’t run into any issues. It’s not exactly what I wanted, as it produces extra (not too many in this case) and thought there might be a more efficient way, but it is what it is. I’ll leave this open a while in case anyone comes up with anything better.

    Note that since the user cannot edit these posts, they are not editable (awesome!)

    Example code:

    // users must see parent pages on the Edit screen 
// even when they can't edit them
// to preserve hierarchy
add_action( 'the_posts', 'add_post_parents_not_in_meta_query'), 10, 2 );

function add_post_parents_not_in_meta_query( $posts, $query ) {
    // only on pages with screen
    if ( ! function_exists( 'get_current_screen' ) )
        return $posts;

    $screen = get_current_screen();

    // only on edit screen
    if ( $screen->base !== 'edit')
        return $posts;

    // only during main query
    if ( ! $query->is_main_query() )
        return;

    // get an array of found IDs
    $post_ids = wp_list_pluck( $posts, 'ID' );

    foreach( $posts as $post ) {
        // check to see if exists, and if we already got this post
        while ( ! empty( $post->post_parent ) && ! in_array( $post->post_parent, $post_ids ) ) {
            $post = get_post( $post->post_parent );

            if ( ! empty( $post ) ) {
                $posts[] = $post;
                $post_ids[] = $post->ID;
            }
        }
    }

    return $posts;    
}

    Result:

    enter image description here