Steve Taylor photo

Custom meta tables in WordPress

A large WordPress project I recently worked on had requirements for post and user metadata that resulted in me creating and working with custom database tables. The ins and outs of that particular project are very complex—in the end I’m not sure the custom tables were 100% necessary. However, there are certainly cases where custom metadata tables will be the best approach.

For instance, if you need to get a lot of posts with all their meta values in one go, WP’s data schema may be unworkable. The default wp_postmeta table is structured for maximum flexibility in terms of adding new fields; there isn’t a column for each field, rather the structure allows for fields to be added on an ad hoc basis via the meta_key column. This is great for being flexible about adding new fields, but getting a post or a number of posts together with all metadata will involve a table join for each field. The more joins, the more impact on the query’s performance. The same applies for the similarly-structured wp_usermeta table.

For what it’s worth to anyone attacking this kind of issue, I want to document here how to integrate custom metadata tables with WP’s core metadata handling.

wp-database-extension

Before getting going, I should mention a project designed to address this issue more comprehensively. At a recent wp-hooked meeting in London, a bunch of us discussed this issue. As a result of this, Ken Snyder established a Bitbucket project called wp-database-extension. Check this out, it may suit your needs. If it doesn’t quite fit, you could contribute to the project. If you’re intent on rolling your own, the following might help get you going!

Warning!

The code here is based on code very specific to the project it was developed for. I’ve adapted it to be more generically useful, but not properly tested. Be assured, the basics work fine, but minor issues may have crept in adapting the code for this post. Test thoroughly (of course), and let me know if you find such a mistake here.

The table(s)

Let’s say you have a custom post type, film. You have the following custom fields for films:

  • director
  • writer
  • year

Some aspect of your site means you think custom meta tables are the way forward, and you want to handle these fields through a custom table. The easiest way is to use phpMyAdmin to create the new table with the following specs:

Field		Type		Length
post_id		BIG_INT		20
director	VARCHAR		255
writer		VARCHAR		255
year		YEAR		4

Call the table something like wp_postmeta_film (though remember to replace wp_ with the right prefix if necessary!).

Set post_id as a unique primary key. This kind of table is known as an “extension table” or “subtable”. Every record in it will correspond, via post_id, to a single record in wp_posts (although not every record in wp_posts will have a record in this table).

This isn’t a very slick way of dealing with the actual table, and of course you’ll probably have a bit of downtime if you decide at a later date to add new fields (at which point you’ll appreciate why WP was designed with its current metadata structure!). I looked into managing the table using the dbDelta function, but I had some problems and for this project just did things manually. If you need something more robust, check out what wp-database-extension are doing, or read this Codex article.

Managing records in the custom table

You’ll need to hook into WP to make sure records in the custom table are kept in synch with your posts. The following code should tackle new posts being created:

// Create meta records for each new post with meta in a custom table
// Need to check for post status because wp_trash_post calls wp_insert_post to create trashed item
add_action( 'wp_insert_post', 'my_custom_meta_insert_post', 10, 2 );
function my_custom_meta_insert_post( $post_id, $post ) {
	global $wpdb;
	if (	get_post_type( $post ) == 'film' &&
		$post->post_status != 'trash' &&
		$wpdb->get_var( $wpdb->prepare("
			SELECT	post_id
			FROM	" . $wpdb->prefix . "postmeta_film
			WHERE	post_id = %d
		", $post_id ) ) == null
	) {
		$wpdb->insert( $wpdb->prefix . "postmeta_film", array( 'post_id' => $post_id ) );
	}
}

So, whenever a new post is created, this will be triggered. It’ll check that the new post is the right type (“film”—change as necessary), that we’re not dealing with a trashed post, and that there isn’t already a record in the subtable for this post. If all checks out, the new record is created with the post’s ID, ready for metadata.

A similar bit of code handles posts being deleted:

// Delete custom meta records when a post is deleted
add_action( 'delete_post', 'my_custom_meta_delete_post' );
function my_custom_meta_delete_post( $post_id ) {
	if ( get_post_type( $post_id ) == 'film' ) {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( "
			DELETE FROM	" . $wpdb->prefix . "postmeta_film
			WHERE		post_id	= %d
		", $post_id ) );
	}
}

Re-routing core metadata handling

For a while now, many WP core functions have included code that allows filters to not only modify the output value, but to totally re-route the handling of calls to that function. For instance, the core function add_metadata includes the following lines pretty early on:

$check = apply_filters( "add_{$meta_type}_metadata", null, $object_id, $meta_key, $meta_value, $unique );
if ( null !== $check )
	return $check;

So if a filter here returns anything other than the null passed to it, the function returns whatever the filter returned without running any of the function’s main code.

Here’s how we utilize this provision to hijack core metadata handling to make sure our custom post type’s metadata goes to the new table:

// Re-route saving custom post meta for posts
add_filter( 'update_post_metadata', 'my_custom_meta_update', 0, 4 );
add_filter( 'add_post_metadata', 'my_custom_meta_update', 0, 4 );
function my_custom_meta_update( $check, $post_id, $meta_key, $meta_value ) {
	if ( get_post_type( $post_id) == 'film' && in_array( $meta_key, array( 'director', 'writer', 'year' ) ) ) {
		global $wpdb;
		return $wpdb->update(
			$wpdb->prefix . "postmeta_film",
			array( $meta_key => maybe_serialize( $meta_value ) ),
			array( 'post_id' => $post_id)
		);
	} else {
		return $check;
	}
}

Note that this includes a check on the $meta_key, making it possible to have some custom fields still managed via the core’s post meta table.

Let’s now deal with deleting and getting metadata:

// Re-route deleting custom post meta for posts
add_filter( 'delete_post_metadata', 'my_custom_meta_delete', 0, 3 );
function my_custom_meta_delete( $check, $object_id, $meta_key ) {
	if ( get_post_type( $post ) == 'film' && in_array( $meta_key, array( 'director', 'writer', 'year' ) ) ) {
		global $wpdb;
		return $wpdb->update(
			$wpdb->prefix . "postmeta_film",
			array( $meta_key => null ),
			array( 'post_id' => $object_id )
		);
	} else {
		return $check;
	}
}

// Re-route getting custom meta for posts
add_filter( 'get_post_metadata', 'my_custom_meta_get', 0, 3 );
function my_custom_meta_get( $check, $post_id, $meta_key ) {
	if ( get_post_type( $post_id) == 'film' && in_array( $meta_key, array( 'director', 'writer', 'year' ) ) ) {
		$result = $wpdb->get_var( $wpdb->prepare("
			SELECT	$meta_key
			FROM	" . $wpdb->prefix . "postmeta_film
			WHERE	post_id = %d
		", $post_id) );
		return maybe_unserialize( $result );
	} else {
		return $check;
	}
}

Obviously there’s much scope here for making the code more extensible, but that’s the basics.

Getting all meta

The above code will mean that when you use get_post_meta (getting a single meta value), the data will be automatically fetched from the custom table. Of course, the whole point of using custom tables like this is to optimize queries that get posts along with all meta. This will require a custom query, or perhaps hooking into the core’s querying code (I opted for the formed in my project due to the complexity of the data). The real purpose of the above code is to re-route the administration of these custom fields, plus re-routing any instances where you do happen to use get_post_meta.

It should be noted that it’s not currently possible to re-route calls to get_post_custom (which gets all metadata for a given post). If you browse the WP source, you’ll find that this function relies on update_meta_cache, which has no mechanism for re-routing. If you need this kind of functionality, you’ll have to write your own function and avoid get_post_custom.

21 comments

  1. Gonzoarte avatar Gonzoarte

    Hi Steve,

    Thanks a lot for the interesting post. I am testing your solution and it is working great (except that the delete hook is not working for now). I am planning to use it on a live site. However, I would like to know if you have had any issues with your approach.

    Thanks in advance.

  2. The site on which this method has been used has been live for nearly two years. There have been a couple of minor issues in that time, but I don’t think they were to do with these techniques. At least, they caused no damage, and were hard to pin down, so it’s hard to be sure. Obviously proceed with caution and do your own testing thoroughly because YMMV. But generally, these techniques seem to work well.

  3. Vasik avatar Vasik

    Thanks for great artricle. Will this work for user metadata also? Or, is there any better way to have custom table for user meta? Thanks

  4. I’m sure there would be equivalent filters that would let you do this for user meta, too. I didn’t need to do that but yeah, can’t think why it wouldn’t work in a similar way.

  5. Awesome post! This is exactly what I was looking for. I am using your method with a little twist. I am using another table to store my info but also using a join table to keep my original table as it was. I have my custom fields on the custom post page filling in with the correct values from the db but the only issue is they are only giving me the first letter for each value. Do you have any advice on why this might be happening?

    Here is what I am using to get that result:

    add_action( ‘get_post_metadata’, ‘my_custom_meta_get’, 0, 3 );
    function my_custom_meta_get( $check, $post_id, $meta_key ) {
    // var_dump($meta_key);
    if ( get_post_type( $post_id) == ‘schools’ && in_array( $meta_key, array( ‘wp_schoolInfo.city’, ‘wp_schoolInfo.state’, ‘address’, ‘zip’, ‘phone’ ) ) ) {
    global $wpdb;
    $result = $wpdb->get_var( $wpdb->prepare(”
    SELECT $meta_key
    FROM wp_schoolInfo
    inner join USZipCodes on USZipCodes.ZipID = wp_schoolInfo.ZipID
    inner join wp_post_to_schoolinfo on wp_post_to_schoolinfo.SchoolInfoKey = wp_schoolInfo.SchoolInfoKey
    inner join wp_posts on wp_posts.ID = wp_post_to_schoolinfo.post_id
    WHERE post_id = %d
    “, $post_id) );
    return maybe_unserialize( $result );
    } else {
    return null;
    }
    }

  6. Steve Taylor avatar Steve Taylor

    Sorry, can’t see anything obvious from your code. You’ll just have to do the usual – breaking at every step to output variables and see what’s going on. Let me know if you find the exact point in the code where something unexpected’s happening and you still can’t see the issue.

  7. Thanks for the response. I have gone through everything and can’t seem to find the problem. I am able to echo out the proper values but when returning them it still only spits out the first letter.

    I have also tried a foreach to try and fix the issue but had no luck.

    $array = (array) $result;

    foreach ($array as $key => $value) {
    $mykey = $value;
    echo($mykey);
    }

    return ($mykey);

    This spits out the right value next to each field but the return is still just the first letter.

  8. Steve Taylor avatar Steve Taylor

    So what is in $result before you return it? If there’s a difference between the data just before you return from the hooked function, and the data coming from the output function (get_post_meta?), there must be something else hooking into get_post_metadata and messing with the data.

  9. When we echo $result, we get the full term. So city is “Houston”. When we return ($result), we get just “H” displayed in the text field.

  10. Steve Taylor avatar Steve Taylor

    First, not sure why the brackets around $result? Probably not making any difference though. So, my guess is you need to search for other things hooking into get_post_metadata. Can’t recall the last time I did that or what I used – Google is your friend :-)

  11. After a long hunt and no fixes. I finally tried a new method of creating the custom fields. Originally I was using Advanced Custom Fields. After testing a few fields using the POD plugin I am now getting the full result. PODS doesn’t quite have all the features I want but at least let me know where the issue was.

  12. Vasik avatar Vasik

    Are there any news about the wp-database-extension project? it would be quite cool to have class for handling custom meta tables, uncluding re-routing the wp_tax_query…

  13. Steve Taylor avatar Steve Taylor

    I’ve not heard anything about that, but then I’ve not been looking!

  14. Jake avatar Jake

    Hi Steve, I’m so glad that I came across this article, but there is a small bug that I found in your code.

    In your example, the ‘my_custom_meta_update’, ‘my_custom_meta_delete’ and ‘my_custom_meta_get’ functions have the default behavior of these filters (they aren’t necessarily actions) should return the value of $check and not ‘null’.

    I ran in to this issue tonight when trying to get this to work with multiple custom post types using external tables for meta data. I spent several hours trying to figure out why one CPT was populating correctly and the other wasn’t.

    The default value of $check is ‘null’ when it gets passed in to the filter anyway.

    My issue was the first CPT’s filter had the proper data being returned, but the second CPT’s filter was overwriting it with null.

    Thank you for the detailed explanation of how this works. It’s saved me headaches down the road for sure! :)

  15. Steve Taylor avatar Steve Taylor

    Hi Jake, glad you found the code useful! You’re right, those should be add_filter, not add_action.

    However, I’m not sure about returning null. Null should be returned if the regular metadata processing needs to happen – non-null will short-circuit things. I guess you’re right that $check is null anyway – so is there any difference between returning null and returning $check? Hmm, maybe this is related to another plugin also adding a filter, returning non-null, before this filter, which then returns null and carries on with the original processing?

  16. Jake avatar Jake

    Hi Steve,

    Yes, if another plugin is also hooked in to this filter the results can be unexpected.

    Say that you have two plugins that set up two different post types (“type_1” and “type_2”).

    If you return null by default, then if your first plugin filter for “type_1” validates and gets the data, a second plugin filter “type_2” would not validate and would then reset it to null since both are hooked in to the filter.

    If you return $check by default, then the value would be passed through properly through all filters, giving you your expected result regardless of filters that are ran afterwards and fail validation due to the post type.

    Hopefully that sheds some light on the little bug when dealing with multiple post types. :) Thanks again for the great tutorial!

  17. Václav Greif avatar Václav Greif

    Hi Steve,
    will this also work with WP_Meta_Query?

    I’m currently working on a big e-shop with 100k products, and the postmeta table has more than 6 billion rows, as WooCommerce saves all the product data including price and availability as postmeta.

    I was thinking of creating a custom table with this basic info and re-reroute the WooCommerce calls…

  18. Steve Taylor avatar Steve Taylor

    I never worked with WP_Meta_Query with this code – I think I posted this before that was part of core. You’ll have to experiment yourself to see.

  19. Arun avatar Arun

    how do listing in admin and filter the admin

Comments are closed.