Add an Object-Relationship Table

The two things missing from WordPress to make it a stellar CMS is Fields functionality and Posts (and other objects) Relationships functionality.

I requested this 8 years ago for WordPress, and we still don’t have one (one of the reasons we can’t have nice things.)

Back then I called it posts_relationships but really it should be object_relationships to support many-to-many relationships for post-to-post, post-to-user, post-to-taxonomy, post-to-term, user-to-user, user-to-taxonomy, etc. etc.


Read-only archive: https://petitions.classicpress.net/posts/103/add-an-object-relationship-table

Author: Mike Schinkel

Vote count: 39

Status: open

Tags:

  • request-add-feature
  • cp-research-plugin

Comments

This would be indeed cool to have and it could be a core feature

But it’d mean adding new tables (please don’t do it with custom fields but properly, so it also alleviates performance)

Existing tools to look at who do it somewhat right are toolset and metabox and posts 2 posts by scribu (we use this one one the docs site)

It also needs an extensive api to catch all moments in the process and display the data efficiently and let users query it

So, this is actually a huge project. But one that is worth it and can distinguish cp, without actually being a backwards compatibility problem.

I can see we already have an experimental plugin for it… I’ll see if I can help with this, as at least from the user experience side I’ve years of usage experience with this kind of things (it’s one of my main areas of work, and there’s a huge lack of tools for users in wp since it’s a blogging tool and not a “data” manager per se)

4 Likes

I’m open to help with this too. Btw, this is the research plugin repo. Looks like a complex thing but as @smileBeda greatly stated:

3 Likes

First thing that comes from activating the plugin is:

The issue is within the install method. For some reason $wpdb->relationships is not defined at that point. The problem is here:

public function register_tables() {
    global $wpdb;
    $wpdb->tables[] = 'relationships';
}

Got some feedback on StackExchange:

There is no such thing as a registered table. Custom tables aren’t something that get registered, the pattern of adding a table to that array like in your code was never an official API, it was something 3rd party devs did to make it easier to retrieve the table name, nothing more, and not something that was ever supported by core. – Tom J Nowell

So I changed that part to let the table names be managed by the class itself, and not by $wpdb

public function register_tables() {
global $wpdb;
$this->table_relationships = $wpdb->prefix . 'relationships';
$this->table_relationshipmeta = $wpdb->prefix . 'relationshipmeta';
}

I tested what is there… create, update, delete relationships. I like how it’s structured, but there still a lot to do.

3 Likes

All this techy discussion sounds like Greek and Hebrew to me, but it nevertheless sounds good. And so… go for it!

1 Like

Actually I think we should go ahead and fix this so that calling $wpdb->relationships works correctly. If this is intended for merge into ClassicPress core then the table will be a property of $wpdb after it’s merged, and so the “preview” plugin should do things the same way.

Do you have a link to this page? I suspect the person who answered didn’t really look at the code, they just said “this isn’t supported”. But people do “unsupported” things all the time in WP/CP and mostly they have worked the same way for many years now.

2 Likes

I have a problem with the name of the proposed tables and classes.
Core already has a table called term_relationships, so either extend the taxonomy tables to accommodate this new thing or name it such that it is clearly separate.

1 Like

This table is for relating posts to terms. It can’t be extended cleanly to handle other kinds of relationships because the post ID that is being referenced is part of the primary key and we shouldn’t change the primary key of any tables on upgrade.

I don’t see a problem with term_relationships being for one specific kind of relationship that is already supported, and relationships being for the rest.

Please name it object_relationships if it’s going to be separate, because relationships by itself is too generic, making one wonder what the existing term_relationships is.

Edit: I never liked database stuff, but couldn’t the relationship be defined by two entries in one table? The proposed table has one entry containing two ids and then some muddle about what table the id comes from. It seems that could also be represented by two entries, although I’m not sure how you would key it. It seems that the proposed key is already a combination of the two objects.

As a thought experiment, if I wanted to represent real world relationships, I need many-to-many for Employer and Employees, or even for me and my 5 sisters, although someone else might have zero employees and my brother has 6 sisters (which are mostly the same as mine).
I’m not quite sure I understand how that would be represented in the proposed table.

The plugin threw an array of errors when I installed it, one of them the one mentioned by @alvarofranz but it is not the only - there is also some stuff about non-static methods called statically, and the code itself looks awful in the plugin. It does clearly not follow a tiny bit of the WPCS, for example.

Looking at the code I also see that it is not well organised. For example it creates database tables in the same class as it retrieves data, updates data, deletes data or creates relationships. This is not scalable.

The query aspect is totally inexistent and it is likely one of the biggest aspects of this project (it has a bare naked file but no code).

So, I believe it is not worth to use this plugin as a base - also because it actually seems to me (if I recall right) a more-or-less “cut and copy parts of” Posts 2 Posts by scribu.
And that plugin is old (while not broken, it is also not maintained anymore actively and was written many years ago).


What I am proposing is that we start with a (fresh) plan.

There are several things to be thought of (when Toolset implemented “real” relationships, for example, even thou a study and plan was conducted and prepared for almost 2 years before shipping, and we had years of experience on the subject already and what users do with it, when it was done it turned out several things could have be done better/differently/more complete).

Let’s use that experience.


To start with, we need to define types of relationship, because there is not just 1:1, 1:m and m:m but there is also distinct self relationships (relationships between the same object types, or also known as “references”, and the question of intermediary objects (IO), and how those can be used specially in m:m)

An additional big aspect is localisation, which is better thought of early, even if there is no actual translation feature in CP or its plugins, there might be one day and that needs to be incorporated early). Is a relationship automatically duplicated to its translation? What about objects that are usually not translated like Users? What about eventual IO meta data? etc.

The API aspect additionally, which can hit performance heavily, mostly in terms of retrieving related data, needs to be well thought thru and only knowing how The API should look and work will then define as well how things need to be implemented.

We need to decide where the actual relationship definitions are stored (as a Post Type? As an option? as Metadata?) and how the syntax to define the relationship should look like.

And not to forget (and the current plugin as well as Posts 2 Posts fully neglects that) is the UI both to define the relationships as well as then actually connect objects.
This needs to be straight forward for the end user - allowing to create relationships and then connect objects in an easy to use and highly performant UI (imagine your blog has 10k posts or perhaps 50K. A select with 10k posts to be possibly connected will crash your site, so that needs to be addressed early, with things like an ajax query in a select2 (maybe), where you show just 10 results and then 10 more or by type + search, for example).

Further, there is a powerful aspect of Relationships when using intermediary objects (IO) where you might want to attach meta data (custom fields in general) to that IO.

Then, you have the whole question of how to call related data (like, call a custom field that belongs to the parent of a given post) and how we solve that (either by first query said parent post and then its fields, or by directly providing a syntax that allows to call the parent, such as get_post_meta( $post->parent ) for example, which would work only in 1:1 or 1:m from the m side, but never in an m:m or from the 1 side in a 1:m because there might be an array of “parents”).

This is a huge project that will reach stalemate in no time if we do not plan it well ahead before even thinking about code, or naming conventions (which is something that of course must be defined early, definitely, but how we name the table will be the very smallest of all issues :slight_smile:)


I don’t think the petition here is the right place to discuss and define all that, and I don’t think starting off with code is (at this point in time) adequate.

What is the best tool for something like this? Google Docs?
So we can add comment or edit cap and once we have a well defined project we can start the actual coding process?

2 Likes

BTW registering a table like that is indeed weird
Nothing I ever saw requires you to register a table.

The way I have always seen is similar to this code (where $this->forms_table is earlier defined with name of the table including prefix etc, that’s all)
(Note the code below is from one of my sketching plugins so the variables are not generic)

private function database_install() {
		
		$charset_collate = $this->wpdb->get_charset_collate();

		$forms = "CREATE TABLE $this->forms_table (
			ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			form_title text NOT NULL,
			form_name varchar(200) NOT NULL,
			creation_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			form_html longtext NOT NULL,
			form_js longtext NOT NULL,
			form_css longtext NOT NULL,
			form_type varchar(20) DEFAULT 'post_form' NOT NULL,
			PRIMARY KEY  (ID)
		) $charset_collate;";

		$notifications = "CREATE TABLE $this->notifications_table (
			ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			notification_title text NOT NULL,
			notification_name varchar(200) NOT NULL,
			creation_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			notification_content longtext NOT NULL,
			PRIMARY KEY  (ID)
		) $charset_collate;";

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $forms );
		dbDelta( $notifications );

		add_option( $this->plugin_name .'_db_version', $tkt_forms_db_version );

}

public function activate() {

                $this->database_install();

}

I wouldn’t know why one would first register a db table name in $wpdb->tables[], if it seems fully redundant to do that.

1 Like

Sure, here it is.

Thanks for your amazing brainstorm, seems very logic, especially what you say about planing. I ended up adapting the table name exactly the same way you are showing in your last code. But as you say, the plugin isn’t even at a 20% of a working solution. And also, I don’t know if a Singleton Pattern should be the way to go. Singletons are called an Anti-Pattern by many. Could we do this differently, and make this potential future core part be an example of clean code? Is something else than the Singleton pattern even possible within the limits of the current core architecture?

It’s true that the current state is ugly, but I wouldn’t just throw everything away. The way the tables are structured is well thought out and that structure offers great potential.

But other than discussing concepts here…

I am in to focus on this project. It is inline with my vision of what CP should be. The most important thing is to get a well thought plan and then execute it step by step.

Starting with Google Docs seems the way to go. I can commit to work 2-3 hours/week on this, as long as I’m backed by someone who commits some time to this too (especially with architecture, because this is really a complex project, and I would say I am not the right person to make architectural decisions here because I am not a WP Master, @smileBeda seems to have a great brain for this).

Btw, @steamboatid, you recently joined the community and are a PHP Lover (as stated on your profile). Does this project catch your attention?

1 Like

Exactly my thoughts when I saw that.
Singleton is great for when it is needed. This is not one of those cases IMO (and it was likely used because of some parts of the code require it but certainly not the rest like retrieve, save and edit data)

I wouldn’t just throw everything away

not my wish, but it might be easier to just start fresh on this one. But sure, lets not throw this experience and then rely on another. Of course thought has been spent on that project too, and we shall value and re-use what we can and makes sense.


Anyway, 2hrs a week seem absolutely doable to me too, I will not be able to sustain this in December due to personal leave, but otherwise I even will try to pledge a bit more than that, lets say “as much I can, at least 2 hours”)

In range of these 2 hours for today left after above rather extensive comment :P…

  • I have reached out to my former colleague who literally wrote an essay on relationships when he did all that stuff in the plugin I mentioned, and I hope I will gain insight in his work. It would kickstart us immensely.
  • I have setup a GDOC here Projektvorschlag - Google Docs, commenting is on for everyone who has the link, editing will be shared to whom requests it.
    It is an early stage DOC. Anyone is free to contribute to this, of course.
    Let’s proceed necessary sculpting work there :smiley:
1 Like

Btw I just noticed there’s also a slack channel dedicated to this project #object-relationships

Might be good to join for usual quick discussions or so

1 Like

The document you created is a perfect starting point. Thank you for taking your time to do that. And definitely a must, to keep the whole idea organised.

But instead of directly commenting there, I think it would be better to open separate topics here on the forum, to allow discussions here and then come up with a conclusion, which then will be reflected on the document.

Using the document as a discussion platform will be a bit messy, and also it will be better if the document only contains specifc and clear descriptions. If there are too many words it will be harder to understand. Less words, clear decisions. Decisions that will have previously been discussed here on the forums, to keep track of all the “WHY THIS?”

I also propose adding a Use Cases section, to start by the most fundamental thing: what real problems are we trying to solve with this?

Knowing the real needs we can create a solution that is designed to solve those needs. Maybe some type of relationships are never used, maybe there is some side case that has not been considered, etc…

So I will open a topic specifcally for that. Here it is. And I think we should open specific topics for each discussion-topic: Single Pattern vs Alternative, API design, UI design, and so on. Of course, not open them all at once, it’s preferable to approach this step by step.

We can keep this topic and the Google Doc as an index.

It does not make sense to me to throw away the existing plugin. There is not much to it but it does look like it already has the basic pieces that will be needed to make this work. I would argue that actually, trying to start fresh every time there is new interest in a project like this is really what will cause it to stalemate.

These issues are easier to just fix than to rewrite the whole thing.

All of this is already handled in the existing plugin. There is a relationships table with existing indexes, and a relationshipmeta table that would be used to store metadata about each relationship (intermediate objects, as you call them).

I would suggest leaving this for after the initial API is fully done. This way the first version of the plugin is for developers and the UI could even be built separately if desired.

This is already done, it needs to be done in a dedicated table and the plugin already handles it reasonably.

The advantage of this (which is a pretty big one, IMO) is that other plugins which make use of this code can call $wpdb->relationships (or ->object_relationships) while it is being developed as a plugin, and then that syntax does not need to change after this is added to core.

1 Like

A balanced approach between throwing it away and reusing it as is, is to leave it there until the theoretical implementation has been figured out.

And also, @smileBeda didn’t actually want to throw everything away, it seems that what he means is to redo what is done, instead of just building upon it without looking back.

The most straight to the point thing we can do is figure out if that tables structure is really enough, also figure out what kind of design pattern should be implemented, and then start working on that.

Also, leaving the UI discussion off for the moment is okay too, so we focus on the API first, which is the fundamental part of this thing.

Since UI is independent from the API (one needs the other but, a UI can be decided after the API is ready, without having to modify the API).

Of course, an awesome UI will have to be created, since it is absolutely necessary to make this thing be useful. But it’s also true that the UI can come after the API, instead of at the same time.

That way we (the people who want to work on this) can join forces and focus on the same part at the same time.

After reading the old Codex page and an old tutorial by Justin Tadlock, I have to disagree with your statement. The existing relationship table

relates objects such as posts or links to a term_taxonomy_id from the term_taxonomy table.

That means that any object type will work, to create a taxonomy. The core functions are mostly handling posts, however. (Links were deprecated long ago.) Here is a plugin for user taxonomy, which states that it doesn’t add tables and uses the basic core functions.

Just to be clear, I’m not saying this solves everything needed for object relationships, but that the existing relationship table is not just for posts.
Taxonomies provide groupings of objects.
Object relationships are more about specific objects tied to other specific objects.

1 Like

It won’t actually work though, because there is only an object_id field and no “object type” field(s). That seems to basically limit its use to posts (and CPTs). I’m not sure if links are still widely used but this would break as soon as there was a post with the same ID as a link. Trying to include other object types would only make this worse.