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 first select clause of the SQL fiddle here:
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?
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:
Result: