0

I'm trying to pull out list of about 5000+ member records for CSV export. The query is timed out and I suppose the reason is that in order to populate the CSV file I need to iterate over those 5000 records and pull custom meta for each user separately.

Is it possible to extend or override either WP_User_Query or WP_User to return all custom meta in single query?

Basically what I need to do is to get rid of calling get_user_meta($user->ID) for each record individually and get all meta in one result set.

Thanks for help! :)

0

In cases like this its better off (IHMO) to simple use a custom query using the $wpdb class

ex:

<?php
global $wpdb;

//first get a list of all meta keys
$keys = $wpdb->get_col("SELECT distinct meta_key FROM $wpdb->usermeta");

//then prepare the meta keys query as fields which we'll join to the user table fields
$meta_columns = '';
foreach ($keys as $key) {
    $meta_columns .= " MAX(CASE WHEN um1.meta_key = '$key' THEN um1.meta_value ELSE NULL END) AS ".str_replace('-','_',$key).", \n";
}

//then write the main query with all of the regular fields and use a simple left join on user users.ID and usermeta.user_id
$query = "
SELECT  
    u.ID,
    u.user_login,
    u.user_pass,
    u.user_nicename,
    u.user_email,
    u.user_url,
    u.user_registered,
    u.user_activation_key,
    u.user_status,
    u.display_name,
    ".rtrim($meta_columns,", \n") ." 
FROM 
    $wpdb->users u
LEFT JOIN 
    $wpdb->usermeta um1 ON (um1.user_id = u.ID)
GROUP BY 
    u.ID";

$users  = $wpdb->get_results($query,ARRAY_A);
var_dump($users);

keep in mind that this is an example you hould build on like filtering unwanted users and meta keys.

  • Thanks for the answer. I was affraid that I won't be able to do it with the WP_User_Query:( See the problem is that I already have pretty robust filtering system that uses WP_User_Query along with pre_user_query hook, as the CSV should be generated based on dynamic criteria. But it seems that I'll have to redo the whole thing:) – wyktor Jul 1 '15 at 10:39
  • Not really, You can change your WP_User_Query to return just the user ID's and filter the above example based on these IDS using the WHERE clause. something like WHERE u.ID in (//ids go here comma seperated) – Bainternet Jul 1 '15 at 10:42
  • I see, so it would be two queries. 1st using WP_User_Query and then the 2nd query would be using WP_User_Query output as source for WPDB call. Right? I think that should do the trick:) Thanks for your help:) – wyktor Jul 1 '15 at 15:33
  • Yet that sounds about right but its ok since the first query is just getting the ids it should be a light query and if you think about it you are saving all of the get_user_meta Queries. – Bainternet Jul 2 '15 at 7:30

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.