ACF Custom Fields Stored in Custom Table

Before I dig in, you should check out a fantastic plugin called ACF Custom Database Tables by Hookturn. This would a better solution then attempting to do it manually as shown here. My longterm goal was to completely move data from ACF completely, without custom post types, which is why I decided to go through this process manually rather then rely on another plugin.

Within CaptainCore, my WordPress management toolkit, I have a custom post type named captcore_website which stores site details via many ACF custom fields. The number of necessary custom fields seems to be never ending as I continue to expand functionality. Currently there are over 50 custom fields and my newest endeavor would require an additional 20 fields.

With the additional fields I would be able to add support for staging websites to my management toolkit. Currently that’s not possible but it’s definitely one of the things I want to tackle this year.

Options to break apart custom fields

With so many fields, it’s time to look at improving the architecture. One option would be to split up the custom post type into two or more post types and relate them to each other using a relationship field. I consider that approach, however it doesn’t solve the issue with so much unnecessary bloat added to the wp_postmeta table.

I stumbled upon a post by Iain Poulson about Managing WordPress Custom Tables with an Advanced Custom Fields Repeater Field. It inspired me to think of ACF and the data storage location as two separate things. I really like the idea of keeping all of the fields in one place for management purposes however break up where the data is stored for better performance and scalability.

Planning out the new custom table

Most of these custom fields deal with environment details as shown below. Each environment, whether production or staging, should contain the same fields. It makes sense that these existing ACF custom fields could be cleaned up if I were to create a new custom database table to hold the separate environments.

ACF fields used to store environment details for CaptainCore websites

Generating the new custom table follows the same format as explained in My First Custom Table with WordPress. This new table is a quite a bit larger. I’m sure I’ll need add or remove columns later on but for now this is good enough.

<?php
function captaincore_create_tables() {

	global $wpdb;

	$charset_collate = $wpdb->get_charset_collate();
	$version         = (int) get_site_option( 'captcorecore_db_version' );

	if ( $version < 3 ) {
		$sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_environments` (
			environment_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			site_id bigint(20) UNSIGNED NOT NULL,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			environment varchar(255),
			address varchar(255),
			username varchar(255),
			password varchar(255),
			protocol varchar(255),
			port varchar(255),
			home_directory varchar(255),
			database_username varchar(255),
			database_password varchar(255),
			use_offload boolean,
			offload_provider varchar(255),
			offload_access_key varchar(255),
			offload_secret_key varchar(255),
			offload_bucket varchar(255),
			offload_path varchar(255),
			storage varchar(20),
			views varchar(20),
			core varchar(10),
			subsite_count varchar(10),
			home_url varchar(255),
			themes longtext,
			plugins longtext,
			users longtext,
			updates_enabled boolean,
			exclude_themes longtext,
			exclude_plugins longtext,
		PRIMARY KEY  (environment_id)
		) $charset_collate;";

		$success = empty( $wpdb->last_error );

		update_site_option( 'captcorecore_db_version', 3 );
		return $success;
	}

}

I didn’t yet create an upgrade routine so running captaincore_create_tables() manually over wp shell generated the new custom table. I admit I had to drop the table and recreate quite a few times to make minor corrections.

Hooking into ACF for custom data storage

ACF comes with many hooks and filters for developers. I settled on the filter acf/update_value and the action acf/save_post. I use the filter to disregard saving certain fields to the database. This keeps wp_postmeta table nice and clean. The code looks something as shown here. In my actual code the $fields_to_disregard array has over 40+ fields.

add_filter( 'acf/update_value', 'captaincore_disregard_acf_fields', 10, 3 );

function captaincore_disregard_acf_fields( $value, $post_id, $field ) {

	$fields_to_disregard = array( 
		"field_9sf242cd2cc60",
		"field_9sf242c42cc61",
		"field_9sf242cb2cc62"
	);

	// Disregard updating certain fields as they've already been stored in a custom table.
	if ( in_array( $field['key'], $fields_to_disregard ) ) {
		return null;
	}

	return $value;

}

Next I use the action acf/save_post to intercept the data and store it in the custom table. This is pretty much a hack as I wanted a make a single call to the database rather than assign fields individual. The proper way would be to handle the fields individual using the filter acf/update_value.

add_action('acf/save_post', 'captaincore_acf_save_post', 1);

function captaincore_acf_save_post( $post_id ) {
    
	// bail early if no ACF data
	if( empty($_POST['acf']) ) {
		return;
	}
	
	// array of field values
	$fields = $_POST['acf'];

	// bail if environment field not found
	if ( ! isset( $fields['field_9sf242c518f1c'] ) ) {
		return;
	}

	$environment_production_id = get_field( 'environment_production_id', $post_id );
	$environment_staging_id = get_field( 'environment_staging_id', $post_id );

	$environment = array(
		'site_id'            => $post_id,
		'environment'        => "Production",
		'address'            => $fields['field_9sf242c518f1c'],
		'username'           => $fields['field_9sf242cc18f1d'],
		'password'           => $fields['field_9sf242c218f1e'],
		'protocol'           => $fields['field_9sf242c918f1f'],
		'port'               => $fields['field_9sf242cd18f20'],
		'home_directory'     => $fields['field_9sf242c538c32'],
		'users'              => $fields['field_9sf242cc85a77'],
		'themes'             => $fields['field_9sf242c804ed4'],
		'plugins'            => $fields['field_9sf242c004ed3'],
		'home_url'           => $fields['field_9sf242c8bf146'],
		'core'               => $fields['field_9sf242cc04ed5'],
	);

	$db_environments = new CaptainCore\environments();

	// Fetch existing environments.

	if ( $environment_production_id ) {
		// Updating production environment
		$environment['updated_at'] = date("Y-m-d H:i:s");
		$db_environments->update( $environment, array( "environment_id" => $environment_production_id ) );
	} else {
		// Creating production environment
		$time_now = date("Y-m-d H:i:s");
		$environment['created_at'] = $time_now;
		$environment['updated_at'] = $time_now;
		$environment_id = $db_environments->insert( $environment );
		update_field( 'environment_production_id', $environment_id, $post_id );
	}

	$environment = array(
		'site_id'            => $post_id,
		'environment'        => "Staging",
		'address'            => $fields['field_9sf242cd2cc60'],
		'username'           => $fields['field_9sf242c42cc61'],
		'password'           => $fields['field_9sf242cb2cc62'],
		'protocol'           => $fields['field_9sf242c12cc63'],
		'port'               => $fields['field_9sf242c72cc64'],
		'home_directory'     => $fields['field_9sf242c8fc2c9'],
		'users'              => $fields['field_9sf242c7ad20'],
		'themes'             => $fields['field_9sf242cc7ad1f'],
		'plugins'            => $fields['field_9sf242c7ad1e'],
		'home_url'           => $fields['field_9sf242cf7ad21'],
		'core'               => $fields['field_9sf242cb7ad1d'],
	);

	$db_environments = new CaptainCore\environments();

	// Fetch existing environments.

	if ( $environment_staging_id ) {
		// Updating staging environment
		$environment['updated_at'] = date("Y-m-d H:i:s");
		$db_environments->update( $environment, array( "environment_id" => $environment_staging_id ) );
	} else {
		// Creating staging environment
		$time_now = date("Y-m-d H:i:s");
		$environment['created_at'] = $time_now;
		$environment['updated_at'] = $time_now;
		$environment_id = $db_environments->insert( $environment );
		update_field( 'environment_staging_id', $environment_id, $post_id );
	}
	
}

Now when creating or updating a captcore_website post type, the related custom fields relating to environments will be managed within the custom table wp_captaincore_environments as shown here.

The only part missing is populating in the custom fields from the custom table. Otherwise the ACF fields will save but appear empty on the backend. For that I used the filter acf/load_value to fetch data from the custom table where necessary.

In order to fetch the correct info for the environment fields I did need to manually map out which ACF keys go with which environment and which database field name. Might look a bit messy but considering it’s only used within my WordPress backend, I consider it acceptable.

add_filter( 'acf/load_value', 'captaincore_load_environments', 11, 3 );

function captaincore_load_environments( $value, $post_id, $field ) {

	$fields_table_map = array(
    "field_9sf242c518f1c" => array( "environment" => "Production", "field" => 'address'           ),
    "field_9sf242cc18f1d" => array( "environment" => "Production", "field" => 'username'          ),
    "field_9sf242c218f1e" => array( "environment" => "Production", "field" => 'password'          ),
    "field_9sf242c918f1f" => array( "environment" => "Production", "field" => 'protocol'          ),
    "field_9sf242cd18f20" => array( "environment" => "Production", "field" => 'port'              ),
    "field_9sf242c538c32" => array( "environment" => "Production", "field" => 'home_directory'    ),
    "field_9sf242c6e9686" => array( "environment" => "Production", "field" => 'database_username' ),
    "field_9sf242cce9687" => array( "environment" => "Production", "field" => 'database_password' ),
    "field_9sf242cc85a77" => array( "environment" => "Production", "field" => 'users'             ),
    "field_9sf242c804ed4" => array( "environment" => "Production", "field" => 'themes'            ),
    "field_9sf242c004ed3" => array( "environment" => "Production", "field" => 'plugins'           ),
    "field_9sf242c8bf146" => array( "environment" => "Production", "field" => 'home_url'          ),
    "field_9sf242cc04ed5" => array( "environment" => "Production", "field" => 'core'              ),
    "field_9sf242cd2cc60" => array( "environment" => "Staging", "field" => 'address'           ),
    "field_9sf242c42cc61" => array( "environment" => "Staging", "field" => 'username'          ),
    "field_9sf242cb2cc62" => array( "environment" => "Staging", "field" => 'password'          ),
    "field_9sf242c12cc63" => array( "environment" => "Staging", "field" => 'protocol'          ),
    "field_9sf242c72cc64" => array( "environment" => "Staging", "field" => 'port'              ),
    "field_9sf242c8fc2c9" => array( "environment" => "Staging", "field" => 'home_directory'    ),
    "field_9sf242cc6c61a" => array( "environment" => "Staging", "field" => 'database_username' ),
    "field_9sf242ce6c61b" => array( "environment" => "Staging", "field" => 'database_password' ),
    "field_9sf242c67ad20" => array( "environment" => "Staging", "field" => 'users'             ),
    "field_9sf242cc7ad1f" => array( "environment" => "Staging", "field" => 'themes'            ),
    "field_9sf242c57ad1e" => array( "environment" => "Staging", "field" => 'plugins'           ),
    "field_9sf242cf7ad21" => array( "environment" => "Staging", "field" => 'home_url'          ),
    "field_9sf242cb7ad1d" => array( "environment" => "Staging", "field" => 'core'              )
	);

	// Fetch certain records from custom table
	if ( in_array( $field['key'], array_keys( $fields_table_map ) ) ) {

		$db_environments = new CaptainCore\environments();

		$item = $fields_table_map[ $field['key'] ];
		$data =	$db_environments->fetch_field( $post_id, $item["environment"], $item['field'] );
		if ( $data && $data[0]) {
			return $data[0]->{$item['field']};
		}
	}

	return $value;

}

In order for that to work I did need to also adapt my database class with a new fetch_field function.

namespace CaptainCore;

class DB {

	static function fetch_field( $value, $environment, $field ) {
		global $wpdb;
		$value = intval( $value );
		$sql   = "SELECT $field FROM " . self::_table() . " WHERE `site_id` = '$value' and `environment` = '$environment' order by `created_at` DESC";
		return $wpdb->get_results( $sql );
	}

}

Move existing data to custom table and cleanup.

Populating the newly created table requires a good amount of resources. In order to make sure it ran successfully I wrote the instructions in a PHP file which was executed over command line line so: wp eval-file populate-environment-table.php --user=adminuser. The initial time it generates the new records in the custom table. If it doesn’t make it all the way through then running it again will update the existing records.

<?php

$sites = get_posts( array(
    'order'          => 'asc',
    'orderby'        => 'title',
    'posts_per_page' => '-1',
    'post_type'      => 'captcore_website',
    'meta_query'     => array(
        'relation' => 'AND',
        array(
            'key'     => 'status',
            'value'   => 'closed',
            'compare' => '!=',
        ),
) ) );

foreach( $sites as $site ) {

    $post_id = $site->ID;

    echo "Processing site $post_id " . get_the_title($post_id) . "\n";

    $environment_production_id = get_field( 'environment_production_id', $post_id );
    $environment_staging_id = get_field( 'environment_staging_id', $post_id );

    $environment = array(
        'site_id'            => $post_id,
        'environment'        => "Production",
        'address'            => get_field('field_9sf242c518f1c', $post_id ),
        'username'           => get_field('field_9sf242cc18f1d', $post_id ),
        'password'           => get_field('field_9sf242c218f1e', $post_id ),
        'protocol'           => get_field('field_9sf242c918f1f', $post_id ),
        'port'               => get_field('field_9sf242cd18f20', $post_id ),
        'home_directory'     => get_field('field_9sf242c538c32', $post_id ),
        'database_username'  => get_field('field_9sf242c6e9686', $post_id ),
        'database_password'  => get_field('field_9sf242cce9687', $post_id ),
        'updates_enabled'    => get_field('field_9sf242c585a78', $post_id ),
        'exclude_themes'     => get_field('field_9sf242c6b9731', $post_id ),
        'exclude_plugins'    => get_field('field_9sf242c0b9732', $post_id ),
        'users'              => get_field('field_9sf242cc85a77', $post_id ),
        'themes'             => get_field('field_9sf242c804ed4', $post_id ),
        'plugins'            => get_field('field_9sf242c004ed3', $post_id ),
        'home_url'           => get_field('field_9sf242c8bf146', $post_id ),
        'core'               => get_field('field_9sf242cc04ed5', $post_id )
    );

    $db_environments = new CaptainCore\environments();

    // Fetch existing environments.

    if ( $environment_production_id ) {
        // Updating production environment
        echo "Updating production environment\n";
        $environment['updated_at'] = date("Y-m-d H:i:s");
        $db_environments->update( $environment, array( "environment_id" => $environment_production_id ) );
    } else {
        // Creating production environment
        echo "Creating production environment\n";
        $time_now = date("Y-m-d H:i:s");
        $environment['created_at'] = $time_now;
        $environment['updated_at'] = $time_now;
        $environment_id = $db_environments->insert( $environment );
        update_field( 'environment_production_id', $environment_id, $post_id );
    }

    $environment = array(
        'site_id'            => $post_id,
        'environment'        => "Staging",
        'address'            => get_field('field_9sf242cd2cc60', $post_id ),
        'username'           => get_field('field_9sf242c42cc61', $post_id ),
        'password'           => get_field('field_9sf242cb2cc62', $post_id ),
        'protocol'           => get_field('field_9sf242c12cc63', $post_id ),
        'port'               => get_field('field_9sf242c72cc64', $post_id ),
        'home_directory'     => get_field('field_9sf242c8fc2c9', $post_id ),
        'database_username'  => get_field('field_9sf242cc6c61a', $post_id ),
        'database_password'  => get_field('field_9sf242ce6c61b', $post_id ),
        'updates_enabled'    => get_field('field_9sf242c87ad1a', $post_id ),
        'exclude_themes'     => get_field('field_9sf242c37ad1b', $post_id ),
        'exclude_plugins'    => get_field('field_9sf242c37ad1c', $post_id ),
        'users'              => get_field('field_9sf242c67ad20', $post_id ),
        'themes'             => get_field('field_9sf242cc7ad1f', $post_id ),
        'plugins'            => get_field('field_9sf242c57ad1e', $post_id ),
        'home_url'           => get_field('field_9sf242cf7ad21', $post_id ),
        'core'               => get_field('field_9sf242cb7ad1d', $post_id )
    );

    $db_environments = new CaptainCore\environments();

    // Fetch existing environments.

    if ( $environment_staging_id ) {
        // Updating staging environment
        echo "Updating staging environment\n";
        $environment['updated_at'] = date("Y-m-d H:i:s");
        $db_environments->update( $environment, array( "environment_id" => $environment_staging_id ) );
    } else {
        // Creating staging environment
        echo "Creating staging environment\n";
        $time_now = date("Y-m-d H:i:s");
        $environment['created_at'] = $time_now;
        $environment['updated_at'] = $time_now;
        $environment_id = $db_environments->insert( $environment );
        update_field( 'environment_staging_id', $environment_id, $post_id );
    }
}

After all of the data has been confirmed under the new table, the last step is to manually purge all of the post meta records. This again I handled through a WP-CLI custom php script. Create a file named purge-postmeta.php filled with all of the ACF custom fields which are no longer needed and run the following WP-CLI command wp eval-file purge-postmeta.php --user=adminuser.

$sites = get_posts( array(
    'order'          => 'asc',
    'orderby'        => 'title',
    'posts_per_page' => '-1',
    'post_type'      => 'captcore_website',
    'meta_query'     => array(
        'relation' => 'AND',
        array(
            'key'     => 'status',
            'value'   => 'closed',
            'compare' => '!=',
        ),
) ) );

foreach( $sites as $site ) {

    $post_id = $site->ID;

    // Purge old postmeta records
    delete_field('field_9sf242cd2cc60', $post_id );
    delete_field('field_9sf242c42cc61', $post_id );
    delete_field('field_9sf242cb2cc62', $post_id );

}

Comparisons

So was moving a significant number of custom fields to a custom table worth it? According to some crude performance testing, YES it was totally worth it. The largest query, which builds up an array of all websites, was taking 4.344 seconds. After the data was moved to a custom table this same query was reduced to 2.541 seconds. I would call that a success.