One-to-many Relationships in WordPress

WordPress is exceptionally flexible when it comes to changing existing behaviors and implementing new ones, and with aid of the multitude of API’s many things are possible.

One-to-many Relationships In WordPress

When you start extending the default data structures in WordPress, most of the time you’ll be extending Posts, either default Posts (or Comments) or Custom Post Types, other times might be extending User objects. Extending would most of the time mean adding more data which you can query by and pivot around.

The most primitive example would be the extension (creation) of a Custom Post Type called “Book”, which already has a post_title, post_content, etc., with a number of additional custom fields, like ISBN, price, etc.

Posts (Pages, Attachments, Custom Post Types, etc.), Users and Comments can be thought of as object models, and these are related to one another. Metadata (postmeta, usermeta, commentmeta) can also be viewed as objects. When you get_metadata you’re retrieving objects that relate to another object (ISBN number of a specific book).

One-to-one relationships

Note: the relationships are between Post/User/Comment objects and Metadata (which can be an object ID)

get_metadata is a one-to-one relationship, meaning that there is only one Metadata set for related to another object. The ISBN number of a specific book will be stored in the `postmeta` table and the relationship is resolved via the post_id field. Like: get_postmeta($book->ID, 'isbn'); will return one ISBN for that one specific book.

Many-to-one relationships

For comparison purposes only, I’m using the term “many-to-one”, even though it’s not generally used. Emphasis is put on a many parents – one child relationship.

If you would like to have many Post, User, Comment objects access a single piece of Metadata, you would probably go for Options, Transients, Taxonomies. Built-in many-to-one relationships exist between Comments and Post, Posts (Attachments) and other Post.

One-to-many

One-to-many relationships can be more complex, where one Post/User/Comment can share a multitude of extended data entries in the form of Metadata. How does one implement a one-to-many relationship correctly?

It would all depend on the context. Simple data structuring often brings about no problem. Use Taxonomies (a book can be in many same genres) or Metadata (a single book can have multiple authors).

Multiple authors? Isn’t that the perfect job for Taxonomies then? Sure, if your author is just a name and doesn’t have a Location, a Date of Birth, some Bio information, a Rating, etc. Taxonomies can’t accommodate complex data structures.

Metadata

Let’s serialize it all…

On second thought, let’s not… serializing kills all of the querying power of MySQL, it won’t unserialize the data for you if you want to suddenly order by rating, will it? Of course if you don’t need searchable data and sorting of complex data, you can use serialization. In many cases you’ll get to a point where you need to sort the data eventually, even when the specification is 100% sure that you won’t be sorting and ordering by a key, you might actually need to in the future.

Managing such collections would need you to get back the meta_id so you can update and delete it specifically after reading. It’s a one-to-many relationship, so you’ll have a lot of ‘author’ keys. WordPress does not return the meta_id so you would have to wrap it up a bit like so:

function get_post_meta_specific( $post_id, $meta_key ) {
  
  global $wpdb;

  $post_id = intval($post_id);
  $meta_key = $wpdb->escape($meta_key);

  $sql = "SELECT * FROM {$wpdb->postmeta} WHERE `post_id` = '$post_id' and `meta_key` = '$meta_key' $rev";
  $results = $wpdb->get_results($sql);

  /* Unpack the data */
  foreach ($results as $index => &$result) {
    $result->meta_value = maybe_unserialize($result->meta_value);
  }

  return $results;
}

A very lucrative but bad idea overall. Good for quick and dirty prototyping maybe, even then, I wouldn’t recommend it. There could be cases where it’s appropriate, but I can’t think of any right now.

I’m not saying that you should not serialize Metadata, there are perfectly excellent uses, like storing settings sets. Perfectly valid for a one-to-one or many-to-one relationship.

Another Custom Post Type

Another solution would be to create a new custom post type called Author, for instance, and enjoy the full power of custom fields. Ordering by meta_value is possible, so there should be no problem to order an author by his/her total number of words written. With added Taxonomies you can stuff Locations into there as well.

The relationship would be tracked via an ‘author_id’ meta_key which holds the ID to the custom post type. A book would have several ‘author_id’s attached. A great advantage is that authors can be reused by other books.

What if an Author has a Publisher, another complex relationship. This grows more difficult the further you take it. Returning whole objects would become increasingly inefficient by standard WordPress means, and table pivoting, potentially with a lot of joins and very long queries, or even procedures would start to appear in your code.

So useful, searchable, highly flexible, sharable among parent objects and ever-extensible… to a certain extent.

New tables

Custom tables in the database with a custom structure will fit the most complex structure imaginable, probably. WordPress doesn’t limit you in creating and using your own tables in its database. One would then reference these sortable and searchable structures in Metadata entries to posts. Queries will have to be manual. This solution is low level and very flexible and complex. You won’t be able to use many of the built-in WordPress functions to query for objects.

How the big boys do it

Here’s an overview of how it’s done in some complex WordPress plugins.

  • BuddyPress – highly complex, installs its own tables to manage data relationships efficiently
  • MailPress – a little less complex, utilizes the power of additional tables in the WordPress database
  • The Events Calendar – registers post types for Venue and Organizer, references those
  • Gravity Forms – uses 8 tables of its own
  • WooCommerce – tables again, does away with 3

You’ll find that the big boys never hesitate to create new tables, they need them. Such plugin environments become highly customizable and flexible.

Conclusion

In general I tend to think that when you’re talking about one-to-many relationship, where there’s only 1-3 data structures, it’s possible to do away with Custom Post Types for each structure unless they’re very massive (remember each extended field requires a Metadata entry). When more flexibility and power is needed, when there are tons of structures – then database tables come to the rescue.

Would love to hear what you think. What good and bad practices have you encountered? What one-to-many relationship management techniques do you employ in WordPress, what are their use cases, examples?

Speaking of relationships, don’t forget St.Valentine’s day!

Update #1

Shout outs to @Rarst for pointing out a plugin called Posts 2 Posts, which allows you to tie all of the default and custom post objects together in many-to-many relationships. It uses an intermediary database table to keep track of these relationships. Highly useful and streamlined. Very worth looking into.