Trying to recursively select database fields based on parent IDs of current selection in a WordPress environment.
On the WordPress side:
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:
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 SQL fiddle here:
As you can see by the commented out part, I want to merge the result set from the meta query with the parents of the results set based on p.post_parent and the parents of THOSE posts. I have no idea how to do this type of recursive query in MySQL. Ideas?