Whilst working on the deliciousbrains.com site we had the need to store some data about a custom post type. Because of the nature of the data it didn’t make sense to try and shoehorn it into the post meta table as a serialized array. Instead, creating a custom table to store it was the better approach.
We want our customers to be able to download the latest version of the plugin at the time their license expired (coming soon!). We use WooCommerce to sell our WordPress plugins, but that only stores the current product version in post meta.
To implement this functionality we need to store all of the versions for a plugin with the date they were released, and the zip filename. The custom table structure would look like this:
CREATE TABLE `wp_woocommerce_software_product_versions` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) NOT NULL,
`version` varchar(200) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`filename` varchar(200) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`date_released` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `product_version` (`product_id`,`version`(191))
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;After the initial seeding of the table with the historical versions and release dates, I need to give site admins a way to add a new version record at release time and set the date and zip filename. They might need to edit an incorrect date or even delete a version (in rare cases). This administration would need to happen on the edit product page in a similar place to the existing product custom meta box.
So I would need to register a new custom meta box, fetch the data from the table, render it in the meta box, add form controls to add new records and buttons to edit and delete. Most likely this would be hooked up with jQuery so it’s all AJAX’d and lovely.
Phew! That sounds a lot of work.
There must be a quicker, easier way? I’m already a massive fan of Advanced Custom Fields and we have recently started using it on the site. The Repeater field, which is a Pro only field, allows you to configure sub fields that are displayed in a tabular fashion with the ability to add/edit/delete rows. This is perfect for what I need, and I thought would be interesting to see how easily I could hack ACF to accomplish what I need.
Someone must have done this before? But after a quick google all I found was an ACF forum post asking about storing data in a custom table instead of post meta. Elliot Condon replied with some filters to make it work.
Turns out that was enough to get me started.
Configuring the Field
The repeater would need to have a text field to store the version number and filename, and a datetime field to select the release date. We don’t need to have a field for the plugin (the foreign key of product_id), we will know the post ID as we are on the edit-post.php page.
The location rule for the field group is set to display only on the post type of ‘product’. The edit product screen now looks like this:
Loading the Custom Table Data
At the moment the repeater is loading data from the post meta table, which has no data for the field. We want to tell ACF to load it from the custom table. Luckily ACF is so developer friendly there are actions and filters in almost every area of the codebase. We need to use acf/load_value to hijack the data returned from the post meta table and replace it with the rows fetched from our custom table:
<?php
add_filter( 'acf/load_value', 'my_load_product_versions', 11, 3 );
/**
* Get versions from the table and return to the repeater field.
*
* @param bool $value
* @param int $post_id
* @param array $field
*
* @return array|bool
*/
function my_load_product_versions( $value, $post_id, $field ) {
if ( 'product_versions' !== $field['name'] ) {
return $value;
}
$data = my_get_product_versions( $post_id, $field );
if ( empty( $data ) ) {
return false;
}
return $data;
}So here I’m checking the field is the ‘product_versions’ repeater and then using a helper method to grab the data from the table and rearrange it to the format the repeater field expects:
<?php
/**
* Get version records in an ACF repeater friendly format.
*
* @param int $post_id
* @param array $field
*
* @return array
*/
function my_get_product_versions( $post_id, $field ) {
$versions = WoocommerceSoftwareProductVersions::where( 'product_id', $post_id )
->orderBy( 'date_released', 'desc' )
->get();
$data = array();
foreach ( $versions as $version ) {
$data[] = array(
$field['sub_fields'][0]['key'] => $version->version,
$field['sub_fields'][1]['key'] => $version->filename,
$field['sub_fields'][2]['key'] => date( 'Ymd', strtotime( $version->date_released ) ),
);
}
return $data;
}I’m using the wp-eloquent package to make it super easy to query my custom table. This is looking more like it:
This is only half the story, let’s make sure any changes are saved to the table.
Saving Data to the Custom Table
The filter we need here is acf/update_value.
<?php
add_filter( 'acf/update_value', 'my_update_product_versions', 11, 4 );
/**
* Update the versions table with the field data on update.
*
* @param $value
* @param $post_id
* @param $field
* @param $_value
*
* @return null
*/
function my_update_product_versions( $value, $post_id, $field, $_value ) {
if ( 0 === strpos( $field['name'], 'product_versions_' ) ) {
return null;
}
if ( 'product_versions' !== $field['name'] ) {
return $value;
}
if ( empty( $_value ) ) {
WoocommerceSoftwareProductVersions::where( 'product_id', $post_id )->delete();
return null;
}
$data = $this->get_product_versions( $post_id, $field );
if ( serialize( $_value ) === serialize( $data ) ) {
// No changes
return null;
}
WoocommerceSoftwareProductVersions::where( 'product_id', $post_id )->delete();
$versions = array();
foreach ( $_value as $row ) {
$data = array(
'product_id' => $post_id,
'version' => $row[ $field['sub_fields'][0]['key'] ],
'filename' => $row[ $field['sub_fields'][1]['key'] ],
'date_released' => date( 'Y-m-d 00:00:00', strtotime( $row[ $field['sub_fields'][2]['key'] ] ) ),
);
$versions[] = $data;
WoocommerceSoftwareProductVersions::create( $data );
}
return null;
}Again I’m checking if the field is my repeater. This time I also need to make sure sub fields of the repeater return null so they don’t get saved to post meta.
If the value being saved is empty, that is, all the rows have been deleted or none have yet to be added, then I need to delete all records from my table for this product ID.
If there are records in the repeater then I compare them to the existing records in the table and bail if they are identical. This is for updates to the product but no changes to the repeater.
If there are differences then for simplicity I just delete the existing records and insert the repeaters rows after reformatting them. Belt and braces approach.
Downsides
This is certainly a quick approach to get custom table date displayed and editable, but it’s not a long term solution for every case.
For example, there are improvements I would want to make that just aren’t supported by the repeater field, such as adding new rows to the top of the field instead of the bottom. Further customisation of the field would probably take the same time as hand-coding the functionality to be exactly what we need from the start.
There is also the need for Advanced Custom Fields which, for solutions beyond quick-and-dirty MVPs, is an unnecessary added dependency.
If you’re looking for a way to store ACF data in custom tables instead of post meta, I recommend checking out the ACF Custom Database Tables plugin.
Wrapping Up
I found this an interesting exercise to achieve a working solution with an existing tool, without reinventing the wheel, whilst documenting the process for the next person that has the thought and turns to Google.
Have you ever customised Advanced Custom Fields in a similar way? What’s your go-to way of displaying data from custom tables? Let me know in the comments below.
