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.
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.
On the BuddyPress forums, sometime people ask why we don’t store our xprofile field data in the usermeta table, and you’ve pretty much explained why. 🙂
Great article on not only good WordPress design, but also good database principles. Is there any good reference material out there on how to do a one-to-many with two custom post types?
I would like to have each record of the “many” side to be listed on the edit page of the “one” side, allowing me to dynamically add records without separate pages for them.
Thanks for stopping by, Andy. If both are custom post types then you’re probably taking the simplest option of storing the “many” IDs in the post meta table of the “one” side. As for managing these connection it’s a question of UI; look into adding meta boxes and provide an interface you’re comfortable with. Let me know if this doesn’t make sense.
Awesome! Thanks for the quick response; this looks like exactly what I was looking for. I’m very new to dealing with admin stuff in WordPress (I’ve done quite a few themes), so this article was definitely something I needed.
I’m looking at creating a one to many relationship between the user object and a contact object. The contact object would have fields like last name, email, job title etc. One wordpress user could have multiple contact objects associated with them.
What would you recommend for this implementation ?
Well, I’d think about creating a post type called “contact”, and use usermeta to store one ID per row (non-unique keys). The contact fields can be stored as postmeta, obviously.
Finally a response 🙂 I ended up going with a custom table for my solution. Cheers.
We’ve just released a new plugin MB Relationships that provides an easy way to create many-to-many relationships between posts, terms and users. It supports bi-directional relationships and use a custom table for storing relationships (for better performance).