4

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 ?

4 Answers 4

6

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 );
1
  • well 99.99% of the time it would be the other way around, i get the api_id, and need to get the post_id for update
    – Neta Meta
    Jan 16, 2014 at 21:56
4

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.

3
  • A custom table is probably overkill -- WordPress allows the use of Custom Fields.
    – Pat J
    Jan 16, 2014 at 21:48
  • yea that's what i thought mike yea custom fields, meta boxes.. achieve the same goal basically
    – Neta Meta
    Jan 16, 2014 at 21:50
  • Understood, I could see how it would be easier to leverage the WordPress-y post_meta instead of using another tabled, but I think my way could be faster. Jan 17, 2014 at 23:07
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().

2
  • I wont be able to use neither of those function as i wont know the post ID , when an update happened i need to use an external id to find the post id, and then update the post.
    – Neta Meta
    Jan 16, 2014 at 21:53
  • You'll need to know the post ID to tie the API ID to it in the first place. Then you can use @ChristopherRoss's first code snippet to get the post ID from the API ID.
    – Pat J
    Jan 16, 2014 at 22:27
0

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.

8
  • the answers do not overlook anything. When you work with wordpress you work under its limitation or better work with another framework. Changing DB schema is equivalent to changing core code, something you just don't do. Meta is used to extend post information in the way the OP needed, not to do "searches" over it Aug 16, 2018 at 1:48
  • If you just have to go this way, as other answers suggest, just create your own table. Aug 16, 2018 at 1:51
  • Are you saying that keeping the post ID tied to the API ID is changing DB schema? If so, why would they have the method to use 'import_id' in the first place? Here's my use case: I'm working with a large database that has 500k posts in it. Those posts are ported in from a different DB, where they have their own IDs. When I run an update from DB 1 to DB 2, I have to use a expensive meta query to figure out which post to update. If I keep the IDs the same, the query is much much faster.
    – jhned
    Aug 16, 2018 at 18:24
  • the downvote was mostly related to your first paragraph and I admit of missing the point of the second. Still it sounds like an inappropriate use of a column which admittedly core should never had had in the first place, if you want to go that direction guid might be more appropriate. Looking at the documentation of the function developer.wordpress.org/reference/functions/wp_insert_post import_id is not even a valid parameter to it and the risk that at some point core will use it for something else is on you Aug 17, 2018 at 0:35
  • I agree, that's a fair point that import_id could be deprecated. I disagree with your assertion that the answers here don't overlook anything. Two out of three of the answers suggest using post_meta which, as I stated in my original answer, is really slow to query against when you've got hundreds of thousands of posts. WordPress VIP won't let you use meta queries in most cases for this exact reason. Not taking that use case into consideration is the definition of overlooking a detail.
    – jhned
    Aug 17, 2018 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.