Correct way to filter get_pages() function

Question

We have a WordPress site into which we have "injected" (via wp_insert_post) ~200,000 pages.

Now, when opening the "Pages" section in the admin panel - /wp-admin/edit.php?post_type=page - MySQL is really struggling to process the queries WP is sending.

We've used...

add_filter( 'posts_where', ...)
add_action( 'pre_get_posts', ... );

...to filter some of the "big" queries, but there is one additional query being sent by get_pages() (in /wp-includes/post.php) that we cannot correctly add a filter...

SELECT * FROM skip_posts WHERE (post_type = 'page' AND post_status = 'publish') ORDER BY menu_order,skip_posts.post_title ASC

How can we cause additional WHERE clauses to be appended to this query, e.g. to filter OUT posts by a particular author?

We see there is a get_pages hook but no good examples that show how to affect the DB query sent by this function (note we cannot filter the pages AFTER they are returned to WP, that's too late).

Answer 1

If you inspect the code for get_pages() you'll see it calls $wpdb->get_results($query); which in turn calls $this->query( $query );

In there, there's a filter:

$query = apply_filters( 'query', $query );

where $query is the full SQL query string, right before it gets executed. Unfortunately, it seems to be the only filter that can do the job you need. Unfortunately because, since all queries pass through that filter, you need to compare with the exact query that you need to modify.

Having said that, perhaps you need to reconsider the choice of pages for storing what you stored? You can create a custom post type for your data, and moving them is just an UPDATE statement run once.