How can I add a column/s to wp_posts table?

Question

I am trying to sync WP with another service that offers an API that allows me to hook up their table's Update, Create, Delete to my sites.

So when they create, update, or delete a row on their server, I get details about that update, including the row ID.

The way I am thinking of syncing is by using custom post types, and either add a new separate table with post -> api relation like post_id:1 = api_id:53.

Or alter wp_posts table by adding a column containing the api_id.

I know I can add metabox which will add that as post_meta but I am not sure how effective will that be and I am pretty sure if I alter wp_posts, querying will be a lot faster.

So how can I alter wp_posts in the WordPress way? If that's not possible, should I add meta boxes or create an entirely new table just for the relation ?

Answer 1

You can technically add a post column with SQL but I'd caution against it as backup scripts, exports etc. would likely ignore it.

Instead, I would add the content as a post_meta, either using a Custom Field or through PHP with the update_post_meta() function.

To fetch a post based on the meta simply use:

$args = array(
        'post_type' => 'custom_post_type',
        'meta_key' => 'api',
        'meta_value' => $api_value,
      );
$posts = get_posts( $args )

or, to fetch the value of the API key

get_post_meta( $post_ID, 'api', true );

Answer 2

I would highly recommend creating your own table just for this relation. In this way queries can be faster and you won't have to worry about the table structure of WordPress ever changing and ruining your work (and potentially dropping your column and all its data).

One thing is for sure, you will always have a id to match up against.

EDIT: Could be done this way, but post_meta has some larger advantages.

Answer 3

It sounds like you're trying to store meta-information about the posts. WordPress allows you to do this using its Custom Fields. The functions you'll probably find most useful are update_post_meta() and get_post_meta().

Answer 4

I came here with the same problem as the OP. What most of these answers here overlook is the fact that the postmeta table is slow to query, because the meta_key and meta_value columns are not indexed. So if you have an enormous amount of posts, and a correspondingly enormous amount of post meta, then it's going to be time-intensive to get the data you need.

The solution is to use the 'import_id' key when importing the data (which is assuming you're using wp_insert_post). This will allow you set the post ID to the API ID, manually. Here's the relevant section on the trac.