0

This question applies to scale and does not concern typical blog implementations. I'm working on a web app where I'm running standard WP_Query to fetch some custom post types. This usually results in running of about 10 SQL queries. Within the loop I need to access both post meta for the post and user meta for the author of the post. Accessing post meta is trivial with use of get_post_meta inside of the loop. WordPress caches results of WP_Query and hence "does not make" additional queries each time get_post_meta called within the loop.

However, things get ugly when retrieving user data for the post author. Typically I would need to call get_user_by within the loop to fetch user data. Here comes the problem. If I use WP_Query to fetch 50 posts each by a different author, calling get_user_by within the loop results in 100 SQL queries (two queries for each user for fetching data from users table and usermeta table). This is in addition to the 10 SQL queries I mentioned earlier. 110 SQL queries! — this is insane.

Next, I thought of using WP_User_Query to fetch user data before the loop runs.

    $custom_query = new WP_Query($args);
    foreach($custom_query->posts as $post) {
        $post_authors[] = (int)$post->post_author;
    }
    $user_query = new WP_User_Query( array('include' => $post_authors) );

I expected WP_User_Query to be smart like WP_Query and reduce the number of queries. This did reduce queries to 51 as opposed to 100 earlier. One query hit the users table for all users and 50 queries hit the usermeta table one for each user. Better, but not what I expected.

How do I reduce the queries for my scenario?

  • If I'm going into wrong direction correct me. You could query all posts by certain authors, and separately, without any loops, query all authors' meta, then just merge the arrays together? Alternatively you could build your own SQL syntax to retrieve the data with INNER JOIN, which would make it even faster, yet uglier solution. – Maciej Gurban May 15 '13 at 14:55
  • @DiH Well, you are on track. I did try my hand at building a custom SQL query to using INNER JOIN on users table and usermeta table and fetch results for all users at once. But somehow this only returned one row (last inserted) from usersmeta table for each user. – John May 15 '13 at 15:47
  • @DiH This is what I had tried: $users = $wpdb->get_results("SELECT * FROM $wpdb->users INNER JOIN $wpdb->usermeta ON $wpdb->users.ID = $wpdb->usermeta.user_id WHERE ID IN (" . implode(',', $post_authors) . ")"); – John May 15 '13 at 15:49
2

After a lot of trial and error I learnt that WP_User_Query takes an argument fields which can be set to 'all_with_meta'. This reduces number of queries to two for any number of users that are fetched in a single go and also provides benefits of caching. Usage would look like:

$user_query = new WP_User_Query( array('include' => $post_authors, 'fields' => 'all_with_meta') );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.