Support

Account

Home Forums General Issues Update_field for multiple Posts using one sql query

Solving

Update_field for multiple Posts using one sql query

  • I update around 300 posts a few times a week with a process similar to this:

    $schools = get_schools_object_from_csv($file_path);
    
    foreach ($schools as $id => $school_data) {
      $post_id = get_post_id_by_school_id($id);
      if (empty($post_id)) {
        $post_id = wp_insert_post( [...$school_data[stuffToCreatePost]] );
        update_field('programs', $data['programs'], $post_id);
      } else {
          update_field('programs', $data['programs'], $post_id);
      }
    }

    These schools are sometimes existing schools (a post already exists) where we need to update the school’s programs: an ACF repeater, or it’s a new school where I need to wp_insert_post and then I add the programs to that post.

    This process takes around 1 minute for around 300 schools. I’m wondering if it could be faster.

    I think I could improve the time significantly if I use php to iterate through the schools and create 1 SQL query, similar to what was described in this other topic where they recommend:

    INSERT INTO wp_postmea (post_id, meta_key, meta_value) VALUES ("1", "field_1", "value_1"),("1", "_field_1", "field_0123456789"),..... etc.

    update_field() allows me to dump a whole array/object of the “programs” repeater field into a:
    update_field('programs', $data['programs'], $post_id);

    I’m not sure how to handle this into a sql query.

    But my thought is that I could run 1 query to fetch ALL post IDs for every school (instead of a db query inside the foreach to get just 1 postID), and then save that PostID to the school array.

    Then do a foreach for $schools and build a sql query (psuedo code):

    $sql=[]
    foreach($schools as $id => $school) {
      $sql[] = "INSERT INTO wp_postmeta (post_id, meta_key, meta_value) 
      VALUES ($school[post_id], "meta_key for programs(?)", $school['programs'])
      ON DUPLICATE KEY UPDATE meta_value = $school['programs']"
    }
    
    $sql_query = implode(' ; ', $sql);
    
    $wpdb->query($sql_query)

    This would allow PHP to run quickly to build one large sql query, with just 1 wpdb query to insert OR update all schools/programs.

    At this point I’m just curious if I can update an entire repeater field w/ a sql query this way?

  • I should mention I cant simply delete all school posts because there’s a lot of other fields (other than the programs repeater) that need to be preserved

  • I forgot to mention that the update_field() is taking so long because the programs repeater field has about 60 sub-fields, including another repeater “locations” inside of it.

    Maybe updating 300 (schools) * 60 (total fields) will just take this long? Was hoping for something sub 10 seconds, since 18000 updates seems like it shouldn’t take 60 seconds

    I’ve also tried several “wp_importing” tricks, none of which have decreased the time to import

    ex: set_time_limit(0);
    ignore_user_abort();
    wp_defer_term_counting( true );
    wp_defer_comment_counting( true );
    $wpdb->query(‘SET autocommit = 0;’);
    if (!defined(‘WP_IMPORTING’)) {
    define(‘WP_IMPORTING’, true);
    }
    $wpdb->query(‘ALTER TABLE wp_postmeta DISABLE KEYS;’);

    with appropriate cleanup after

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.