Complex data structures in WordPress: WP Café #3

In episode three of WP Café, we’re joined by John James Jacoby and Elliot Condon (ACF) for a chat about how to deal with complex data structures in WordPress.

Elliot and John really know their stuff but what’s really refreshing about this conversation is how open and pragmatic they both are about the best approaches to take.

There’s no dogmatism here, just the acceptance that there are lots of ways to achieve the same things. The ‘right’ approach depends on the situation but the most important thing is just to get stuff built. Don’t stress about the minutiae at the start.

They are also both just really nice guys and were really easy to talk to.

We hope that you enjoy this conversation as much as we did.

Make sure to subscribe to our YouTube channel and follow us on Twitter for notifications about the next episode.

Transcription

Keith:
Okay. I think we’re live. So hello and welcome to Episode Number Three of WP Café, a show where we chat with WordPress professionals about challenges, solutions and ideas around WordPress development for solo and small WordPress development teams. My name is Keith Devon and I’m today’s host. I’m joined by my co-host Mark Wilkinson, in this episode we’re pleased to welcome two familiar faces, John James Jacoby and Elliot Condon. Welcome to the show, guys.

John:
Hi. Thanks.

Elliot:
Hi.

John:
Happy to be here.

Keith:
So we’ve called this episode Dealing With Complex Data Structures in WordPress. And what we mean by that is, I guess, working with WordPress when we’re pushing it a little bit outside of its comfort zone. So we’re going to cover a few things like custom post types, when to use them, custom database tables, when to use those, metadata, taxonomies, post relationships, URL structures. And as always we’ll be keeping an eye on the comments for listener questions or viewer questions, so please add those there and we’ll do our best to put them to the panel today. So okay, before we get into the nitty gritty, John, can you introduce yourself, please?

John:
Sure. So most people in WordPress call me, or know me, as J-trip, but you all can just call me John. My friends just call me John. And I’ve been using WordPress since 2005 or 2006, and lead the BuddyPress and bbPress projects. I’m a member of the WordPress.org security team, and I think one of the things that qualifies me for being on the show today, is BuddyPress is largely known for being a behemoth of a plugin that uses custom database tables for everything. So when it’s custom post types versus custom database tables, usually BuddyPress is what folks think of. So I’m happy to be here. I was really excited to get the invite. I think the show is awesome and I think it’s a great idea. Plus it’s awesome to be here with Elliot who I don’t think I’ve met in person. So this is exciting for me.

Keith:
That’s cool. It’s nice to bring you guys together. So Elliot, can you tell us a bit about yourself please?

Elliot:
Yeah, hi. My name’s Elliot. I’ve been working on a WordPress plugin called Advanced Custom Fields for the past nine years. Before that I’ve got a history with freelance web design, agency web development, and all round just enjoy hacking, building stuff, in and around WordPress and web development space. So I should have some kind of knowledge with custom post types, a lot of about custom database tables, and yeah, hopefully I can help share and explain some stuff.

Keith:
That’s super cool.

Elliot:
Cool. Yeah.

Keith:
It’s especially cool to have you guys together, considering the timezone differences. I don’t know what the difference between you two is at the minute, but it’s pretty massive. I think, John, you’re up super early, and Elliot, you’re probably staying up pretty late for this, I’d imagine.

John:
Yeah, it’s 5 a.m. here, and it was one of those where, well, how do I not screw this up? Because I will definitely oversleep. So I’m a little groggy this morning, but I am excited and happy to be here. We’ll make it up.

Keith:
What time is it with you, Elliot?

Elliot:
It’s 8 p.m. here, so I cannot complain. It’s absolutely fine.

Keith:
Okay. So it’s John we need to feel sorry for, really.

Elliot:
Absolutely.

Keith:
All right, cool. So let’s get into the show, then, and into some questions. Lots to talk about. Again, if anyone has any specific questions, stick them in the comments and I’ll be keeping an eye on those. Well, let’s start off with a well-known discussion which is custom post type versus custom database table. So often a custom post type is potentially an easier way of creating an object to store data in. It gives you a lot out of the box. But there are potentially some drawbacks. So when should a developer choose to use a custom post type over storing data in a custom database table? What are the benefits and drawbacks of each? Go to you first, John, for that.

John:
I think most of the time, custom post types are probably the place to start. Because you can get something going, and prototype something, so extremely quickly, and you get so much for free because it’s hooked into all of the inner workings of WordPress. Just with a couple of lines of code, you just get so far. And I think in the beginning of building an app or a plugin or a thing, that I think most of the time, people don’t really consider the data structure right off the bat. That’s not top of mind for most people. And that reveals itself as the thing starts to take shape.

John:
It is certainly possible for some people, and everyone works differently, where maybe the data structure is important and that is a thing that you do consider, and you can start there and work your way the other direction. I don’t think that’s what most people, and I don’t even think that that is what I would probably recommend, because I think one of the old-school UNIX philosophies is to make it popular first and to make it good second. So custom post types will get you something that people enjoy using and they don’t care what’s underneath it, and you can always make it better and scale it later, and improve how it performs and everything else. But if nobody uses it and it’s not good, then you’ll never get a chance to improve it. So I think usually custom post types are the way to go.

Keith:
Okay. Thoughts on that, Elliot?

Mark:
That’s a great snippet just there.

Elliot:
Yeah. Yeah. Totally agree. I mean, custom post types, like you say, get you so much for free out of the box. You get admin interface, the ability to edit, create, delete, all of that good stuff. You also get permalink structures straight out of the box as well, so you can access your data without any custom code, so that’s great. And a huge array of functions at your disposal. And tables as well. You’ve got metadata capabilities straight away, and you can connect with term objects as well. Yeah, so heaps of good stuff. And now even REST API scaffolding, just straight out of the box, which is pretty cool.

Keith:
Yeah. Cool. Mark, you were… Sorry I jumped in there. What were you-

Mark:
I was just saying, what John was saying was a really good snippet. If it’s not popular then it’s never going to succeed, so I think that’s a really good philosophy. Make it work and then worry about dealing with making it better afterwards. That’s a really good philosophy.

Keith:
So in terms of making it better, if there is a point where custom post types maybe don’t do the job any more, what is that point? When do you need to start considering something outside of WordPress?

John:
Yeah. I think that’s tricky, and I think that point is different for every project. You can look at a plugin that is as huge as WooCommerce and as popular as WooCommerce, and they’ve made it incredibly far in terms of just community building and support, and without necessarily needing to rely on custom database tables for a big chunk of its success. But inevitably when something grows to that scale, and the demands of an application like that just become as massive as something like WooCommerce is, the decision seems obvious to try and move things over to a custom database table of some kind.

John:
WordPress is a unique piece of software when it comes to custom database tables, because it doesn’t give you a whole lot. It leaves you on your own to figure all of that stuff out. So everybody’s fluency and comfortability with having a custom database table and creating a schema that’s going to perform well, and then adding caching on top of it, and then getting permalinks to work again, and then building their own editing interface again, and then doing all this stuff, it’s like, well, it’s a lot of work to redo all that stuff.

John:
So depending on the size of your shop, or your level of comfort with the APIs underneath it, it could very easily be a thing that, sure, your plugin would benefit from, or your code needs, but you just might not know how to do all that stuff. Custom permalink structures, they can get pretty crazy. And the REST API is infinitely extensible. So how comfortable are you writing your own scalable REST API endpoints? It just isn’t a thing that I think most people should be expected to understand all those things. And you just get it for free with custom post types. So I think that point’s different for everybody. For every application. And I don’t know that there’s tell-tale signs. I think your community tells you. I think your users tell you, and you listen to them and go from there.

Keith:
Are the wins with a custom database table, are they purely performance wins in terms of query speeds and things like that? Is that what you’re talking about?

John:
I think yes. There is something nice about owning your own data structure, I think, in a plugin. Because with WordPress we are locked in to what the design of those database tables is, for better or for worse. And it is a little bit crazy, how shockingly good that the database schema is in WordPress. How flexible it is, and how useful that it is. We’ve been able to get a lot done with posts and meta, and comments and meta, and users and meta, and taxonomies, and terms. We’ve gotten real far with it.

John:
So it is largely performance-driven, but one of the things I said in my WordCamp Europe talk, the thing with owning your own database schema is you get to change it. You get to decide when something isn’t working correctly or performing well, and you can change it whenever you want to. Whereas with WordPress you don’t get to change it. You have to fit your data inside of whatever WordPress gives you. So you’re trapped with WordPress’s core tables and you have to figure out how to make that work, or someone else has to figure out how to make it work around whatever it is that you’re trying to build.

Mark:
It’s quite exciting for me as a developer, listening to you, John, an experienced person who understands this stuff a lot better than I do, saying that actually, custom post types will go a long way, actually. Because I read a lot of people Tweeting out saying, “Oh, you shouldn’t be doing it with that, you should be doing a custom method,” and something. It’s quite reassuring to hear that actually the method we often choose is a decent method and will get a long way, which is good.

Mark:
Because I know ACF uses post types, doesn’t it, Elliot? To do a lot of its field groups and things. Have you ever thought about moving that to custom database table? Have there been any problems with the way you’ve done it?

Elliot:
We actually moved back from custom tables to post types. I dabbled with custom tables, I think it was in Version Two of ACF. I started with post meta and then I went to custom tables, and then I opted back for post types. For a lot of reasons. It wasn’t a performance-related reason, it was more about compatibility, and flexibility. So doing things the WordPress way opened up more doors for the plugin.

Elliot:
At the time, I think I was being restricted on some hosting services, which I don’t think would be a problem now, but just the fact that there were custom tables, those hosting services wouldn’t allow ACF to be installed. I don’t think that’s a problem now, because custom database tables seem to be actually the way to go with a lot of popular plugins.

Elliot:
But I’m glad we did the move, and I’m definitely staying with custom post types. I think they work really well for ACF fields. A typical website you might create maybe five field groups, five to 10 field groups, and you might create a total of 100 fields. So it’s really not that many entries in the WP posts table. When you think about even creating a few pages and add on 10 revisions each, and there’s your 100 entries anyway.

Mark:
Awesome.

Elliot:
But I was thinking just before, at what point do you consider moving past the WP posts table into a custom table? And I think it probably does come down to performance aspects. And then maybe there’s also some edge cases where there’s functionality that you can inherit from using a custom table. So the functionality would be relational stuff. So the posts table, anything really, with the WordPress tables, apart from using terms, because we’ve got the term relationship table and that’s great. But if you’re trying to relate posts, you’re quite limited in what you can do there. So yeah, using custom tables is obvious way to get around those limitations.

Elliot:
But then, performance, I mean querying performance, it would be much faster to query a custom table that’s been indexed perfectly to work on your specific data, and your specific application, as opposed to using the meta and term queries built into the posts API. Which are great, but I think at some scale you’ll start to see some kind of issue. And we’re talking big scale. Anything up to maybe a million entries and you’ll be fine, but once you start pushing past that, which does happen. I mean, you can bloat out a post table pretty quickly, especially when you’ve got maybe community involvement. It doesn’t take long for the replies and comment threads, and a forum could blow up pretty big pretty quickly.

Elliot:
Also, on that note just one last thing that I want to say about performance, you should also be thinking about not just the performance of your own query, so for example if you’re creating a custom field plugin when I’m using a custom post type, or you’re creating a real estate website where you’ve got a real estate post type, to not just think about the performance queries of that specific post type, but also think about the performance impact of the overall website. If you were to create this real estate listing website that had a million listings, well, every hit to your website to find the homepage will have to, unless there’s caching involved, it’s going to hit this WP posts table that has a million entries, even though there might only be three pages. So that’s probably when splitting things into individual tables will have a really good benefit to the project.

Mark:
Yep. Definitely. And I guess that’s where caching would obviously help, wouldn’t it? With things like that, in terms of querying all those entries.

Elliot:
Absolutely. Yeah. I mean everything at the end of the day comes down to smart planning, and every project has unique goals it needs to solve or it needs to hit. And yeah, yeah, the caching techniques, or just anything along them, you can get very creative in that area.

John:
It’s so easy to take caching for granted in WordPress core, because so much of WordPress is finely tuned, in terms of not just query caching, but object caching, and making sure that things like, when you edit a post that all of the related things in the cache you get updated correctly. So when you have your own custom database tables, you have to manage all that yourself. And if something is not in the cache, or if cache doesn’t match what’s in the database, something is mismatched, then it’s your problem, it’s not WordPress’s problem. And you have to try and trace that back.

John:
WordPress does a lot of things for very specific reasons that you don’t, as a user, or a developer who’s not deep in it, you don’t really need to worry about or think about. WordPress, you can select as many posts as you need to, but if you’re going to update a post or delete posts, WordPress doesn’t have WPDB methods or functions for updating many rows at a time, or for deleting many rows at a time. So it deletes them atomically, it deletes them one at a time. In the event of the failure the query only fails on one row instead of failing on a million rows, or 10,000 rows, or something. So these big operations that you would think that you want to do, like emptying the trash in your comments, it’s doing that one at a time. It kind of doesn’t make a lot of sense, but the reason it does that is so that it doesn’t fail massively, it fails predictably and somewhat gracefully.

John:
So some of the performance benefits that you think, like, “Oh, I have my own custom database tables, I can write all these cool functions to update many rows or delete a bunch of rows and I can do it more efficiently,” and then eventually you realize, “Well, it’s never going to work in a live application that has millions of rows, because when it dies it dies catastrophically now, or you run out of memory on your database server or you don’t take into account things like HyperDB or LudicrousDB or you’ve shared your database tables across multiple database servers. So your tables aren’t actually on the same server, and joining across tables where you shouldn’t be.

John:
It’s like, okay, well, WordPress core and custom post types kind of manage that for you, but your own database tables, you’re on your own to learn all these things that no one tells you. So the opportunity cost of thinking that it makes sense to move your app over to custom database tables can sometimes just be so huge that it’s not worth it for a long time. The performance benefits, you’re like, “Of course, it makes a lot of sense.” But then you get there you get there and you’re like, “Well, this is going to take forever.” And Elliot, I can’t imagine going to custom tables and then coming back. It’s twice the work.

Elliot:
[crosstalk 00:20:14]

John:
[crosstalk 00:20:14] again.

Elliot:
It was in the early days of the plugin. No one used it back then. It’s fine. Lesson learned.

John:
Well, and with [crosstalk 00:20:26] Oh, go ahead, go ahead.

Elliot:
I was just going to say lesson learned. I mean, you start with something that works, right? And then you go and explore all these other options and you just come right back to what works and you can justify it.

John:
Mm-hmm (affirmative). That’s right. We thought about, in BuddyPress, we were going to just have a bunch of global functions to get anything, like a get post, update post, delete post, kind of approach to just BP Get, and then list whatever the component was. Because people are so used to the convenience of a simple, singular set of functions to get data in and out, whereas calling BP Activity, Get Activity, BP Friends, Get Friend, BP Groups, Get Group, it’s just too many dumb functions for people to remember. Even if the pattern is there, it’s just a lot of PHP to write or remember. So the convenience of those functions in WordPress is hard to beat, because it’s just so simple.

John:
But the original bbPress used custom database tables. It even had a shared metadata table between topics and posts and forums. Instead of each one having its own meta, they were all shared with an extra key for what kind of type of meta that it was for. And it made scaling bbPress forums relatively simple because all the meta, you could just grab it all at once for everything that just happened to be in the query, instead of having to go and get forum meta, and then go get topic meta, and then go get reply meta. You’d just get it all in prime and stuff.

John:
But nobody really liked using old bbPress because it was a whole bunch of new functions to learn and remember and use, and because the forums were built using bbPress, the original WordPress.org was a bbPress site, it was like, people just didn’t want to learn all that again. And it wasn’t documented as well. And it wasn’t kept up to date with WordPress core all the time. So it made more sense to make bbPress a plugin.

John:
So sure, the forums have millions of posts in them, and the post tables, they get a little creaky, a little slow. There’s a little bit of magic sauce in WordPress.org to make bbPress work well. But it’s still custom post types in the forums with millions of posts in there, because it’s easier for the community to support and build on top of than custom database tables are.

Keith:
Cool.

Mark:
And I guess if you’re using custom post types, like this example with the real estate stuff, any developer knows how to get a property, or knows how to get data about a property, because it’s all just WordPress standard stuff, isn’t it? Like you were saying, they don’t need to know these specific PHP things to be able to get the data, which is handy as well. Which is good. I think we’ve got some questions, haven’t we, Keith?

Keith:
Got a couple of questions. We’ve got one from Carly Reeves. She was saying, “What is the tipping point?” So I think we covered that. I’ll summarize it, as the layman of the four of us, I’ll try to summarize it as, stick with custom post types for as long as you can, and then whenever you find a need, a performance need or something like that, then you can think about making the switch. But is that roughly accurate?

John:
Yeah. I think it’s accurate.

Keith:
Yeah.

Elliot:
Yeah. Yeah. Unless you go into the project knowing that it’s going to be a large amount of data, then you can probably skip that first step.

Keith:
Cool. One more question at this point. When developing a WordPress plugin that uses a custom database table, do you have a migration workflow for modifying cable structure should it be required? If so could you offer some insight please? Is anyone willing to take that on?

John:
I’ll take that one, because we worked on that a little bit with a project that we worked on at Sandhills for Easy Digital Downloads 3.0, which has been in development for a while. But the database schema changes are updates to database tables. Again, they’re one of those things that you plan for growth, but you never really know exactly how that’s going to happen, or which direction that that’s going to go in. So WordPress has one function that is supposed to manage all those kind of things called dbDelta. It’s a function that takes the delta of what the table currently is and what you want it to be, and it’s supposed to [diff 00:25:00] it and make it work. But it doesn’t work in every case, so you can’t really trust it or use it reliably.

John:
And there are just so many operations you can do on a database table, if you’re trying to tweak it and lock yourself into something that’s going to perform as well as you want it to. We opensourced a library of database management tools that didn’t have a name when I was talking about it, but we named it BerlinDB after being in Berlin for WordCamp Europe. And the upgrade routine for it, the only thing that we built into it being opinionated was that it should happen behind the scenes and not be a thing that a user has to click on to click upgrade. It just happens by version in each one of the database tables behind the scenes, and just looks and sees if there’s a new version there, and then it just runs the upgrade routine that it needs to run in order to make it work. But it does that behind the scenes, it’s transparent to a user.

John:
I think the upgrade routine, you just need to be able to run it whenever you need to run it, but it should be able to be anything or do anything. And by anything I mean not just changing a column or adding an index or adding a column or whatever, but you could clone the entire table and do an operation in the background on a table that isn’t being used right now, so that if the site is live, and you need to be able to continue accepting purchases or something, that you can clone the entire table in the background, run the operation you need to on a ghost table or a hidden table someplace, and then move the new data over into the new table all on the fly in a way that people aren’t… The operations can be endless. You can do whatever.

John:
So, the upgrade routines in BerlinDB are intentionally open-ended. It’s just a method in a class that just hopes to get called if an upgrade needs to happen, but then you’re kind of on your own to figure out what the MySQL is you’ve got to write in order to upgrade your database table the way you need to, because it’s always different. We have some hacks on WordPress.org database tables to add indexes to meta. I have a plugin that adds a column to the WP terms table. You can just do whatever you need to, and WordPress isn’t really going to care about it.

John:
So I think just leave your upgrades open-ended, but you don’t want to bother users with it. That’s the thing, that everything, like Skype, we’re on Skype right now, has database underneath, that you never touch it and it upgrades itself all the time. Everything that you use, your Android, your iPhone, the apps inside of it, they’re all powered by MySQL-like databases and things, that it doesn’t bother you to do the upgrade. It doesn’t interrupt your day to go, “Well, the database is out of date. Why don’t you click this button?” It’s not a thing that we users need to be exposed to. It is only the kind of thing that our jobs as developers is to just do those things correctly for people, and not have to bother them with it.

John:
So it’s kind of a shame that WordPress doesn’t have more sophisticated tools to help us with these things, because we’d stop reinventing the wheel a little bit, but yeah, upgrades are so endless. We kind of do them bespoke a little bit I think.

Mark:
I think not bothering users is a good point, because I think that’s where users get nervous. They see the, “You need to upgrade.” And it’s like, “Oh. Don’t know what to do, do I press it? Do I not press it?” Otherwise, if it needs to happen then make it happen, if it doesn’t then don’t.

John:
And I think because WordPress doesn’t give us the tools for it, that it’s developers, it’s a very human thing to project our own anxiety onto someone else to share in the pain. So we’re not confident that the database upgrade is going to work, so here, it’s yours now, you click the button. It’s okay. I think it totally makes sense why we do that. But it’s not a good practice to get into I think.

Keith:
Yeah. That’s good.

Mark:
Cool.

Keith:
Cool. Let’s move onto another either-or debate, I guess. Whether to use custom fields or a taxonomy term to assign data to a post. When should developers be considering making the choice between a custom field or a taxonomy? What are the pros and cons of each? How do you make that choice?

Mark:
I think an example probably would be good. So we do a lot of work with jobs post types, and you see a lot of developers who want to mark a job as featured, so they can say, “Get me all the featured jobs.” Some create a category called Featured, or create a custom taxonomy with a term in there that’s featured, and then some just have a meta field that says Featured Job and it’s ticked or not ticked, so I presume it’s storing True or False. I just wondered if there’s a right way to do that, if there’s a wrong way to do that, if both are correct in certain circumstances, if that makes sense. Shall we go to you first, Elliot?

Elliot:
Yeah, yeah, yeah. First, what you want to do is you want to go get a plugin called Advanced Custom Fields Pro. I think this exact example of featured, that’s a nifty one, because it sits on the fine line between you could use meta for it and you could use terms for it, and I don’t think there’s a right answer for this, because on the one hand its primary function is to categorize those listings, and you’re going to be probably querying those listings at some point or at least ordering or sorting them to be at the forefront.

Elliot:
So in that sense, you do want them to be a term because you will get maximum performance out of querying by terms as opposed to querying by meta. Not all the time, but let’s just say, as a general rule of thumb, it’s most likely to end up that way. However, creating potentially a whole new taxonomy for this one specific thing, or having this category that’s just floating around in admin potentially waiting to get deleted accidentally and causing huge mayhem to the website.

Elliot:
That doesn’t feel right either, so maybe in the user experience sense, it should be a custom field. Just a little toggle switch, because essentially, it is a property of a post as opposed to a category. So that’s a real tricky one. I don’t know what I would do. I think if I had to make a call I’d probably go on the user experience side of things for the content editor, the author, and I would make it a custom field. Just a little toggle switch. And I would sacrifice a little bit of performance and yeah, I’d start that way. I guess you can always migrate in the future if you get a problem.

Keith:
Totally. Yeah.

John:
I totally agree. We build things for users, and so, whatever works and is easiest for users I think… I like website performance and tweaking stuff as much as the next person, but sticky posts in WordPress are meta. They’re not a term. They’re not a taxonomy. They’re stickies. They’re kind of like what you’re talking about. But they’re just post meta, in WordPress core. The query to make them work is a little fun. But it works. And I think it is all about making it the best user experience that you can make it.

Keith:
I love this, because I was expecting you both to say the opposite. So I’m really intrigued, and that’s really good to hear that actually, in a way. Because I remember I was at a WordCamp years ago, I don’t know if, Mark, you were at the same one, but I listened to a talk and it was all about how much more performance taxonomies are for this kind of thing.

John:
They definitely are.

Keith:
Yeah. And I listened to that talk and from then to today, I’ve thought, “That’s the way to do it. That’s the right way to do it.” But you do have to make a few of those UI sacrifices.

John:
If you’re going to have a bunch of them. If you know you’re going to have 10, 30 or whatever, at some point the toggle is a little exhausting. If you can just drag it into the bucket that it’s supposed to be in and be done or whatever. There’s probably a better way to do that. But if all you’re doing is for a few, and you know you can manage it on and off, I think that whatever gets the job done the easiest and feels right makes the most sense.

Keith:
Elliot, does ACF have… Do you have a UI to be able to toggle a taxonomy? I thought you did.

Elliot:
I mean, not specifically toggle a taxonomy. There’s a taxonomy field and you can render that. You could render it as a checkbox input. So it’s like a basic toggle. Or there is a true/false field that you can make appear as an actual toggle switch.

Keith:
Yeah. I had a feeling we’d done it before with ACF, something like that. So you could potentially get the best of both worlds.

Elliot:
And do it as a term and then hide it as a custom field. Yeah. Totally.

Keith:
Exactly.

Elliot:
Absolutely. Yeah.

Keith:
Cool.

Elliot:
I mean, you could go to… No, I’m not going to get sidetracked.

Keith:
Go on. No. It’s okay. All right. Cool. Anything else to did to that, Mark? Because that’s a new stance that might change the way we approach things going forward.

Mark:
Absolutely. I was pleasantly surprised, like you said, I assumed the answer would be the opposite. But that’s good to know, actually. That you can do it either way. It depends what the numbers and stuff are. So that’s good.

Keith:
Yeah. That’s really cool. All right. Any more questions? I can’t remember.

John:
While you’re looking at questions, if we put a capstone on this idea, as the term relationships query has to join a few tables, and you could end up doing two or three queries to try and figure out, get the term, get the posts in the term, get meta for all that stuff. If it’s only one or two, you may be doing more work than you have to. So it is totally possible that meta might just be the easiest, fastest way to do it in the first place.

John:
So, I can hear Otto whispering in my ear that you should always use terms if you’re related posts. I can feel him tapping me on the shoulder saying, “What are you telling people? Always use terms for this.” But there is a tipping point where meta is totally fine.

Keith:
Cool, well, actually-

Elliot:
That’s actually a really good point. In this situation, yeah, if you were querying by the meta and then using the meta, that would be a simpler… I mean, that’s really nitty-gritty.

Keith:
So Carly asks there, John, that’s kind of segued nice into Carly’s question, so she says, “Is one better than the other for relating content?” So especially for performance, she’s saying.

John:
Yeah. Yeah. If what you’re doing is relationship things, then you want to use terms and you want to rely on taxonomies and terms and the term relationships database table to handle that for you. And it is not without its own faults either. Terms has come a long way, and has gotten a lot better, but it is not… I think with relationships you have one to many, you have many to many, and you have one to one, one to many, and many to many. So the relationships table’s kind of really only super good at the one to many side of this.

John:
For example, you can tag a post with a bunch of terms but you can’t easily tag users with those same terms, because the IDs collide. The post ID of one and a user ID of one. It doesn’t know how to differentiate which one is which. You can’t do that many to many kind of relationship here. So if you want to, and I have a plugin for this, to tag your users, I think it’s WP User Categories or User Groups maybe. I forget what it’s called. WP User Groups and it uses taxonomy terms, and it tags users. But it has to be a unique taxonomy to tag these users. You wouldn’t be able to share terms with posts and users at the same time.

John:
So, if you’re relating things, you want to use terms for sure. But it only gets you so far. The many to many is a thing you can’t do.

Keith:
All right. Cool. I’ve got a question. So we’ve had a challenge that’s come up a few times in a few of our builds where we create a custom taxonomy, but each of the terms within the taxonomy also needs its own post-like data for example. You might have a page with its own content and title and everything, but that is also a term, if this makes sense. Got a good example, actually. Recent site we built, it’s a video learning platform, and the videos are categorized into channels and playlists. And each channel and each playlist also needs to be its own post. It needs to have a lot of data content within it. We came up with a solution, but how are you going to work with this structure?

John:
I want to hear your solution.

Keith:
Yeah, we’ll come to that. Yeah. What do you think, guys? Have you got any ideas around creating a structure like that?

Elliot:
Yeah, I’ll take that. I’ve done some similar stuff in the past, actually, working on eCommerce websites. I think you hit the same issues with say a brand, because a brand you want as a taxonomy, and a brand might have full link content and images and a color scheme et cetera, and the term. It all comes down to the editing experience, again, at the end of the day. Because term has term meta, post has post meta, so technically they are equal, almost, except for the fact that the much more rich editing experience with the post versus the term as far as the admin is concerned. At the moment. That might change in the future.

Elliot:
How would you go about doing it? If you’ve got a term… What was the taxonomy again in your example?

Keith:
Playlists and channels.

Elliot:
Ah right, okay. So you’ve got a channel and then you’ve got, you know, Highrise Digital is the channel, and then you’ve also got that as a post. So I guess you’ve got the term and then you’re connecting the post to the term, that’s pretty normal. Then you can connect that term to videos, or other stuff. So you get the querying capability, and then you’ve got the post editing functionality. And there might be a few tweaks needed to the permalink setup so that you don’t accidentally end up on the term page. Or maybe you do want that, and then you pull from the post object, I’m not too sure.

Elliot:
You either want to end up on the post page and then use that slug to get the term, or end up on the term page and use that slug to get the post. One or the other, you’ll end up with the same result.

John:
It is totally one or the other. bbPress, you would think that a forum is a good example of something that should be a taxonomy, because you are relating topics to a forum and so it is easy to just dump it into a taxonomy, and they’re all related to a forum and it’s easy. But because forums could have thumbnails, they have their own permissions, and at the time you couldn’t do per-term capability checks in WordPress core. So we made forums a custom post type, and we used the post character column to link topics to their forum rather than using a taxonomy. And it was just because the tech inside of WordPress core at the time didn’t support the idea of expanding forums as a taxonomy with terms, and term meta wasn’t in WordPress core yet. So it just didn’t make sense to have it be that way.

John:
But I also have a plugin, more of a tool than a plugin, that’s on GitHub somewhere that’s called WP wp-term-meta-ui. So it is a drop in, extensible class that lets you say… It hooks into all the places, and to the WPI menu I, for the term page, quick edit, and the regular edit page, and everything else to just give you a place to plunk in some kind of form field. So you could use that, or something similar to it, to build out a term, to be a post, like Elliot’s saying, where if you don’t want to do all the extra guesswork of matching permalink slugs and building a page and everything else, that term meta gets you real far. You can just add fields to it and give yourself an icon, a thumbnail, whatever else, and just use term meta forever until it stops scaling or stops working the way you want it to.

John:
I think that both solutions are completely acceptable and will work the way that you want them to work, and no one needs to know that you did it, because it won’t really matter to them. And I think that’s what makes the story fascinating, is how you shoehorn it in there. How did you make this work in a way where people don’t really know what you’re doing behind the scenes? That’s why I’m excited to hear what you guys did.

Keith:
I was going to say, what… Over to you, Mark. How did you make it work?

Mark:
What I did is I created a post type for channel and playlist, and I also created a taxonomy for the same thing. When you create a post, it hooks into the save post, I can’t remember the hook, it creates a term which is exactly the same name, links the two together with metadata. If you delete the post it deletes the term. And then we don’t actually use the terms on the front end of the site at all. They don’t have a permalink. We just use the posts. So when you’re on the post page for a channel, I’m obviously just getting all the playlists that are associated and vice versa. You kind of get the idea. So that’s how we did it. But it’s interesting to hear there are a few methods you could use.

Elliot:
It’s good. It’s not uncommon.

John:
Shadow terms.

Mark:
Yeah, that’s essentially what they are. They’re just there for querying purposes almost so that the posts are assigned to those terms, and then we just query them based on that. But they don’t actually have a front end. We’re never using the permalink page of the term. We’re just using the post apps that we’ve created instead. So it works okay.

John:
I wouldn’t be surprised if something similar to that shows up in WordPress core at some point, supporting it. Because people do that all the time. It sounds totally wacky, but it’s completely… It’s a totally fine solution to this problem, and it’s very natural. It seems like it’s a little clunky, but once you know how WordPress works, it’s totally natural to do it that way.

Mark:
Yeah. And we just hid the terms and taxonomies from the back end as well. So the user never sees them. They don’t have a front end. They don’t have a back end. They’re just there to work for the developers. So it seems to work well.

Elliot:
It’s cool. Awesome.

Keith:
It’s there anything, with our heads on the chopping block here, is there anything we should be thinking about, worried about, with that approach? Is that potentially dangerous in a way that you guys can see?

Elliot:
No.

John:
No.

Elliot:
That’s the neatest way of doing it. But also, I just wanted to, we’re pretty much having a conversation about why there isn’t the ability to form post-to-post relationships inside of WordPress. Right? That’s what we’re ending up saying.

John:
Yeah.

Keith:
Yeah.

Elliot:
One other solution, I mean I think someone else before, what should we do? And we said, “Use terms.” Well, there’s some situations, like with bbPress, using the post parent column, I actually do a similar thing with field groups to field relationships because it’s such a simple relationship. It’s a one-way thing so you can totally use that column, so that’s a good way of doing it because you can actually connect together two different post types using that column. WordPress is totally happy doing that. Totally fine.

Elliot:
But if it’s actually a simple application, where querying ability isn’t of utmost importance, you can just use metadata completely. You could use ACF. So I did bring that up, but like a relationship field, a post object field, where you can select whatever posts you want, and you’ll just save them in the meta as a serialized array. Maybe it’s not the sexiest way of doing that, but you get the ability to select and edit on a basic, essentially comma delineated text value multiple posts. So you can create a relationship that way.

Elliot:
It is also possible to do some kind of reverse query using a like meta statement comparison type thing. Probably wouldn’t advise that for longevity of the project. But yeah, I’ve also done that lots of times, when you’ve got something simple, and you’ve just got artists and exhibitions and all you want to do is link them together and there’s no querying going on, you just want to show their names on the exhibition, then you can use metadata for that as well. I mean, at the end of the day it just comes down to the project. There’s no point developing a Ferrari if you want to go off road and there’s no point developing an off-roader if you want to race around the track. So it’s just totally up to you and the project.

John:
And if you’re going to end up writing… If you wrote your own custom database tables, you’d end up writing your own custom database queries, which means if you’re going to write your own custom database query and you’re not going to take it all the way and add caching to it, then you may as well just be querying the post table with a custom queries anyways, and not having to maintain all your own database tables. You’re still doing a custom query and you’re still doing all the work, and you’re not caching it anyhow. So just do the query and save yourself the time. It’s just about getting data in and data out, and it doesn’t have to be the sleekest, most performant way to get the job done, because you’re going to always go back and do that later. So yeah, I completely, totally agree.

Keith:
Cool, that’s good.

Mark:
Nice. Reassuring to know.

Keith:
I’ve got some [crosstalk 00:49:04] next door noise if anyone hears any drilling or something. I apologize. It’s always [inaudible 00:49:13] I’ve got a question from Ross Wintle. “Do either of you have abstract layers that you place on top of things like WP_Query and other WordPress classes to make your queries use naming that’s more domain specific? What do you recommend here?”

John:
That is a good question. I don’t, but I have thought about doing it multiple, multiple times. For BerlinDB, we did do that a little bit with the WP Meta Query class, because there’s a lot of cool logic that’s oddly flexible in that class where I originally did not want to rewrite all of that, so just extended it and built on top of it.

John:
Eventually, we did fork it, but mostly just for maintainability, not because we necessarily needed to. And then, in WordPress core, the W Date Query class is really, really cool, so if you do have to query anything by date columns, you can do crazy stuff with a WP Date Query class, where we’ve extended it and done other things. But not WP Query itself, or WP User Query, or WP Term Query. I’ve never… At that point I usually go, “Okay, I’m writing my own database table.” I just go, “I’m not going to extend to that, I’ll just do my own thing over here.” But it is a good question. It’s a good idea.

Elliot:
ACF dabbles a little bit in that area. It’s not a huge extension on the WP Query class, but there’s definitely a hook into the where clause so that I can search for fields with a field key of X, or ACF_field_key of X, and then this filter will take that and actually change that into while searching the post name column, because that’s where the field keys get stored. That’s actually something probably worth mentioning, if you’re going to use post types, then you may find yourself getting a bit creative as to where you’re putting the data within the confined columns that are already given to you. You’re probably going to find yourself just dumping a JSON serialized object, or a PHP serialized object, into the post content column, and that’s probably the best way to get the meat of all of your data in there.

Elliot:
But you’ve also got some other columns in there that are indexed to play with. So the post name and the post excerpt columns. I’m not sure, actually, if the post excerpt is indexed but the post name definitely is, and yeah, so ACF is using that for storing field keys as a really fast way to look up fields via their unique identifier. So [crosstalk 00:52:17]

John:
To Elliot’s point, dumping stuff in meta, it can get a little crazy because you can’t see it. You have to open your phpMyAdmin, or you have to have a plugin like Advanced Custom Fields, or rely on the custom fields metabox in WordPress to expose those things to you. It is kind of just a catchall for stuff. So as you start to grow an application and start putting things in post meta, you don’t have a way to really see what’s going on in there.

John:
Again, there’s a plugin on GitHub that’s half finished that kind of works that I have that’s called WP Meta Manager, and you could query the meta table, any meta table, just by itself with its own query, instead of querying it and get it to join on the post table. And then it gives you a list table view for just rows in the metadata table that you pick. So if you did need to browse your meta tables, there’s a pretty plugin out somewhere that mostly works. It doesn’t show you serialized stuff, which is the last thing I got to. I was like, “I really don’t want to handle the serialized recursion of exposing all this stuff on a UI. Doesn’t make any sense.” So I just gave up at that point. But it’s out there if somebody wants to. If you need to see what you’ve got in meta, then I’ve got something out there just to get you going.

Keith:
That sounds cool. We’re going to wrap up hopefully in about five minutes or so. I’ve got what last question that could be a bit long for five minutes. Again, another thing that we’ve come up against and struggled to deal with on multiple occasions is this idea of having an archive page as a child of something else. Example I can think of is you’ve got a post type called P, person, for example, and it’s team members. So you’ve got an archive of that called Our Team, that’s fine. But you want that archive to sit under the About Us page, for example, so the URL structure might be something like /about-us/ourteam/bob. Do you have any ideas on how to handle that kind of situation?

John:
I do, but you’re not going to like them. The custom permalinks API lets you do kind of anything, really truly. bbPress has a good example of some of that, if you want to see how I’ve tried to separate some of the logic out into somewhat understandable pieces. But you can trick WordPress into routing queries kind of anywhere you want to, if you register your permalink structures, and you are hooking into WordPress in the right area to catch that request and then pass the query arguments that you need to get the job done.

John:
So it is completely possible to do those kind of things, but the two things are, your permalink structure has to be ordered correctly or prioritized correctly, because it is just one big, ugly option that is top-down looked through to see what the match is going to end up being. So you want yours to match ahead of time so that you can catch it first, or bail out early if it’s not a match. But you can totally do it with custom permalink structures. You just have to know how to write the permastruct, which there’s not a ton of documentation on, because kind of an advanced use case. But bbPress has some code in there that you can nick if you need to, because we wrote some stuff like that in bbPress.

Keith:
That’s cool.

Mark:
I was going to say, something that I’ve struggled to find information on that, about dealing with permalinks and changing permalinks and stuff, you get some posts and they’re just half understanding of it, and some of them are really struggling. I don’t think anyone’s got a definitive. And maybe someone listening has got a definitive post that they might use, someone’s written, that’d be really useful to know because it’s something that I think could really be improved. Definitely.

John:
Yep, I agree. Yeah, there’s a bunch of things that eventually I’d like to do deep dives on, where permalink structures would just be fun to get into. Because there’s weird voodoo in their endpoint masks and stuff where you’re like, “What does that even mean? How do I use this? For what purpose? What is it for?” They are super useful once you know what their intent is.

Mark:
Cool. Good.

Keith:
Last one, then I don’t think there’s any more viewer questions. So my last question really was, at what level of complexity is WordPress no longer the right tool for the job? When are you just forcing it to do too much that is too much, too far outside its comfort zone? How do you know you’re getting to that point?

Mark:
Have you ever got to that point, where you think, “No, can’t use WordPress for this. This is ridiculous”?

John:
I mean, WordPress is, I’m the hammer and it’s my nail, right? I use WordPress for everything. So no. It reminds me a lot of the modding communities for video games where you can just take it so far. Modders just take the old Doom engine and make it do insane stuff, and you’re just like, “Why would you do that?” Well, because you can. Because it can. It supports it and there’s no limit to the direction that you can take your creativity with it. And I think filters and hooks and PHP is a relatively easy language to learn. You can start off very simply and you can scale and take it as far as you need to.

John:
Websites like Reddit are built behind the scenes with a very similar object meta pairing for everything. For users, for posts, for comments, for votes. It is an object and a meta table and it scales to trillions of events happening constantly. I know that in the world of things WordPress gets a bad rap and it’s a legacy application and all these things. And all those things, there’s merit to those opinions. They are valid opinions. But there’s not a whole lot you couldn’t do with it if you knew how to do it. It is a piano or a guitar, it is a paint brush, it is a canvas, it is a thing that you use to produce something cool. And if someone judges you for using WordPress to do that thing, then they are not your target audience and they’re caring about the wrong thing.

Keith:
That was good. Thank you for that, Elliot.

Elliot:
I like that. That’s good. It’s totally a canvas. I mean, I’ve been wondering why lately I’ve ended up using WordPress, and I think it’s because when I first stumbled onto its path I just felt so comfortable in PHP. I felt so comfortable working in WordPress, and I realized super quickly that I could build anything I wanted to, web development-wise, inside of WordPress. The fact that it gave me an admin section out of the box, there was actions, there was filters, there was a really clear life cycle through the admin pages and through the front end pages. You can really strip WordPress down. You can delete every file in a theme until it’s just the functions.php and a style.css, and you can delete all your plugins, and you can delete all the default hello world, and the default category, uncategorize, potentially, maybe.

Elliot:
If you really strip it all down, then you can make it do anything. You’ve got such amazing APIs built in. Obviously we’ve talked a bit about post types, but the WPDB class itself, or the instance of it, is awesome. You’ve got out of the box the ability to sanitize and safely create database tables safely, up to date, all the CRUD functions, in a way that multi-charactering, coding people. Which, the thing when you’re dealing with international foreign characters, these are the kind of things that can trip up websites or databases, but if you’re using the WPDB class with the prepare method, you’re fine. You’re safeguarded from all of that. And that’s a lot of logic just [inaudible 01:01:53] to get your app up and running.

Elliot:
You can create custom tables if you’d like, and you can even create a custom admin panel inside WordPress so easily, can’t you? And then you can have full control over the HTML of that page. Or potentially you want to build a react app inside of that, and work completely in JavaScript. Well, that’s more possible now than ever, and you can use WordPress as built-in REST API scaffolding there as well, if you want to build out something headless in that sense.

Elliot:
So yeah, I totally agree with John. There’s no limit. Your only limit is probably PHP, I would say, if your project is not able to work on a PHP-based system, then you need to be on Ruby, or [inaudible 01:02:44] or JavaScript’s own name type stuff, then that’s probably the only time to change. But saying that, I’m a lot of biased.

Keith:
That is, I think, a very, very nice way to end the show, with a lot of love for WordPress and its capabilities. And we’ve spoken for an hour about WordPress without mentioning Gutenberg, which is awesome too. Probably unusual these days. But no, it’s great. Guys, thoroughly, thoroughly enjoyed talking with you both, soaking up all your expertise and skills.

Mark:
It was awesome. Just before we go out, we did have a question as to, I know you were mentioning Easy Digital Downloads Version Three and someone did asked when that’s out, John, I wonder if you can mention that?

John:
We get that question a lot.

Mark:
Do we not know?

John:
I think we’ve got nine or 10 open pull requests now that we have to review, and then we’re going to start internally testing it on our own live sites. So sugarcalendar.com is probably going to be the first site that gets it because it has the least amount of data. So it is imminent. It is coming. It is actively getting worked on. It’s opensource. It’s on GitHub, so people could see the activity on the 3.0 page if people are curious about where it’s going on. If you really want to test 3.0, I think we probably could have shipped it a long time ago, frankly. But it is little quirks. There is a lot of data moving around there now. So it’s a valid question. If there is one thing that I am, it is usually the delayer of projects because we are taking on and solving big, massive problems. So I’m willing to take some of that responsibility for it taking a little bit longer than it probably should have. But it’s getting there.

Keith:
It’s coming. It’s coming. Well, we’ll look forward to that, because we use it as well, so that’ll be good to see. And I know you’ve got a version of ACF. Is it currently in beta, I think, the next version? Is that going to be out shortly?

Elliot:
We’re out of beta. It’s version 5.9, currently released tentative one. It’s hopefully going to be launched in a week from today if everything goes well.

Mark:
Excellent. Which it will, of course.

Elliot:
It’s pretty… Yeah. Super excited about this version. It’s good. I’m not going to mention the G-word, but there’s really cool stuff happening on that front with 5.9 and really merging PHP into native JSX, cool react stuff. There’s some really fun code going on in this version. Big stuff happening. Big stuff.

Keith:
Yeah, I’m looking forward to that for sure. That’s really exciting. Cool. So lastly from you guys, if people wanted to find out a bit more about each of you, where would they go to do that?

John:
JJJ.blog, where I don’t plug as much as I should, right? I guess we’re all guilty of that. Or Twitter and GitHub, @JJJ.

Keith:
Awesome. Elliot?

Elliot:
Yep. And for me, I just hide behind Advanced Custom Fields. So go visit advancedcustomfields.com, and on Twitter it’s just WP_ACF.

Keith:
Awesome, guys. Thank you very much, and thank you to everyone who watched the show today. Obviously people can get this on YouTube, it’ll stay up there evergreen. Hopefully it’ll be a really useful resource for people for a while to come. On that note, if you did enjoy the show, please subscribe. We’re putting out one of these every month and we’re just covering development and business topics for one man and small WordPress dev teams. So if that’s you and you like this kind of thing, then please subscribe and hopefully we’ll see you again soon. Thanks again everybody, and catch you next time.

Mark:
Thank you.

John:
Bye.

Elliot:
Bye.

Keith:
Bye.

About the author

Mark is the lead WordPress developer at Highrise Digital. He has been working with WordPress for over 15 years, way back to 2005. He focuses on back-end development, integrating the website build with WordPress so everything can be editable.