WordPress post to post relationships, without altering the Database

Using WordPress as a framework for a web app, often requires some creativity on applying your desired data model to the standard WordPress database setup. This article wants to explore possible ways of creating WordPress Post-to-Post relationships. But, be warned, it is not about best practices, performance or perfect database modelling. Its more an attempt to list some options to consider before adding custom tables and queries and working around all the secure and tested WordPress API functions.

Consider the enhanced entity relationship (ERR) diagram below. It shows some simple relationships of an exemplary author database. There is a many-to-many (n:m) relation of authors and books and in addition a one-to-many relation of publishers and authors.

There are several ways to implement such a data-model without adding any custom tables to the WordPress database. The one thing you have to do in any case is to add appropriate custom post types, extended with suitable meta data.

Implementing custom post types and fields

WordPress gives us custom post types and fields to build our own data-structures into the database. In our example, custom post types correspond to the three main tables (authors, books and publishers). Following our model, we would define three post types, named author, book and publisher, like the one in this, somewhat generic, code example:

register_post_type('author',
	array(
		'labels' => array(
			'name' => 'Author',
		),
		'public' => true,
		'has_archive' => true,
		'supports' => array('title','editor','custom-fields')
	)
);

That would provide us with the extra post types in the admin main menu. Posts of these new types have a title field and an editor field by default and you are able to add whichever meta-data you like, through custom fields.

Relating Posts

That was the easy part, but now it gets more tricky. How do we relate posts of the custom post types, just by using the WordPress API and no custom tables? There are a some of candidates among all the WordPress components that might work.

hierarchical posts

WordPress posts, and so posts of custom post types, are already designed to have an relation, if you want them to. It is a simple parent-child relationship that you might know from WordPress pages. Thus, all of our custom posts can have one parent and, luckily, it mustn’t be the same post type.

meta data

As you read above, we can use post meta data to add any kind of information to a post. It could be a ISBN-number to a book, but it could as well be information about relations between one post and another. As you will see we can realize all significant relation-types with metadata.

custom taxonomies

In contrast to posts, which are holding content, taxonomies are organizing it. You already know the two default taxonomies WordPress provides: categories and tags. Even taxonomies can be used to establish relations, but as I think meta-data is the cleaner way, there will be no example in this article.

Lets have a look at a simplified WordPress data model and see how these component are build. Hierarchical posts are realized by a post_parent column in the wp_posts table, which can be set to the ID of any post. Post-metadata is implemented through the wp_postmeta table, which is able to hold multiple metadata rows for each post.

Now, what we want to do, is to utilize the existing database schema, to get the relations between posts of varied types to work. But, our goal is to do this only by using the API, so we can’t just start to hack SQL-queries. Lets go through the types of relations we want to build one by one and take a look at how it can be done.

Implementing an one-to-many Relation, using the post_parent-column

Now, how to implement an one-to-many relation, like the one between publishers and authors in our example model? The section on hierarchical posts might have given you the idea. An 1:n relation is nothing more than a parent-child relationship. One parent post can have multiple children, but one child post can only have one parent. So, lets say, we are inserting an author-post, the only thing we have to do is to set the post_parent property and we are done.

$publishers_id = 22; // insert the real id of a publisher-post here
$author = array(
   'post_parent'    => $publishers_id,
   'post_status'    => 'publish',
   'post_title'     => 'This is an example author',
   'post_type'      => 'author'
);
wp_insert_post( $author );

Retrieving parent posts is equally simple. Just use a custom WP_Query object for getting all the authors related to a publisher:

   $args = array(
      'post_type' => 'author',
      'post_parent' => $publishers_id
   );
   $authors = new WP_Query($args);

The drawback of this method is that you aren’t able not relate a post to more than one parent, because all the posts are in one table. Thus you can only build a parent-child relation between one post type and another and not one post type and multiple others. Do you have a more complex model than we have, chances are big that you have more than one 1:n relation on some post types. For example, if we wanted to add labor unions to the model. That would mean that you had to set both a publisher and an authors union as parent, which isn’t possible.

Multiple one-to-many Relations, using post-metadata

If you look at the WordPress database scheme above, you can see that one post can be related to multiple wp_postmeta rows. The idea is now, to use this existing relation in the database for our relation of multiple post types – we want, in our concrete case, to relate some authors to both a publisher and a union. Our implementation is basically a variation of the post_parent method, as we will just create additionally post_parent-meta-data for every post type we are relating.

// insert the real post ids here
$publishers_id = 1;
$unions_id = 2;
$author_1_id = 3;
$author_2_id = 4;

// add relations, author 1 has only a publisher, author 2 has both publisher and union
add_post_meta($author_1_id, 'publisher_id', $publishers_id);
add_post_meta($author_2_id, 'publisher_id', $publishers_id);
add_post_meta($author_2_id, 'unions_id', $unions_id);

Even retrieving the posts again is only slightly different from the post_parent-method we used above. Instead of querying the posts by post_parent we have to query them by meta_key.

   // getting a publishers authors
   $args = array(
      'post_type' => 'author',
      'meta_key' => 'publisher_id'
      'meta_value' => $publishers_id
   );
   $authors = new WP_Query($args);

   // getting a unions authors
   $args = array(
      'post_type' => 'author',
      'meta_key' => 'union_id'
      'meta_value' => $unions_id
   );
   $authors = new WP_Query($args);

A note on 1:1 relations: Implementing one to one relations is basically the same as implementing one-to-many relations. The only difference is that you manually have to set a constraint, limiting the application to relate not more than one post to another, so that a post can’t set as parent if it already is the parent of  another post.

Many-to-many relations, using post-metadata

Many-to-many relations can’t be created with the parent_post-method. They are usually created by using a junction table, which holds the keys of two related posts in each row. In the author database model at the beginning of this article, you can see a junction table between authors and books, which physically enables a n:m-relation via two 1:n relations to the junction table. Now, take a look at the simplified WordPress database scheme and the wp_posts/wp_postmeta relation. There is a one-to-many relationship between them, but, since we relating author-posts to book-posts, stored in the same table, there actually are two one-to-many relations and the wp_postmeta table is working precisely as a junction table. The only thing we have to do, is to adjust the meta-data example above, to save multiple related post ids, instead of one. Luckily, the WordPress API enables us to do that.

// insert the real post ids here
$book_1_id = 1;
$book_2_id = 2;
$author_1_id = 3;
$author_2_id = 4;

// add relations, author 1 has only a publisher, author 2 has both publisher and union
add_post_meta($author_1_id, 'authors_books', $book_1_id);
add_post_meta($author_2_id, 'authors_books', $book_1_id);
add_post_meta($author_2_id, 'authors_books', $book_2_id);

That was all. Retrieving the ids of the books of an author is as simple as reading the authors_books-meta-data of an author post. If the author is related to multiple books, you will get an array of ids, which you can use to read the book-posts. Getting all the authors of a book requires a custom WP_Query with meta_key-option, just as the one we used on 1:n relations.

   // getting a books authors
   $args = array(
      'post_type' => 'author',
      'meta_key' => 'authors_books'
      'meta_value' => $book_id
   );
   $authors = new WP_Query($args);

Many-to-many relations with additional relationship information

Sometimes you need to add a piece of information to the relationship itself. How many chapters of the book are written by each of its authors, would be an (uninspired) example. Usually you just add columns to the junction table, but that’s not possible in our case, because we don’t want to change the database setup.

Since the only way to dynamically add custom data are custom post types, we have to create one, just to store the relation-data. Lets call it author_book. Now, when we create a relation between an author-post and a book-post, we even have to create an author_book-post, store some data in it and connecting it to the relation itself. We are doing this, again, with post-metadata.

// post ids, of authors and books
$author = 1;
$book = 2;

// insert relationship post
$author_book = array(
   'post_status'    => 'publish',
   'post_title'     => 'Information about the relation between author and book',
   'post_type'      => 'author_book'
);
author_book_id = wp_insert_post( $author_book );

// relate posts
add_post_meta($author_book_id,'author',$author);
add_post_meta($author_book_id,'book',$book);

The author_book-post is now related to both, the author– and the book-post. Retrieving the data, has to be done in several steps now, which might be ineffective – but, as I said before, this article is not about finding the most performant solution.

// retrieve authors of a book, plus information post
$book_id = 1;

$args = array(
   'post_type' => 'author_book',
   'meta_key' => 'book'
   'meta_value' => $book_id
);
$author_books = get_posts($args);

foreach ($author_books as $author_book) {
   $author = get_post(get_post_meta($author_book->ID));
   // do sth with the author
}

Conclusion

Relating posts of different posts types is one of the key features for using WordPress as an web-application framework. Using post-metadata and some API functions, it is possible to implement 1:n, 1:1, n:m and even data-holding n:m relations. The pros are, that you can base on the reliable, tested and continuously bug-fixed WordPress API. The cons are that you might run into performance problems, not using an optimized database. However, I think the discussed solutions for one-to-many, one-to-one and many-to-many relations are at least considerable and could be used in production. While the last case of simulating a junction-table with content columns was more of a showcase whats possible.