WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 3 years ago

Last modified 5 months ago

#14513 closed feature request (maybelater)

Time for a wp_post_relationships table?

Reported by: mikeschinkel Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.0.1
Component: Posts, Post Types Keywords:
Focuses: Cc:

Description

Now that we have custom post types the obvious next step for people using them is to start relating post types using parent-child and/or many-to-many relationships. I've implemented a taxonomy-mirrored-posts plugin but am starting to discover many of the problems that @prettyboymp has pointed out on wp-hackers and think it might be time we consider adding a new table to allow us to relate post and to allow us to build functionality around this table such as related post pickers, etc.

I'd like to offer a straw man proposal to start discussions of a potential wp_post_relationships table added to WordPress 3.1 with three fields: parent_id, post_id and term_taxonomy_id. This allows us to relate any two post records and optionally associate at taxonomy+term to classify the relationship (here's the SQL to create the table):

CREATE TABLE `wp_post_relationships` (
  `parent_id` bigint(20) unsigned NOT NULL,
  `post_id` bigint(20) unsigned NOT NULL,
  `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`parent_id`,`post_id`,`term_taxonomy_id`),
  KEY `term_taxonomy_id` (`term_taxonomy_id`),
  KEY `post_id` (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Looking forward to your feedback.

Change History (124)

comment:1 scribu4 years ago

You don't really need an extra table, as demonstrated by my plugin:

Posts 2 Posts

comment:2 follow-up: nacin4 years ago

Wondering if we can create a viable API for this increasingly used case by internally leveraging postmeta.

comment:3 in reply to: ↑ 2 Denis-de-Bernardy4 years ago

Replying to nacin:

Wondering if we can create a viable API for this increasingly used case by internally leveraging postmeta.

No chance, due to the lack of good indexes. The above table could be a first step to merging posts and taxonomies, however.

comment:4 scribu4 years ago

There's no point in creating the table if we don't provide UI and APIs to go with it. So the feature request should actually be "Enable many-to-many relationships between posts".

comment:5 Otto424 years ago

We already have many-to-many relationships between posts. You can relate a bunch of posts to other posts by simply giving them all the same term in a taxonomy.

I say put this on hold while the discussion happens on wp-hackers. Maybe we can come up with a better solution.

comment:6 follow-ups: jane4 years ago

This seems to be straightforward plugin territory to me. Fairly niche use, etc. I would support whatever is needed in terms of hooks to make it easy, but I can't see this as a real candidate for addition to core, given that the bulk of users would probably never need this. If I'm wrong, someone creating a plugin for it and showing wide adoption stats would convince me to reconsider.

comment:7 scribu4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from new to closed

Discussion will probably continue on wp-hackers.

comment:8 in reply to: ↑ 6 ; follow-up: mikeschinkel4 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

I guess I forgot to check "cc" because I didn't get any emails about discussion ticket. Even though it was closed I'm reopening it so I can respond.

Replying to scribu:

You don't really need an extra table, as demonstrated by my plugin:

Posts 2 Posts

Sure you can do it that way. But it's not a great, general purpose solution for reasons discussed on wp-hackers and below.

Replying to nacin:

Wondering if we can create a viable API for this increasingly used case by internally leveraging postmeta.

As Denis said, using postmeta would require either doing lookups on a 768 byte key field or storing the relation twice in two different meta fields and thus increasing likelyhood that one of them will get updated by a plugin but not the other and "corrupting" user data.

Replying to Denis-de-Bernardy:

The above table could be a first step to merging posts and taxonomies, however.

By "above table" I assume you mean the one proposed? If yes, +1. :)

Replying to scribu:

There's no point in creating the table if we don't provide UI and APIs to go with it. So the feature request should actually be "Enable many-to-many relationships between posts".

I completely agree. Sorry I didn't mention that explicitly; at the time it seemed obviously implied but now seems like not mentioning it was an oversight on my part.

Replying to Otto42:

We already have many-to-many relationships between posts. You can relate a bunch of posts to other posts by simply giving them all the same term in a taxonomy.

I say put this on hold while the discussion happens on wp-hackers. Maybe we can come up with a better solution.

We don't already have many-to-many relationships in a manner that reasonable people agree on the implementation. Taxonomy requires either to much overhead to do lookups and/or to many joins and it requires maintaining many records in a consistent state across many tables. Too many places where data can get changed a corrupt the data at the user level (and I'm referring to both post_title/name and post_name/slug.)

Replying to jane:

This seems to be straightforward plugin territory to me. Fairly niche use, etc. I would support whatever is needed in terms of hooks to make it easy, but I can't see this as a real candidate for addition to core, given that the bulk of users would probably never need this. If I'm wrong, someone creating a plugin for it and showing wide adoption stats would convince me to reconsider.

I think you may be unaware how widespread the need for post-to-post relationships really are. Yes for the blogging use-case most people are not going to use it (though I believe many of them would if the benefits were explained to them).

But when using WordPress as a CMS I would be hard pressed to envision a site that would not explicitly need post-to-post relationships. Let me give you an example. You blog a lot about WordCamps. You could add a WordCamp post type where you have city, dates, venue, photo of venue, etc as custom fields. In addition, you could add a post type of "Featured Presenters" to your blog which would include their photo, their bio, their session topic(s), their company, their twitter name, etc. These features presenters could be keynoters or others that are "notable" in a Wikipedia kind of way. Then you could relate all your featured presenters to each WordCamp and have a generic post relationships widget that displays upcoming WordCamps and their Featured Presenters. The widget would be plugin territory but the table that stores that data and the "Related Featured Presenters" metabox in the admin UI (aka "Related %post_types%") that allows you to relate the two would be generic and triggered by a "supports" arg of the register_post_type() function and they should IMO be in core.

Also your mention of offering hooks implies you may not understand the scenario. The hooks to support this are already in WordPress; I've already implemented a plugin to relate posts. What we need are not hooks but instead a table to store the data. My plugin could add its own table but I loathe writing plugins that add tables to WordPress because that means WordPress doesn't have what we need for common use-cases. And if I add a table it's almost certain nobody else will write functionality for my table but if it was added to core there is 100% certainty that many other people will (which is one reason I'm advocating for it.)

Of course Otto42 and scribu claim that the taxonomy system is sufficient but I and at least prettyboymp disagree. However I had to explain the use-case to Ott42 and (thus it's clear to me) he hasn't actually implemented any sites that use taxonomy for this use-case and so his advocacy for taxonomy hasn't been tested by the rigors of real-world experience. prettyboymp has deployed a large site and came to the conclusion that using taxonomy for related posts was not workable and I'm coming to the same conclusion on a large plugin for CMS use-cases I'm trying to finalize. scribu just yesterday changed his plugin to use taxonomy from postmeta so it seems he hasn't tested it "in the field" either.

Does anyone else thinks post-to-post relationships are fairly niche? Surely not scribu, he wrote a plugin to address the use-case. Nacin points out that it is an "increasingly used case." Denis seems to imply that he believes it would be a viable use case (true?)

Unless a reasonable number disagree the point of this debate is not whether it is "fairly niche" (it's not) but instead:

1.) Should a table to relate posts as well as basic supporting UI functionality be added to core? (I think so, prettyboymp thinks it's useful but that we should wait to include in core, and others may agree or disagree with including in core) and

2.) If it will be in core how should it be implemented? (Otto42 and scribu this taxonomy is fine, prettyboymp and I disagree.)

Postscript: Yes I'll be disappointed but otherwise I'll be fine to learn that the majority decide not to include the proposed table or anything similar in core. However I will be upset if the decision is made based on false assumptions.

comment:9 mikeschinkel4 years ago

  • Cc mikeschinkel@… added

comment:10 in reply to: ↑ 8 ; follow-ups: Denis-de-Bernardy4 years ago

Replying to mikeschinkel:

Does anyone else thinks post-to-post relationships are fairly niche? Surely not scribu, he wrote a plugin to address the use-case. Nacin points out that it is an "increasingly used case." Denis seems to imply that he believes it would be a viable use case (true?)

I certainly agree that using taxonomies is unrealistic -- things are then broken, slow, awkward, etc. Then again, any generic posts relationship table I can think of would end up looking like:

  • nodes (id, type)
  • node2node (parent, child, type)

To me, things ought to look like the above in WP, with terms and posts getting merged at some point.

In some respects, it's a horrible idea. But it's also the most generic and flexible. And considering the implicit assumption in WP that a DB is a gigantic array, I'd say we're heading in that direction anyway.

comment:11 in reply to: ↑ 6 Denis-de-Bernardy4 years ago

Replying to jane:

I can't see this as a real candidate for addition to core, given that the bulk of users would probably never need this. If I'm wrong, someone creating a plugin for it and showing wide adoption stats would convince me to reconsider.

Am I correct in assuming that this rigorous process led to introducing the capital P feature? :-)

comment:12 Otto424 years ago

I say to not close this, but to let it sit and stew for a while. I'm not convinced of a need for it, but I'm not convinced it's a bad idea either.

Let's focus on getting terms (and comments) integrated with posts first. As that happens, then it might just turn out that we really need this along the way.

comment:13 in reply to: ↑ 10 mikeschinkel4 years ago

Replying to Denis-de-Bernardy:

Replying to mikeschinkel:
I certainly agree that using taxonomies is unrealistic -- things are then broken, slow, awkward, etc. Then again, any generic posts relationship table I can think of would end up looking like:

  • nodes (id, type)
  • node2node (parent, child, type)

Definitely another consideration. I had hoped to others would propose alternate and possibly better implementations, as you just did. (I was actually surprised when some of the first response was "it's not needed.") So thanks.

Replying to Otto42:

Let's focus on getting terms (and comments) integrated with posts first. As that happens, then it might just turn out that we really need this along the way.

Yes, that sounds like a great plan. Thanks.

comment:14 in reply to: ↑ 10 scribu4 years ago

@Mike and @Denis:

You mention that using a taxonomy would be hard because you have to keep post slugs syncronized. I have tried that approach in the past and, indeed, data got corrupted pretty fast.

That's why I use the post ID instead. Since the ID never changes, keeping things consistent is a lot easier. You just have to delete the term when the post is deleted.

Take a look at the most recent version of my plugin to see what I mean:

http://plugins.trac.wordpress.org/browser/posts-to-posts/trunk/core.php?rev=271772

comment:15 follow-up: jane4 years ago

@Mike. We don't put everything in core. It's meant to be the must-have stuff, and extensible for specific use cases. A use case that is growing is not the same as one that is applicable to all. Get a good plugin out there that makes the table you want, see how many people adopt it. If it's an overwhelming number that would make the case. Adding tables that most of the 20 million+ users probably don't need doesn't make sense. When it gets to a point that it looks like a majority of them really do need that, as shown by plugin stats, that would be a good time to consider it.

comment:16 scribu4 years ago

  • Milestone set to Future Release

comment:17 Denis-de-Bernardy4 years ago

Replying to jane:

@Mike. Get a good plugin out there that makes the table you want, see how many people adopt it.

Doing so is somewhat nonsensical in this particular instance. No plugin dev in his right mind would use (let alone search for) a plugin that adds an extra table to WP in order to write his own plugin.

What he'll do instead is use dbDelta and create the table himself. (If only to spare his user base the hassle of needing to install two plugins in order to implement whatever he's coding.)

FWIW, I can vaguely picture two approaches for n-n post2post relationships in WP:

  • Storing the post_meta table. It won't scale much and, as a cursory look at support threads of scribu's plugin reveal, this results in poor reciprocal relationship management.
  • The taxonomy API, as in mapping each post to a term and then creating term2post relationships (a bit like we do with menus). But I've my doubts that the menus API can handle more than a hundred dozen menu items.

comment:18 in reply to: ↑ 15 ; follow-up: mikeschinkel4 years ago

Replying to scribu:

You mention that using a taxonomy would be hard because you have to keep post slugs syncronized. I have tried that approach in the past and, indeed, data got corrupted pretty fast.

That's why I use the post ID instead. Since the ID never changes, keeping things consistent is a lot easier. You just have to delete the term when the post is deleted.

Take a look at the most recent version of my plugin to see what I mean:

http://plugins.trac.wordpress.org/browser/posts-to-posts/trunk/core.php?rev=271772

That's what I'm already doing (using a post ID) as seen in the code I posted yesterday to gist for Lox. The problem I have been running into is ensuring that adding of a term doesn't fail because of a pre-existing slug that has been added for a post tag. Sure I can just add a "-2" or whatever, but the client is very anal (as most clients are, and just like me :) and they want the taxonomy slugs to match (if we are using taxonomy then we should be using the taxonomy system URLs, etc.) It's basically a rock and a hard place. So while I agree using taxonomy is useful in lieu of having a better method, it is non-optimal.

Using taxonomy for this requires telling your client "No, sorry, we just can't do that" and I'd rather have a system where I can always say "Yes, of course we can do that (it might cost more, but we can do it if it is important to you.)" BTW, I think this is the same reason prettyboymp became disenchanted with using the taxonomy system to relate posts.

Replying to jane:

@Mike. We don't put everything in core. It's meant to be the must-have stuff, and extensible for specific use cases. A use case that is growing is not the same as one that is applicable to all. Get a good plugin out there that makes the table you want, see how many people adopt it. If it's an overwhelming number that would make the case. Adding tables that most of the 20 million+ users probably don't need doesn't make sense. When it gets to a point that it looks like a majority of them really do need that, as shown by plugin stats, that would be a good time to consider it.

What Denis-de-Bernardy said. :)

Seriously, using that same argument we would not have gotten custom post types because most of the 20 million+ users don't (realize they) need them. (Precedent set. :) Future, post relationships are infrastructure not a feature. Features are plugin territory, infrastructure is not. Actually, this is simply rounding out missing functionality from the evolution of post types, not as a feature in-and-of itself. Honestly I think maybe you have an automatTic (pun intended?) Pavlovian response to any new features: First say "No, make it a plugin" Then actually understand the proposal. (I say that in good natured jest, please take it that way. :-)

By analogy, consider the idea for threaded comments. Mark Jaquith's suggestion of 3 years ago was for people to roll their and he points out that the field "comment_parent" was there for them to be able to do so. Had comment_parent not been there, his answer would have needed to be "Add your own table" which I think most of us agree is not something to encourage, right? This is much the same, we need a robust place to store post-to-post relationships. Adding a table may not be the right solution, but a solution is needed even though not all 20 million people will benefit.

Another analogy would be if I asked for the state to build a road between Macon and Columbus and you petitioned against it on the grounds that few people not traveling by car between the two cities. Clearly that's a chicken & egg problem; people don't travel by car between the two as frequently as say between Macon and Savannah or Macon and Valdosta because there simply isn't an easy way to get there. If there were a road there you can be certain more people would travel between the two. That doesn't mean it makes sense to build the road just that the justification for not doing so would have been faulty, same as in this case.

OTOH if WordPress had a built-in system to allow a plugin dev to "require" other plugins and then automatically and transparently have them downloaded and activated maybe I'd be able to agree with you but right now: "What Denis said."

Now, can we get on to discussing an optimal implementation for post relationship and also when (and even if) it makes sense to add to core? :)

Denis, another idea besides a specific table could be to simply add a parent_id to wp_term_taxonomy and overload it to allow for post relationships? I don't know if doing so will have unintended consequences with normal usage of the taxonomy system as the nav_menu_item post type did for plugin queries and such, but it might be manageable?

comment:19 follow-up: nacin4 years ago

The problem I have been running into is ensuring that adding of a
term doesn't fail because of a pre-existing slug that has been added for a
post tag.

While not exactly intuitive via the existing APIs, it's entirely possible to add a term_taxonomy relationship that uses the term object as a post tag, thus the same slug, etc. Terms (as defined by the schema) can exist in more than one taxonomy.

comment:20 in reply to: ↑ 19 mikeschinkel4 years ago

Replying to nacin:

The problem I have been running into is ensuring that adding of a
term doesn't fail because of a pre-existing slug that has been added for a
post tag.

While not exactly intuitive via the existing APIs, it's entirely possible to add a term_taxonomy relationship that uses the term object as a post tag, thus the same slug, etc. Terms (as defined by the schema) can exist in more than one taxonomy.

Yes, but then if I edit the mirrored post's title and automatically update the term's name and slug it modifies the term for the post tag too (the user requirement is, by definition that the two remain in-sync.) Clearly changing the post_tag would be an undesirable side effect, no?

I guess we could run code to create a new term and update all wp_term_taxonomy records that use the term as a tag but that's starting to feel more and more kludgy; what if that update fails for some reason? Yes we can code referential integrity in PHP but the more we do it in increasingly complex ways the more we are asking for corrupted user data and yes, disenchanted users.

comment:21 follow-up: matt4 years ago

Could you describe some use cases for this besides manually-curated related posts?

comment:22 scribu4 years ago

Replying to Denis-de-Bernardy:

  • Storing the post_meta table. It won't scale much and, as a cursory look at support threads of scribu's plugin reveal, this results in poor reciprocal relationship management.

"Cursory looks" very weak base for arguments make.

The issues you saw were either completely UI related or I was using my own API incorrectly.

comment:23 in reply to: ↑ 21 mikeschinkel4 years ago

Replying to matt:

Could you describe some use cases for this besides manually-curated related posts?

Absolutely, thanks for asking. Here is what I posted to wp-hackers on the same subject. Not everything mentioned would be a post of need to be related in the manner proposed but each example has at least two pairs of post types that need to be related and the first 3 are actual projects I've worked on since v3.0 went to beta:

1. A law firm website has attorneys, practice areas, articles, case studies, and events/presentations. Each attorney is in one or more practice areas. Each article and each case study can be attached to one or more attorneys and relevant to one or more practice areas. Each event/presentation can have one or more attorneys presenting and can be represented one one or more practice areas' calendars.


2.) A conference website can have sessions, sponsors, presenters, time slots and rooms. A session can have one or more presenters and be presented in one or more time slots and one room per time slot ,and a presenter can represent a sponsor or not.


3.) A website listing restaurant menus can have restaurants, locations, menus. A restaurant can have one or more locations and one or more menus (breakfast, lunch, dinner, catering, etc.) A menu can apply to all locations for a restaurant or only a specific location.


4.) A movie website could have movies and person-roles where a person-role could be an actor, producer, cinematographer, casting, film editing, costumer, set designer, composer, etc.


5.) A hospital website has doctors and departments. Each department can have multiple doctors and each doctor can work in multiple departments.


6.) A radio station website could have stations and disc jockey's where a station could have multiple disc jockeys and each disc jockey could appear on multiple stations (given how radio works these days.)

I can give you many other examples if you need it. I've advocated with many business owners about using WordPress for their websites (often at Meetups) and I've identified obvious use-case for post relationships for every single prospective business person I've spoken to. This functionality would fill in one of the remaining few missing pieces businesses need without having to hire a developer to use WordPress for their entire website instead of just for their blog. And I expect the businesses I've spoken with are far from unique.

(For developers who may fear adding this will reduce their ability to find clients to pay for enhancements consider that the more businesses that start to use WordPress the more of them will identify functionality specific to their business they need that does require a developer. So let's give them the low value generic things that are not "for free" and they'll have more money to spend on the higher value business specific things.)

Actually, if there were only two (2) things left that we could ever add to WordPress I'd vote for 1.) something to address post relationships (which I will contribute code if given the agreed direction) and 2.) improved URL routing (which I plan to work on in the near future.) Beyond that, WordPress is pretty much baked, IMO. :-)

comment:24 vteixeira4 years ago

I just want to add some considerations:

1- New proposed functions that would really get wordpress to the next level always face difficulties for getting into core.

2- Instead of just adding one table to the database some people prefer to hack with the way taxonomies and post types work... not the best approach.

3- Maybe it's time to think of Canonical plugins more seriously. If the functionality is not considered as a 'must have stuff' as Jane said, then this could be a canonical plugin, which could be implemented faster and without all the restrictions faced when something is trying to get into Core. And then developers could rely on that plugin and build custom functionality and other plugins on top of it.

4- I really think that right now all this Custom Post Type and Custom Taxonomy stuff are incomplete. I'm building two sites right now with not so complicated architecture and I'm having a hard time to get it to work the way I want with custom post types and taxonomies. We don't have different types of columns on the database to store different types of data, everything is stored on a text column - come on this is not a real world cms usage, we should have columns for at list some different data types - text, integer, double, datetime.

5- I also proposed some way to filter taxonomies by post types on the permalink structure (yes, it's not possible right now). See #14497 and #14502.

6- another thing missing that I didn't see anybody talking about yet is taxonomy relationship. Imagine I have a taxonomy called Country, another called States and then Cities. There's no way to relate them so one can depend on another. Not advanced stuff.

If wordpress is going on the CMS way, then it must be a complete and full featured cms without imposing restrictions to the site builder. People should not prefer wordpress over other cms just because of the better UI, but because of the features and flexibility also.

Just my thoughts...

comment:25 follow-up: westi4 years ago

Even with all the examples above I still don't see why the current taxonomy system can't be used (along with custom post types) to provide what you need.

If you want to store extra data about a particular term within a taxonomy the use a custom post type to store that information and have it linked to the term.

If you implement the ui correctly the user can't break these links i.e. you only give them one way to edit things.

Taking one of these examples:

A conference website can have sessions, sponsors, presenters, time slots and rooms. A session can have one or more presenters and be presented in one or more time slots and one room per time slot ,and a presenter can represent a sponsor or not.

Post types: Sessions, Sponsors, Presenters, ( maybe Rooms)
Taxonomies: Presenters (on Sessions), Sponsors (on Presenters).

You hide the creation ability of the two taxonomies from the users and just let them put the existing ones on to relevant posts using a selection ui on the post edit page.

They add new entries to the Taxonomies by creating custom posts of the matched types.

comment:26 in reply to: ↑ 25 ; follow-up: mikeschinkel4 years ago

Replying to westi:

Even with all the examples above I still don't see why the current taxonomy system can't be used (along with custom post types) to provide what you need.

Because it doesn't. With complete respect please try and implement a few real world sites that need this functionality and then let us know if you still feel the same, please.

If you want to store extra data about a particular term within a taxonomy the use a custom post type to store that information and have it linked to the term.

That's not a primary concern of the use-case this proposal addresses. That part was a tangent; the core here is needing a robust and performant way to related two different post records in a many-to-many scenario. Linking them through a system that requires 6 levels of indirection (post->term_relationship->term_taxonomy->term->term->term_taxonomy->term_relationship->post) rather than one level (post->post_relationship->post) is just asking for the downsides that come with too much complexity.

If you implement the ui correctly the user can't break these links i.e. you only give them one way to edit things.

I'm not so concerned about the users as I am about other plugins. You are suggesting we build a house of cards.

Taking one of these examples:

A conference website...

You hide the creation ability of the two taxonomies from the users and just let them put the existing ones on to relevant posts using a selection ui on the post edit page.

They add new entries to the Taxonomies by creating custom posts of the matched types.

Although the conference example is the least compelling of all them (hence it shouldn't surprise me you picked on it :) you are still ignoring the problem of posts getting renamed by users that have mirrored terms that are shared by other taxonomies. It's all a mess. What I struggle with is why I can see the problem yet (most) others cannot. Maybe I'm wrong, or maybe my specific experience in these use-cases has given me insight that your (potential lack of?) experience in these use-cases has not?

comment:27 in reply to: ↑ 26 scribu4 years ago

Replying to mikeschinkel:

you are still ignoring the problem of posts getting renamed by users that have mirrored terms that are shared by other taxonomies.

I thought we covered that. Why do you keep bringing up an argument that has already been addressed?

comment:28 scribu4 years ago

Oh, and yeah, I looked at your code on gist and it's NOT similar to what I'm doing.

comment:29 in reply to: ↑ 18 ; follow-up: Denis-de-Bernardy4 years ago

Replying to mikeschinkel:

Denis, another idea besides a specific table could be to simply add a parent_id to wp_term_taxonomy and overload it to allow for post relationships? I don't know if doing so will have unintended consequences with normal usage of the taxonomy system as the nav_menu_item post type did for plugin queries and such, but it might be manageable?

Well, if you "must" know, the reasoning behind the suggested table set above breaks down to my own research, years back, in semantic indexing algorithms. In essence, the most generic set of tables you can have amount to a nodes (id, parent, type) table and a meta table on the same. It breaks down to the fact that at some point you need to store data in a relationship (say, how much merchandise was moved from a premise to the next), and it follows that a relationship is a node in it's own right (or vice versa).

However, the same research (never published, sorry) also showed that it didn't scale well, and that two tables (plus the obvious meta) As described further up scaled a lot better for data designed to be displayed on a screen (in that particular case, the base case was a bookstore db for web use), because in web use cases it's rare to store data in a relationship (besides the relationship itself).

Fwiw, I've been periodically reinvestigating the topic as a hobby since then, until I came to a final (and dismissive, I'll confess) conclusion that it's a lot better to have multitudes of tables, with a generic API that knows which table to hit. At this point, however, you need to take into account the assumptions behind WP, ie that a db is a huge array that stores generic information to be processed by php, rather than some relational database management system that, low and behold, might contain an sql trigger.

It thus seems, to me, that things point towards using two tables, nodes and relationships (whatever they're called then, plus two meta tables), for storage at some point. Maybe in a year, maybe in five, but I trust we'll eventually get there. And when we get there, posts and terms will be merged at the same time or shortly after because it'll make little sense to some that we've duplicate code all over the place.

Based on the replies I don't sense the slightest interest for 3.1 though. Or 3.2, for that matter. Maybe this should get revisited when php6 returns a fatal error when checking for magic quotes? It might be a good time to break absolutely everything...

comment:30 in reply to: ↑ 29 mikeschinkel4 years ago

Replying to scribu:

Replying to mikeschinkel:

you are still ignoring the problem of posts getting renamed by users that have mirrored terms that are shared by other taxonomies.

I thought we covered that. Why do you keep bringing up an argument that has already been addressed?

Replying to scribu:

Oh, and yeah, I looked at your code on gist and it's NOT similar to what I'm doing.

The reason I kept bringing it up was that I misunderstood; I thought you were essentially the same as what I was doing. You are correct, I apologize, is it NOT similar.

However, not only is your solution NOT similar, it is worse. When you boil away all the other stuff the problem is that you are overloaded a 200 character text field to store a 20 byte key. It's actually a cool little hack, but it doesn't scale and shouldn't be recommended.

When I run EXPLAIN on this simply query:

mysql> EXPLAIN SELECT term_id,name,slug FROM wp_terms WHERE slug='p24';

This is what we get (I don't know why MySQL makes the key 602 bytes but while it's not as bad as trying to key off a longtext field, it is close):

*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : wp_terms
type         : const
possible_keys: slug
key          : slug
key_len      : 602
ref          : const
rows         : 1
Extra        : 
1 rows in set (0.00 sec)

Your code ultimately runs this (assuming it is not cached) which is basically the same thing:

SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'p2p' AND slug = 'p24' LIMIT 1

The above code is not so bad if there are <1000 or even <2500 records in wp_terms but if we have 10,000 records (movies) with 10 relationships a piece (actors) this absolutely does not scale. (And to make matters worse, your code current does not recognize the reciprocal relationship when one is encoded so a user would have to do both tasks: 1.) Add Actors to a Movie and 2.) Add Movies to an Actor which is crazy.

So I can't endorse your implementation except for use on a site that will never have more than a small number of posts.

Heck, if I were going to hack the taxonomy system and use it in a non-standard way that could cause confusion, I'd add an index for "term_group" and the store foreign key values for $post->ID there. Then you'd at least be to relate to 8 bytes keys with an index with an EXPLAIN that looks like this:

mysql> EXPLAIN SELECT term_id,name,slug FROM wp_terms WHERE term_group=24  \G
*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : wp_terms
type         : ref
possible_keys: term_group
key          : term_group
key_len      : 8
ref          : const
rows         : 1
Extra        : 
1 rows in set (0.01 sec)

Replying to Denis-de-Bernardy:

Replying to mikeschinkel:
Well, if you "must" know...

I've been interested in much the same but have never dug deep so I'll defer to your research and judgement. Frankly I'd have to really think about what you posted at length and right now I'm too tired. What's important to me on this ticket is 1.) that we recognize that post-to-post relations are a common and important use-case and 2.) that we don't accept a solution that is impractical and/or doesn't scale simply because nobody stood up to challenge it. Of course I'd like to support get added to core but frankly I knew that it might be an uphill battle so I'm prepared for that not to happen.

So in the spirit of #2 another option could be to add an indexed field to postmeta (maybe "meta_int") that can be used to start bigint values in (like post_ids) and/or anything else that can be serialized into a big int (i.e. time, etc.) If it is null then is should behave in a backward compatible manner. If it is not null then we could look to meta_value for the "type" (post, date, date/time, etc.) Just a thought...

comment:31 follow-up: nacin4 years ago

This ticket is not going to get anywhere if it's just a lot of back and forth, some of it not in the most appropriate tone, complete with questioning the intelligence of a core developer willing to share the current collective opinion of the core team on this. It's only going to cause the folks who you want to pay attention to this ticket to simply mute it.

I spent some time yesterday reading through old threads and tickets around the time the taxonomy schema was originally implemented in 2.2. wp-hackers was very effectively used as a discussion of use cases, demand, consensus-building on a schema and API, and the consideration of future situations. Tickets, on the other hand, are for code. In my opinion, a ticket like this -- a major feature request that can only get into core as a blessed task -- should not be created until it has been slated for a milestone at a scope meeting.

At the moment, this reads more like a wp-hackers discussion, and it belongs there. If any movement is going to happen on this, there needs to be solid consensus on a flexible system that can address clear, popular use cases. I see all sorts of use cases but I'm not convinced they can't be implemented with the existing schema; I've also seen, what, three or four conflicting ideas for how this should be implemented, to the point where the definitions of "this" are different depending on the developer and the use cases they were considering.

We need to see both demand and innovation, and preferably some consensus, before even considering this. Give it a year, I imagine that will evolve, but being ahead of one's time is not always a good thing.

comment:32 in reply to: ↑ 31 mikeschinkel4 years ago

Replying to nacin:

This ticket is not going to get anywhere if it's just a lot of back and forth, some of it not in the most appropriate tone, complete with questioning the intelligence of a core developer willing to share the current collective opinion of the core team on this. It's only going to cause the folks who you want to pay attention to this ticket to simply mute it.

Point taken.

But please reread my comments because I don't want to be misunderstood; I questioned only the level of familiarity related to the use cases. In no way did question intelligence and if that is how it came across I profusely apologize.

At the moment, this reads more like a wp-hackers discussion, and it belongs there. If any movement is going to happen on this, there needs to be solid consensus on a flexible system that can address clear, popular use cases. I see all sorts of use cases but I'm not convinced they can't be implemented with the existing schema; I've also seen, what, three or four conflicting ideas for how this should be implemented, to the point where the definitions of "this" are different depending on the developer and the use cases they were considering.

We need to see both demand and innovation, and preferably some consensus, before even considering this. Give it a year, I imagine that will evolve, but being ahead of one's time is not always a good thing.

That's all fair.

comment:33 mikeschinkel4 years ago

  • Resolution set to wontfix
  • Status changed from reopened to closed

Based on Andrew Nacin's comments I'm going to go ahead and close this ticket. My goal was to get people thinking about the need and applicable use-cases. I think that was accomplished.

comment:34 scribu4 years ago

The above code is not so bad if there are <1000 or even <2500 records in wp_terms but if we have 10,000 records (movies) with 10 relationships a piece (actors) this absolutely does not scale.

How is the number of relationships relevant? They are stored in the wp_term_relationships table.

Please run through the actual queries that happen when using my API before saying "this absolutely does not scale".

your code current does not recognize the reciprocal relationship when one is encoded so a user would have to do both tasks: 1.) Add Actors to a Movie and 2.) Add Movies to an Actor which is crazy.

Yes it does:

get_connected($id, 'from');

get_connected($id, 'to');

comment:35 scribu4 years ago

  • Milestone Future Release deleted

comment:36 follow-ups: mikeschinkel4 years ago

Just found this from the wp-hackers list from back in April:

On Apr 17, 2010, at 5:31 PM, scribu wrote:
> To link posts to other posts, I would use a posts2posts table, instead of
> the taxonomy system: fewer tables => better performance, easier to maintain
> consistency.

Makes me wonder if my suggestions are being challenged because they are my suggestions?

comment:37 in reply to: ↑ 36 ; follow-up: westi4 years ago

Replying to mikeschinkel:

Just found this from the wp-hackers list from back in April:

On Apr 17, 2010, at 5:31 PM, scribu wrote:
> To link posts to other posts, I would use a posts2posts table, instead of
> the taxonomy system: fewer tables => better performance, easier to maintain
> consistency.

Makes me wonder if my suggestions are being challenged because they are my suggestions?

Definitely not - at this point in time I don't see that core needs to add this feature.

Sometimes you will need more than core to develop a complex site - we are not trying to solve 100% of use cases only the more significant ~80%. The other 20% is where plugins sit in the eco-system so that you can make it your WordPress

comment:38 in reply to: ↑ 36 scribu4 years ago

Replying to mikeschinkel:

Makes me wonder if my suggestions are being challenged because they are my suggestions?

If you take a look at that thread, no one supported my idea either, not even you. Funny how opinions change, no?

comment:39 in reply to: ↑ 37 ; follow-up: Denis-de-Bernardy4 years ago

Replying to westi:

Sometimes you will need more than core to develop a complex site - we are not trying to solve 100% of use cases only the more significant ~80%. The other 20% is where plugins sit in the eco-system so that you can make it your WordPress

Best I'm aware, situations where node2node relationships are desirable include the vast majority of non-trivial/real-life business applications. The keyword here is business: people who pay to get a website that is of any use to them, as opposed to a corporate blog for PR purposes.

Even if you stick to a web content publishing workflow, one can think of use cases:

  • a post occasionally ought to have several authors
  • a tag or category ought to have multiple parents and children (social psychology belongs in psychology and in sociology, and perhaps in philosophy as well)
  • a post comparing products, movies etc ought to be related to several products, movies etc - each posts in their own right
  • a product, movie etc could have multiple reviews and comparisons

One of the more compelling examples Mike gave, IMO, was the one for the law firm. The little I've worked in that field would have had me laugh at the idea of using WP. You can bend reality to make things fit in taxonomies and meta tables, in order to fit real life situations into an arbitrary schema. But for such a setup you quickly end up ditching WP altogether, in favor of Drupal (barely better) or a php framework.

comment:40 in reply to: ↑ 39 ; follow-up: mikeschinkel4 years ago

Replying to Denis-de-Bernardy:

Replying to westi:

Sometimes you will need more than core to develop a complex site - we are not trying to solve 100% of use cases only the more significant ~80%. The other 20% is where plugins sit in the eco-system so that you can make it your WordPress

Best I'm aware, situations where node2node relationships are desirable include the vast majority of non-trivial/real-life business applications. The keyword here is business: people who pay to get a website that is of any use to them, as opposed to a corporate blog for PR purposes.

Exactly.

I wasn't going to continue, but since Denis did... I'd argue that (what in WordPress would be) post-to-post relationships are in the 90th percentile case; certainly not in the 20th percentile. If we accept the custom post types are WordPress' equivalent of a "relation" (relations are viewed as "tables"[1]) in the relational model for "large shared data banks" as written about by E. F. Codd in 1970[2] then this pattern is one of the most fundamental pattern in relational databases that exist.

The "post-to-post" relationship would be implemented simply with a table that allows the creation of a many-to-many connection to store as foreign keys[5] the primary keys[6] of posts, in our case $post->ID. This concept is fundamental to every relational database system ever built, not some "edge" use case.

Because of it's origins WordPress has nailed the blogging pattern and with that pattern has implemented many primary key to foreign key relationships:

  • $post->post_parent TO $post->ID
  • $term_relationships->object_id TO $post->ID
  • $comment->comment_post_ID TO $post->ID
  • $postmeta->post_id TO $post->ID
  • And several more.

Given WordPress implements the blogging pattern so well maybe it's not clear that the missing fundamental component required for implementing generic database-oriented business systems for arbitrary business use-cases is the post-to-post table aka the many-to-many[7] table. Alternately we could build out each new use-case pattern for which people want to use WordPress for much like the blogging use-case pattern was built out but why go that route (aside from it will never happen) when we can simply include the low-level many-to-many functionality and let people build it themselves?

So this is not a stretch nor some obscure need. This is as low a level of fundamental building block as you can get it WordPress and it's the last major one that's missing.

One of the more compelling examples Mike gave, IMO, was the one for the law firm. The little I've worked in that field would have had me laugh at the idea of using WP. You can bend reality to make things fit in taxonomies and meta tables, in order to fit real life situations into an arbitrary schema. But for such a setup you quickly end up ditching WP altogether, in favor of Drupal (barely better) or a php framework.

LOL! Denis, PLEASE don't tell my client that! :-) :-) :-) I told them that WordPress would be better than Drupal or a PHP framework and they are paying me by the hour to spend hundreds of hours building on that use-case! (I'm hoping that I'm not proven wrong here.)

FWIW I'm basically building a law firm website plugin that can be used to launch hundreds if not thousands of law firm websites which is the business my client is in; managing websites for larger law firms. My code will be replacing their former custom content management system.

And guess what? I'm developing some potentially really significant functionality I will be launching as plugins where the functionality is targeted at developers and themers not at end users. Said another way, because the company is paying me to build a business system on top of WordPress (a business system which needs robust post-to-post relationships, as almost all business systems built on WordPress will need) I am able to spend lots of time building functionality I can contribute to the community. (You've probably seen my recent helping of people on the wp-hackers list; that knowledge came from being commissioned to delve deep into WordPress and learn it's low-level functionality. Later contributions will be plugins after I can package and document them.)

But this isn't about me and my development; maybe my stuff is all crap; who knows? It's about all the other developers who could be commissioned to build business systems too. And because they would get to focus their time on developing for interesting business use-cases some of them would develop generic tool sets and contribute them back to the community thus growing the use-cases that WordPress can be used for with trivial effort.

But if the only use-case pattern WordPress addresses well is the blogging pattern then the majority of interesting generic toolsets that will be contributed back will continue to be by and large for blogging (I'm not saying WordPress can't currently address those interesting use-cases, it would just be a lot easier if there were a standard way to relate posts to posts.)

There are numerous ways to address this need without requiring the relationships be more complex than a single linking table; I'm just asking for one of them:

  • Add a wp_post_relationships table
  • Add a "parent_post" field to wp_postmeta
  • Overload wp_term_relationships by adding a "post_id" field.
  • And I am sure there are other ways to tackle this too.

FWIW.

-Mike

comment:41 follow-up: momo360modena4 years ago

  • Cc momo360modena added

Fun !

I just find this topic today.

With my team, we worked on a plugin with "cloned taxonomy" but after 3 months and lot's of problem with big DB, multisites, we decided to create a table for relation and we published the plugin :

http://wordpress.org/extend/plugins/relation-post-types/
The implementation is not completed (missing AJAX in admin, a complete integration with WP_Query) but it is already very functional.

comment:42 in reply to: ↑ 41 mikeschinkel4 years ago

Replying to momo360modena:

Fun !

I just find this topic today.

With my team, we worked on a plugin with "cloned taxonomy" but after 3 months and lot's of problem with big DB, multisites, we decided to create a table for relation and we published the plugin :

Thanks for commenting. Glad to get some additional validation.

http://wordpress.org/extend/plugins/relation-post-types/
The implementation is not completed (missing AJAX in admin, a complete integration with WP_Query) but it is already very functional.

I'll definitely check it out.

-Mike

comment:43 follow-up: scribu4 years ago

That's funny, because I'm also moving my Posts 2 Posts plugin away from taxonomies. Not because of instability, but because there was no clean way to add additional information per connection. So, the next version will have not one, but two custom tables: wp_p2p and wp_p2pmeta.

Due to the second table, you can connect the same two posts more than one time, but with different metadata.

Here's the discussion that led to this change:

http://wordpress.org/support/topic/plugin-posts-2-posts-how-to-add-info-to-connection

comment:44 in reply to: ↑ 43 mikeschinkel4 years ago

Replying to scribu:

That's funny, because I'm also moving my Posts 2 Posts plugin away from taxonomies. Not because of instability, but because there was no clean way to add additional information per connection. So, the next version will have not one, but two custom tables: wp_p2p and wp_p2pmeta.

Due to the second table, you can connect the same two posts more than one time, but with different metadata.

Here's the discussion that led to this change:

http://wordpress.org/support/topic/plugin-posts-2-posts-how-to-add-info-to-connection

Glad the use-cases becoming evident.

However, why do you really need two new tables? Wouldn't one work? The one table could link two posts with a third ID that links to a taxonomy_term or another post for context.

Of course you might need the second table for what effectively would be taxonomy meta but it seems that taxmeta was getting thumbs up for 3.1 so that could be moot?

comment:45 scribu4 years ago

However, why do you really need two new tables? Wouldn't one work? The one table could link two posts with a third ID that links to a taxonomy_term or another post for context.

Main reason is that it's meant to allow arbitrary data, like creation date, etc. Also, it's a lot easier to CRUD, using the metadata_*() functions.

comment:46 scribu4 years ago

Of course you might need the second table for what effectively would be taxonomy meta but it seems that taxmeta was getting thumbs up for 3.1 so that could be moot?

Not really, because to have metadata per connection, you would need a third column in the wp_term_relationships table.

comment:47 scribu4 years ago

I see what you meant now with linking to an additional post or term, but that would add one or more additional rows per connection.

comment:48 ploobers4 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

I know that there has been a lot said on the issue, and I don't think that there is much more to be said in terms of why this should exist. I don't see how this is separable from custom post types at all. If we are going to have potential dozens of custom post types (I have 15 in a site I am working on, with plans for the future implementation of another 15 or so), then linking them together in a one to one relationship is absolutely necessary.

Without custom post types, people had to hack around using categories to try and simulate custom post types. It was obviously a need that was recognized by the WP community and addressed in 3.0. Being able to store connections, and even more so, to be able to store specific details about each connection, makes custom post types a much more flexible solution.

In my personal case, I am developing a soccer equipment site. I have
-Brands
-Leagues
-Clubs
-Players
-Shoes
-Balls
etc.

Brands can sponsor specific leagues, clubs and/or players. That can change over time, so I need to store along with the connection start and end dates for the sponsorship. On the other hand, shoes are always going to be tied to one specific brand.

Clubs can belong to multiple leagues, for example the English Premier League and Champions League. Those associations also potentially change per year.

Players move around be between different clubs and brands, plus they also change which shoes they are wearing at any given point.

That's just a small view into the current site I am building, and though I realize that not everyone is building that type of site, I do believe that over 50% of people that use custom post types will want to connect them. When I am viewing a brand, I want to be able to show a list of all the different connected post types, whether they are sponsored leagues, clubs or players, and also their related equipment that they manufacture.

As scribu has shown with his Posts 2 Posts plugin, it is fairly easy to implement such a solution, and since most of the coding is done, almost trivial to integrate with the WordPress core. To ignore it or to write it off as an 'edge case' is simply short sighted.

comment:49 in reply to: ↑ 40 ploobers4 years ago

If we accept the custom post types are WordPress' equivalent of a "relation" (relations are viewed as "tables"[1]) in the relational model for "large shared data banks" as written about by E. F. Codd in 1970[2] then this pattern is one of the most fundamental pattern in relational databases that exist. This concept is fundamental to every relational database system ever built, not some "edge" use case.

I think that Mike phrased it great right here. Custom post types demand to be treated as a related entries in a relational database.

comment:50 husobj4 years ago

  • Cc ben@… added

comment:51 sorich874 years ago

  • Cc sorich87@… added

comment:52 mau4 years ago

  • Cc mau added

comment:53 wikichaves3 years ago

  • Cc wikichaves added

comment:54 follow-up: scribu3 years ago

  • Milestone set to Future Release

comment:55 in reply to: ↑ 54 ; follow-up: mikeschinkel3 years ago

Replying to scribu:

By setting to "Future Release" does that mean the team has decided to entertain the idea of incorporating some sort of table for post relationships?

Just curious, I'm about to implement into my own systems and would hate to do something future incompatible if it could be avoided.

comment:56 in reply to: ↑ 55 ; follow-up: nacin3 years ago

Replying to mikeschinkel:

By setting to "Future Release" does that mean the team has decided to entertain the idea of incorporating some sort of table for post relationships?

The team has declined to implement this in core for the time being, but all open tickets need a milestone. Sometimes feature requests are sent off to future release until it receives momentum and traction.

comment:57 in reply to: ↑ 56 mikeschinkel3 years ago

Replying to nacin:

The team has declined to implement this in core for the time being, but all open tickets need a milestone. Sometimes feature requests are sent off to future release until it receives momentum and traction.

Thanks for the clarity.

comment:58 goto103 years ago

  • Cc goto10 added

comment:59 cogmios3 years ago

  • Cc cogmios added

Too bad. I also would have liked this in a near edition of the WordPress Platform.
I think it is the main missing puzzle piece.

I also posted this in 29 may: http://groups.google.com/group/wp-hackers/browse_thread/thread/24b2e89995e448d4?pli=1

comment:60 axwax3 years ago

  • Cc axwax added

comment:61 johnnychen3 years ago

  • Cc johnnychen added

comment:62 jwriteclub3 years ago

  • Cc jwriteclub added

If we accept the custom post types are WordPress' equivalent of a "relation" (relations are viewed as "tables"[1]) in the relational model for "large shared data banks" as written about by E. F. Codd in 1970[2] then this pattern is one of the most fundamental pattern in relational databases that exist. This concept is fundamental to every relational database system ever built, not some "edge" use case.

mikeschinkel absolutely captures the case of custom posts. However it goes beyond this. Wordpress, to all intents and purposes, no longer uses SQL as a Relation Database Management System, but rather, uses SQL as a data store for a custom relational model which just so happens to use SQL as the storage layer. Sure, Wordpress makes use of indexes and other features to speed up access, but these could be used if Wordpress stored its data in a B-Tree, or any other structure.

Custom posts are Relations, and more importantly, so are the Relationships between Relations (at least in the relational model)*. The ability to arbitrarily link Relations is fundamental to the relational model.

Less abstractly there are a multitude of use cases which require this functionality and, more importantly, it is a significantly missing piece to Wordpress having a complete data model.

Implementation wise, I have built this out in my own sites by using an additional table, as relating on taxonomies simply imposes too great an overhead for large scale use.

  • Just a note: a Relation is a n-Tuple of data, whereas a Relationship is the logical connection between tuples, which is, itself, also a Relation.

comment:63 talus3 years ago

  • Cc mirepup@… added

comment:64 mpvanwinkle773 years ago

As someone who use frequently uses WordPress for mid-to-large scale CMS projects, I am very interested in seeing this added to core. A straight forward and scalable way to manage relationships is not trivial. Resources are scarce, including mental resources. In many cases you can use taxonomies but it's a convoluted solution with lots of opportunity for error. I frequently use Pods CMS when I need scalable relationships. But there are challenges there in terms of plugin development, as has been mentioned. Again, I understand the resistance to adding anything to core that isn't necessary. But WordPress Core Devs have to decide whether they want WP to be a full CMS capable of corporate level deployment or whether it will stick to being a Blogging/Publishing Platform. There are merits to both sides of this argument, but if WordPress is going to work toward being a full CMS it needs to work on relationships. Otherwise it should stop giving lip service to the CMS contingent.

comment:65 scribu3 years ago

Related: #15471

comment:66 mikeschinkel3 years ago

My latest iteration of this, ignoring the required SQL details looks like this:

CREATE TABLE wp_relationships (
  object_id bigint(20),
  related_id bigint(20),
  term_taxonomy_id bigint(20) 
 ) 

The idea is that it could be used to relate any other objects, i.e. post->post, post->user, user->post, etc. I'm working with someone else to improve code that would offer this functionality and plan to release as a plugin but would be happy to contribute to WordPress is there is interest.

comment:67 bainternet3 years ago

  • Cc bainternet added

comment:68 swissspidy3 years ago

  • Cc hello@… added

comment:69 azizur3 years ago

  • Cc azizur added

comment:70 cogmios3 years ago

FYI: The discussion reminds me of the Siebel S_PARTY model which uses S_PARTY, S_PARTY_REL and S_PARTY_PER for this. (the last two for the many to many relations e.g. from households to companies to contacts)

Maybe also handy to know that in Siebel each record holds a unique key over all tables.
And that this model has proven to work for most of the largest corporations.

many google links e.g. how they moved from the old model to the party model: http://download.oracle.com/docs/cd/B40099_02/books/ConfigApps/ConfigApps_TablesColumns15.html#wp1072693

So in comparison: S_PARTY holds the [ {the various {taxonomies, objects}}, users, capabilities, etc...] and each links to an extension table. Comparable to the POD model (i think i dont know it). This seems logical whether you call them PARTY or WP_THINGS.

S_PARTY_REL holds all the defined relations between party's such as custom taxonomy 1 and custom taxonomy 2 (comparable to Mike's wp_relationships but not fixed to defined types in the relation).

S_PARTY_PER hold the relations where extra access control is needed e.g. between users and capabilities.

Last edited 3 years ago by cogmios (previous) (diff)

comment:71 ciobi3 years ago

  • Cc ciobi added

comment:72 dwenaus3 years ago

  • Cc deryk@… added

I would love to see this happen in core. Or as a core plugin (whatever happened to those?)

Last edited 3 years ago by dwenaus (previous) (diff)

comment:73 tammyhart3 years ago

I agree with mpvanwinkle77, and mikeschinkel's usage example is a great one. This is something I really liked about wrking with ExpressionEngine. It's not worth switching, and switching to Pods is not worth losing post type and taxonomy flexibility, but it sure would be one less thing to have to hack around with.

comment:74 tammyhart3 years ago

  • Cc tammyhart added

comment:75 ptahdunbar3 years ago

  • Cc trac@… added

Definitely +1. I've built a CRM plugin which relates users to post types (and vis versa) amongst some other relations like taxonomy meta and went with the taxonomy syncing route. I've also tried using postmeta to no avail. These solutions don't scale, feels way too hacky for such a core feature ability and is prone to user error and data corruption, almost guaranteed. Thank god for daily backups :)

comment:76 nacin3 years ago

  • Milestone Future Release deleted
  • Resolution set to maybelater
  • Status changed from reopened to closed

If we ever decide to implement something along what's been proposed here, it'll be a major design decision and scoped as a task for a release. There's no reason to leave this ticket open before this potentially occurs, so I'm closing this as maybelater.

comment:77 follow-up: mikeschinkel3 years ago

As the original poster of this now-closed ticket, I'd like to add an update to those who have expressed a strong interest in seeing this come to fruition.

Let's Build an Object Relationships Plugin

I am working with a team of a few developers, funded by a client project, to build and launch a robust object relationships plugin. We'd like to see it be a proof-of-concept for or even the actual solution incorporated into future versions of WordPress. We are most focused on creating the most robust foundation and then enabling an API so that other plugins can build on what we are doing. To achieve our goals we need a community process that includes the best and brightest thinkers on the subject and we need people to believe in what we are doing and want to help us see it succeed.

We'll Use a Community Process

To create an object relationships plugin that meets the broadest needs for web professionals that are using WordPress we have launched a private community process to tackle this problem using some commercial community management solutions (i.e. solutions that charge us for each developer we give a login to, hence the limitation.)

Existing Solutions as Reference

As a reference, here are the solutions I've found so far for this use-case although each of them is operating as an island and not using a community process to speak of:

Interested? Then Participate

If you are interested in participating on this please email me at mikeschinkel@gmail.com and tell me why it interests you and how much time you can spend on discussions and maybe even coding over the next several weeks, maybe even up to a month.

Especially Theme Vendors and Existing Solutions

We are also very interested in theme vendors that would be willing to incorporate this functionality for use with their themes. And we are especially interested in existing solutions; if you are already offering a solution for this and would be willing to merge your efforts in to the initiative so as to potentially achieve a single unified approach that is compatible with all the other implementations that are attempting to solve the same problem. The value to existing solutions is lower-level compatibility allowing you to build higher-value user interface and use-case specific functionality.

Thanks in advance for considering.

-Mike

Last edited 3 years ago by mikeschinkel (previous) (diff)

comment:78 cogmios3 years ago

Great initiative Mike, IMHO the single big gap.I hope this turn into the prime solution.

comment:79 follow-up: bueltge3 years ago

  • Cc frank@… added

Great to see, the ticket is reopen - thanks Mike.
I think, the goal to build relationships from different post types is more and more important for customer. It makes easy to build different post types and realiced a correalation. Its better for more possibilities and also for structre on backend in WP and also! for the righst on the different post types.

I see this also as chance to get an better solution for create n2n relationship on post types and also on attachments, this is current not realy fine. Current i use/create a plugin for this, but i think, this is an part of core solutions.
http://wordpress.org/extend/plugins/post2media/

Maybe i build/have with a team a great plugin for multilanguage solutions on WP Multisite and we create the correalation about the postmeta. But its was easier with a core relationship and so more intuitiv and interesst for business users.

But is see this topic and solution as an core plugin, not an part of core. Its give also many users without this requirement and i think, WP is big enough. Maybe we can realized a core plugin to add the table and API for use this topic.

*Thanks for read my bad english.

comment:80 in reply to: ↑ 79 ; follow-up: mikeschinkel3 years ago

Replying to bueltge:

Great to see, the ticket is reopen - thanks Mike.

Thanks for the comments and interest Frank. I did not reopen this ticket, I simply invited those interested to convene for a separate community process to provide opinions on the architecture for such a plugin. We want to take our best shot at building it hence the desire to have those who have interest to weigh in, and hope that it will become successful enough to be a WordPress core plugin one day.

I have sent you a separate email with a login to the discussion forum which is currently private because we want to have shipping code before me make the project public. Please bring your ideas from comment #80 above with you to the other forum.

P.S. No apologies for your English needed; my German is much worse. :)

Last edited 3 years ago by mikeschinkel (previous) (diff)

comment:81 in reply to: ↑ 80 bueltge3 years ago

Replying to mikeschinkel:

Replying to bueltge:

Great to see, the ticket is reopen - thanks Mike.

Thanks for the comments and interest Frank. I did not reopen this ticket, I simply invited those interested to convene for a separate community process to provide opinions on the architecture for such a plugin ...

I have sent you a separate email ...

Yes, sorry - i have seen the status after i send the comment :( Thanks for email with the login-data to oyur forum for diskuss this topic.

comment:82 prettyboymp3 years ago

  • Cc mpretty@… added

comment:83 c3mdigital3 years ago

  • Cc chris@… added

comment:84 dougal3 years ago

  • Cc dougal@… added

"Wordpress, to all intents and purposes, no longer uses SQL as a Relation Database Management System, but rather, uses SQL as a data store for a custom relational model which just so happens to use SQL as the storage layer. Sure, Wordpress makes use of indexes and other features to speed up access, but these could be used if Wordpress stored its data in a B-Tree, or any other structure."

I think this is a pretty good point. We barely use the database as an RDBMS. Oh sure, we *do*, but the key here is that we care more about the relations at a 'context' level than at a 'data integrity' level. WordPress is all about context of data, if you think about it.

And it's not like there isn't precedent for introducing new tables or more complex features before. Look at our taxonomy system -- we could have added support for custom taxonomies in *much* simpler ways. It was originally going to just be a simple 'tags' system. But instead, we used a system which, while complex, is very flexible. And to the best of my recollection, the whole WordPress world was not screaming "we want custom taxonomies" at the time it was added. Most people were getting on fine using categories and postmeta (which is one of my proudest contributions to WP core, despite my in-hindsight mistake of the multiple-value implementation). But look at all the cool things that people have done with custom taxonomies since then!

This is pretty much the same. To me, many-to-many post relations (or object relations in a more general sense) are just going to be a natural evolution in WordPress. I really don't understand the reluctance here. Others have already pointed out use-cases where post-to-post relations make things much easier than trying to wrap things into taxonomy and/or post-meta. Those solutions only work for a sub-set of the proposed problems.

But if you wanted to try to build the next IMDB or AllMusic, you pretty much need many-to-many post relations. The music scenario can be even more complex than the movie database scenario. At least with movies, the list of actors in a film stays constant over time. With music, the list of musicians in a band can change not just from album-to-album, but from song-to-song (guest musicians, for example). Musicians can be members of more than one band at a time. Or be a musician for one project, and a producer for another. Or both at the same time on the same project. You probably *could* implement all that with taxomomy and postmeta, but you'd end up with a bunch of custom code that was only applicable to that one particular project. For the next project, you'd do a bunch of *similar* implementation, but just different enough that you'd end up rewriting a bunch of code. Plus, I bet you'd end up with certain relationship queries that would be very inefficient, because you'd have no good indexes for them. A good many-to-many relationship API in core would make this type of thing much easier, and much more portable between projects.

So, we don't know what the implementation should look like yet. It might be version 3.4 or later before it goes in, but I think that something like this is inevitable. We need to start thinking about implementation. One day we'll be looking back at some awesome theme, or plugin, or custom site built on this, and saying, "Remember when...?"

comment:85 rofflox3 years ago

  • Cc rwuensche@… added

comment:86 sabreuse3 years ago

  • Cc sabreuse added

comment:87 ramiy3 years ago

  • Cc r_a_m_i@… added

comment:88 in reply to: ↑ 77 ryno2673 years ago

  • Cc chuck@… added

To think a rewrite is niche and not needed is somewhat shortsighted... I've run into this a lot of very large db sites where it just doesn't seem feasible to build in WP w/ the high traffic, relations and ultimately the on-site search volume. I've resorted to custom codeignitor dev for those. I think it being implemented would help with future use cases of WP and it'll be more common in the future for more projects. #innovate :)

I have another similar project on the brink and they want to use WP... I'm worried w/ the couple million products and relations/taxonomies/cpt's etc that need to be built and searchable quickly w/o killing servers is something wp can still handle.

I'd love for an Open Relationships plugin...

comment:89 tomauger3 years ago

  • Cc tomaugerdotcom@… added

Though I see the ticket is currently closed, I will leave my vote and my cc: here because I do believe that over the years we have seen a fundamental shift in the primary use cases of the WordPress platform - I have not build a WP-based site in the last six months that didn't leverage some kind of custom post type with additional field metadata: the platform has definitely moved from pure-play blogging to CMS.

One does not have to look very far at all to find use cases for post-to-post relationships (think: products to product manufacturers, students to courses, participants to events, books to authors, reviews to products, etc. I'm not even trying here).

I find Mike's recommendation for an intersection table compelling from a performance perspective, and I agree with the recommendation to make this fully supported in Core.

In terms of the living and evolving entity that is WordPress, I believe that one needs to keep an eye on the horizon and realize that where content used to be king, these days it's the network or the relationship that has been getting all the attention. WordPress would do well to heed this trend and increase its ability to add richness to its content by creating more tightly-coupled relationships between its atomic elements.

comment:90 freshman663 years ago

  • Cc freshman66 added

comment:91 skippybosco3 years ago

  • Cc skippybosco added

comment:92 sirzooro2 years ago

  • Cc sirzooro added

comment:93 MHagemeister2 years ago

  • Cc MHagemeister added

comment:94 travisnorthcutt2 years ago

  • Cc travis@… added

comment:95 mercime2 years ago

  • Cc mercijavier@… added

comment:96 dwenaus2 years ago

Is this feature being contemplated for version 3.4? Even just the bare bones infrastructure, without any UI support would be great.

comment:97 toscho2 years ago

  • Cc info@… added

comment:98 andrewayala2 years ago

  • Cc andrewayala added

comment:99 jeremyfelt2 years ago

  • Cc jeremyfelt added

comment:100 johnjamesjacoby2 years ago

Curious if a core-worthy approach to post relationships was ever developed. I know Mike lobbied for help, and scribu has a good one out there. Any developments worth noting or linking to over the past year?

comment:101 nacin2 years ago

Curious if a core-worthy approach to post relationships was ever developed.

scribu's is pretty top-notch.

comment:102 dwenaus2 years ago

This thread started as a post-to-post relationship table, but developed into a more general object relationship table. Scribu's plugin is post-to-post only, but could perhaps be used as a foundation for object relationships, it's solid.

comment:103 scribu2 years ago

The Posts 2 Posts plugin also supports posts-to-users relationships now:

http://scribu.net/wordpress/posts-to-posts/p2p-1-1.html

comment:104 tar.gz2 years ago

  • Cc code@… added

comment:105 sc0ttkclark22 months ago

  • Cc lol@… added

I'd like to see more capabilities in this table for relating between more than posts, perhaps wp_relationships with:

CREATE TABLE wp_relationships (
    related_id bigint(20) AUTO_INCREMENT,
    object_id bigint(20),
    related_id bigint(20),
    object_type varchar(25),
    related_type varchar(25)
)
Last edited 22 months ago by sc0ttkclark (previous) (diff)

comment:106 scribu22 months ago

You have two related_id columns.

comment:107 sc0ttkclark22 months ago

Sorry, got distracted with a fire while editing.

CREATE TABLE wp_relationships (
    rel_id bigint(20) AUTO_INCREMENT,
    object_id bigint(20),
    related_id bigint(20),
    object_type varchar(25),
    related_type varchar(25)
)

The column names can be whatever, but you get the picture. Having a way to know what type each object they are is what I was after.

comment:108 scribu22 months ago

Or we could have a single column, 'relation_type', which would be something like 'posts-to-users' or 'pages-to-posts' etc.

You would have a function, register_relation_type() where you can define the types of objects. It offers greater flexibility, since you can define multiple relationship types between the same kinds of objects.

This is how it's done in the Posts 2 Posts plugin and it works really well.

comment:109 husobj22 months ago

I find Scribu's implementation of 'relation_type' in the Posts 2 Posts plugin very useful. It's definitely important to be able to create multiple relationship types between the same kinds of objects.

comment:110 follow-up: jeangalea20 months ago

I support adding the functionality of Scribu's plugin into Core. If you're building a plugin, you cannot really make use of the Posts2Posts plugin, if this was in Core it would be more straightforward.

Some examples of other people who are waiting for this to be added to core:
http://pippinsplugins.com/introduction-posts-2-posts-plugin
http://www.onextrapixel.com/2012/05/18/the-practical-guide-to-multiple-relationships-between-posts-in-wordpress/

comment:111 murtrd18 months ago

  • Cc murtrd added

comment:112 AliMH18 months ago

  • Cc ali.mohammadhosseini@… added

comment:113 emzo16 months ago

  • Cc wordpress@… added

comment:114 hhhsands15 months ago

  • Cc hhhsands added

comment:115 talus15 months ago

  • Cc mirepup@… removed

comment:116 marcosf14 months ago

  • Cc marcosf added

comment:117 MZAWeb12 months ago

  • Cc wordpress@… added

comment:118 elyobo12 months ago

  • Cc wp@… added

comment:119 in reply to: ↑ 110 Funkatronic12 months ago

Replying to jeangalea:

I support adding the functionality of Scribu's plugin into Core. If you're building a plugin, you cannot really make use of the Posts2Posts plugin, if this was in Core it would be more straightforward.

Some examples of other people who are waiting for this to be added to core:
http://pippinsplugins.com/introduction-posts-2-posts-plugin
http://www.onextrapixel.com/2012/05/18/the-practical-guide-to-multiple-relationships-between-posts-in-wordpress/

Actually, scribu has a version of Posts 2 Posts built specifically to be included with other plugins: https://github.com/AppThemes/wp-posts-to-posts-core. I'm working on a gallery plugin that does so.

comment:120 nacin12 months ago

If/when this ticket is blessed and re-opened, #10657 should be as well.

comment:121 ryanve10 months ago

Imagine an events site with 3 taxonomies where, when, and who such that posts could be displayed by location, date, or speaker. Post types for place, date, and person could also be used. Ideally one object could accomplish all capabilities, either by enabling post capabilities to taxos #10142 or enabling posts to be used to taxomize.

Version 0, edited 10 months ago by ryanve (next)

comment:122 boxcarpress6 months ago

If you were waiting for one more developer to say "Please add this to core," here I am! As it is, plugin development that incorporates p2p is a bit difficult.

comment:123 buffler6 months ago

  • Cc jeremy.buller@… added

comment:124 grantpalin5 months ago

  • Cc grantpalin added
Note: See TracTickets for help on using tickets.