Add an Object-Relationship Table

[anon66243189]

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 )

---

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?

[anon66243189]

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.

[alvarofranz]

@james

Do you have a link to this page?

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, @anon66243189 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?

[anon66243189]

@alvarofranz

I don’t know if a Singleton Pattern should be the way to go

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 ClassicPress Object Relationships - 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

[anon66243189]

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

[alvarofranz]

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.

[james]

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.

@anon66243189

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.

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

@anon66243189

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)

@anon66243189

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.

@anon66243189

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.

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).

@anon66243189

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.

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.

@anon66243189

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.

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

@anon66243189

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

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.

[alvarofranz]

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, @anon66243189 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.

[joyously]

@james

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

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.

[james]

@joyously

That means that any object type will work, to create a taxonomy.

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.

[joyously]

The ID is stored in the database. The interpretation of it is added through code at load time, when registering post types and taxonomies.

[james]

If so, that’s a terrible design.

@joyously

Here is a plugin for user taxonomy ,

This plugin has 801 lines and it doesn’t even register any taxonomies, just “makes registering user taxonomies work” ^{[testing needed]}. And the code is extremely hacky. This is a good example of something that is outside the scope of taxonomies as currently implemented.

[joyously]

@james

If so, that’s a terrible design.

That’s how it evolved. The posts table contains the post_type column, but if it isn’t registered, it is ignored as if it doesn’t exist.
The term_taxonomy table contains the taxonomy name and description, but unless it is registered, it is ignored.
Edit: that description is for the term, and the taxonomy is the slug.

[tradesouthwest]

Could be that the reason WP never (wanted) to adopt the idea of universal object relations is that there are too many relational relationships and unless you use Redis and serious local storage callbacks for the db results, you will never get something as large as WP [CP] to load pages very well.

IMO: This is what CPT (Custom Post Types) are for. You need a custom setting to function 100%; you will only achieve 99% using a direct schema to the simplest query possible. If you need a user relation to your post_meta, make the CPT call the least number of joins and never go any deeper than two or three tables.

If you need a one-size-fits-all query (obj-engine) or you need a query anything relative to my CMS tool; you won’t use post_metas nor will you use WP existing tables. CPT and custom tables is your only truly effecient solution.

[timkaye]

This isn’t so much about an alternative to CPTs as an alternative to relying on custom taxonomies and term meta.

[tradesouthwest]

Yes, @timkaye the scope of this thread is about taxonomies, so this is why CPTs are really the sound answer. Using a WP post type and then expecting the World of WP or CP to build into the core something that would be robust and fool proof, would take way more time and effort than one making their own CPT with associated terms and taxomonies, unlimited to the relationships you could use.

Point/case would be building custom tax and terms via a database table which is designed to allow open end joins and meta queries to any degree needed to accomplish one’s task. You could build several tables, just for a single query if you needed to. Although less is better, as far as creating tables, as long as the CPT plugin was built to remove all tables on request, this would still beat trying to reinvent the wheel for something—that very few people would really ever need to use with WP/CP.

So basically that was my talking point on the above CPT used as a deep query model.

[timkaye]

No, that won’t (and doesn’t) do the job. This proposal is about, for example, enabling connections between taxonomies.

I have a current project where making one taxonomy a child of a different taxonomy, which would itself be a child of another taxonomy, would be the sensible way to go. And this is for user objects, not post objects. But WP/CP just can’t cope with that, so it currently has to be accomplished with parallel taxonomies instead, while linking them using term and user meta. Which is a bit of a nightmare really, because both the bi-directional relationships and the UIs that taxonomies create automatically have to be built (and ensure they will be kept in sync) manually when using meta data.

[timkaye]

The project I mentioned above has led to me creating a plugin that adds an object-relationship table to the database. The code, which is inspired by the above discussion between Greg Schoppe and Mike Schinkel (though they are not responsible for how I have implemented it), can be inspected at GitHub - KTS915/Object-Relationships: Enables relationships between objects to be stored in a dedicated database table.

Full details of how the plugin works are in the README file. As I say there, pull requests are welcome.

[timkaye]

The plugin at https://github.com/KTS915/Object-Relationships now also includes a meta table and related helper functions.

The approach is deliberately simple. Any CP/WP object can be connected to any other object by specifying an ID and object type for each.

All relationships are treated as bidirectional, because that’s what relationships are. The nature of each relationship is not specified. It could be stored as meta data if desired, or just be a matter of code. (I have found that, so long as I know which objects are related, I know what I want to do with them, so trying to specify in the database what relationships are appropriate seems unnecessary and even counter-productive.)

Similarly, there is no attempt to differentiate between one-to-one, one-to-many, and many-to-one relationships. Whatever is applicable in any given case is simply a matter of what is returned when the database is queried; in a one-to-many relationship, for example, more than one ID may be returned.