Understanding ACF data storage

• 

  

  • iamrobertandrews

  • December 9, 2022 at 7:07 am

  I know you have dealt with a lot of “How does ACF store data?” questions over the years.

  I have been noting-down what I think I have learned, and I want to post it for sense-checking…

  ### Field Group – wp_posts (acf-field-group)

  Field Groups (for posts) are stored in the wp_posts as post_type acf-field-group.

  Eg.
  – post_title – Post – Article, Viewpoint & Report fields
  – post_name – post-article-viewpoint-report-fields
  – post_excerpt – source_url
  – post_content –
  `json
  a:13:{s:8:”location”;a:3:{i:0;a:1:{i:0;a:3:{s:5:”param”;s:9:”post_type”;s:8:”operator”;s:2:”==”;s:5:”value”;s:7:”article”;}}i:1;a:1:{i:0;a:3:{s:5:”param”;s:9:”post_type”;s:8:”operator”;s:2:”==”;s:5:”value”;s:9:”viewpoint”;}}i:2;a:1:{i:0;a:3:{s:5:”param”;s:9:”post_type”;s:8:”operator”;s:2:”==”;s:5:”value”;s:6:”report”;}}}s:8:”position”;s:15:”acf_after_title”;s:5:”style”;s:7:”default”;s:15:”label_placement”;s:3:”top”;s:21:”instruction_placement”;s:5:”label”;s:14:”hide_on_screen”;s:0:””;s:11:”description”;s:0:””;s:12:”show_in_rest”;i:0;s:18:”acfe_display_title”;s:0:””;s:13:”acfe_autosync”;s:0:””;s:9:”acfe_form”;i:0;s:9:”acfe_meta”;s:0:””;s:9:”acfe_note”;s:0:””;}
  `

  ### Post Field – wp_posts (acf-field)

  Individual fields (items of a Field Group) (for posts) are stored in the wp_posts as post_type acf-field.

  Eg.
  – post_title – Source URL
  – post_name – field_5b39e6dc170b7
  – post_excerpt – source_url
  – post_content –
  `json
  a:7:{s:4:”type”;s:3:”url”;s:12:”instructions”;s:0:””;s:8:”required”;i:0;s:17:”conditional_logic”;i:0;s:7:”wrapper”;a:3:{s:5:”width”;s:0:””;s:5:”class”;s:0:””;s:2:”id”;s:0:””;}s:13:”default_value”;s:0:””;s:11:”placeholder”;s:0:””;}
  `

  —

  ## Post meta data

  ### wp_postmeta

  The wp_postmeta table stores meta fields for WordPress posts.
  Each meta item has an entry, linked to the post ID.
  – meta_id
  – post_id
  – meta_key
  – meta_value

  #### 1. Actual data

  ACF stores the **data** as WordPress expects, eg:
  – meta_key: source_url
  – meta_value: [https://www.example.com/my/added/page.html](https://www.example.com/my/added/page.html)

  #### 2. ACF meta

  However, it also keeps a second meta item to link it to the ACF Field Group information.
  – meta_key: _source_url
  – meta_value: field_5b39e6dc170b7

  This is a signpost that allows ACF to link this field back to the Field data.
  – “field_5b39e6dc170b7” is the ACF name of the native field “source_url”
  – “field_5b39e6dc170b7” the post_name of the field stored in wp_posts, with post_title “Source URL”.

  #### ACF saves postmeta with null values

  Even for actual WordPress postmeta data, ACF appears to create wp_postmeta records for ACF fields with no meta_value. (2022-12-09: there were 24,013 such records with zero meta_value).

  ## Summary

  – Field Group information is stored as posts.
  – Field information is stored as posts.
  – ACF keeps 2x as much postmeta as necessary (2022-12-09: 237,853 entries in wp_postmeta)
  – It also keeps blank meta records.

  —

  ## Questions

  – Will deleting a field delete the corresponding wp_post item of type acf-field?
  – Will deleting a field group delete the corresponding wp_post item of type acf-field-group?

  – In a scenario in which ACF is no longer used, deleting all wp_postmeta with values field_* should help tidy things up, right?
  – If I want to delete all the blank postmeta records that have been saved, I should select them with SELECT * FROMwpcxt_postmetaWHEREmeta_keyIS NOT NULL ANDmeta_value= '' and delete, right?
• 

  

  • John Huebner

  • December 18, 2022 at 11:28 am

  – Will deleting a field delete the corresponding wp_post item of type acf-field?

  Yes

  – Will deleting a field group delete the corresponding wp_post item of type acf-field-group?

  Yes, an it will also delete all of the fields in that field group

  – In a scenario in which ACF is no longer used, deleting all wp_postmeta with values field_* should help tidy things up, right?

  This will only delete the field key references and not the field data

  – If I want to delete all the blank postmeta records that have been saved, I should select them with SELECT * FROMwpcxt_postmetaWHEREmeta_keyIS NOT NULL ANDmeta_value= ” and delete, right?

  Yes, this would delete all of the entries with empty values.
• 

  

  • iamrobertandrews

  • March 30, 2023 at 8:37 am

  @hube2 ,

  Ages ago, you posted this code…

  <?php 
    
    // this action is run by ACF whenever a field is deleted
    // and is called for every field in a field group when a field group is deleted
    add_action('acf/delete_field', 'delete_acf_content_on_delete_field');
  
    function delete_acf_content_on_delete_field($field) {
      // runs when acf deletes a field
      // find all occurences of the field key in all tables and delete them
      // and the custom field associated with them
      global $wpdb;
      // remove any tables from this array that you don't want to check
      $tables = array('options', 'postmeta', 'termmeta', 'usermeta', 'commentmeta');
      foreach ($tables as $table) {
        $key_field = 'meta_key';
        $value_field = 'meta_value';
        if ($table == 'options') {
          $key_field = 'option_name';
          $value_field = 'option_value';
        }
        $table = $wpdb->{$table};
        // this query gets all key fields matching the acf key reference field
        $query = 'SELECT DISTINCT('.$key_field.')
                  FROM '.$table.'
                  WHERE '.$value_field.' = "'.$field['key'].'"';
        $results = $wpdb->get_col($query);
        if (!count($results)) {
          // no content found in this table
          continue;
        }
        // loop through keys and construct list of meta_key/option_names to delete
        $keys = array();
        foreach ($results as $key) {
          $keys[] = $key; // delete acf field key reference
          $keys[] = substr($key, 1); // delete acf field value
        }
        // do escping of all values.... just in case
        $keys = $wpdb->_escape($keys);
        // delete all of the content
        $query = 'DELETE FROM '.$table.'
                  WHERE '.$key_field.' IN ("'.implode('","', $keys).'")';
        $wpdb->query($query);
      } // end foreach table
    }
  
  ?>

  For me, it doesn’t seem to fire when I delete a field (and re-save the group).

  Any reason why, in 2023, that wouldn’t work?

  Thanks.
• 

  

  • John Huebner

  • March 30, 2023 at 9:20 am

  No idea, but that is not a completely safe way to delete data. It would only be safe where there is a unique field name that is never repeated in any ACF field group.

  Not sure where you got that, but I don’t even remember writing it. I can’t even be sure I tested it when I wrote it. There could have been some change in ACF since then. A quick look at the ACF code shows the hook should still be firing but I did not do a thorough search of the code.

  I can tell you that I’ve written a lot of code here that I’ve never tested and this would likely fall into the category of things I’ve never used or tested because I don’t use unique field names and I don’t have not worried about stray data in the database for a long time.