[Plugin: Posts 2 Posts] How To Add Info to Connection

I was thinking that perhaps the taxonomy description would be a place to store that information without having to create new tables or fields. I could maybe serialize some information and put it in as part of the connection process. Does that sound like a good way to accomplish what I’m trying to do? The API might then be changed to have an optional filter based on the information stored in the description.

It would be really hackish, because there’s only one description field per term (i.e. post). You would be better of storing the data as custom fields on one side or the other.

For example, you could make the Team post type hierarchical. There would be a parent team post, where you store the permanent data, like founding date, description etc. Each parent post would have child posts which connect to players. Whenever a player comes or goes, a new child post would be created with the new team configuration.

Another way would be to have a third post type, dubbed “Connection” or something, which would act as a bridge between a team and a player.

Thanks for the advice. I’m actually going to end up using both of your suggestions for different parts of the site. I’m going to make my teams hierarchical like you said. I’m also keeping track of which players are wearing which shoes, so I’m going to create a player-shoe custom post type as the bridge between them. Thanks!

Now that I’m knee deep in the in-between post type, I’m running into a couple issues, mainly with scalability. Here are the three post types and how they are connected with P2P.

soccer-shoes <-> connection <-> soccer-players

If I’m on a soccer-shoe, I can call p2p_get_connected( $post_id, 'both', 'connections', 'objects' ); The potential issue is that I will then have to then loop through all the connections to that shoe, which could be a ton, then call p2p_get_connected( $loop_post_id, 'both', 'soccer-players', 'objects' ); on each of the objects in the array.

Would it be more efficient if I built a custom SQL query to pull out everything related?

Would it be more efficient if I built a custom SQL query to pull out everything related?

Yes, it would, but given the complicated schema of the taxonomy system, it would not be trivial to write.

I have to concede that a custom db table would be the best solution in this case. Something like this:

post_a int(20) NOT NULL
post_b int(20) NOT NULL
connection int(20) NOT NULL default 0
UNIQUE KEY (post_a, post_b, connection)

The ‘connection’ column would point to a post id that contains extra data about the connection. It’s the same idea as before, but with a better db layout.

Alright, I tried for hours before succeeding, but I eventually won. Here is a function that will return connected posts. Here are the caveats to this approach.

1. I had to edit all the p2p terms table entries to remove the ‘p’ prefix
2. I also then had to edit the p2p core.php file to stop the convert function from adding / looking for the ‘p’ prefix

This function will return all posts of a certain type given the starting post’s ID, the custom post type in the middle that we are connecting through, and the custom post type that we are looking for.

In my case, this is how it works.
soccer-shoes <-> connection <-> soccer-players

$related_objects = p2p_get_connection_via_connection( 30, 'connection', 'soccer-players' );

function p2p_get_connection_via_connection( $post_id, $post_type_b, $post_type_c )
{
	global $wpdb;

	$sql = "SELECT p.post_title, cp.post_title, ccp.post_title
	  FROM sr_terms t
		INNER JOIN sr_term_taxonomy tt ON t.term_id = tt.term_id
		INNER JOIN sr_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
		INNER JOIN sr_posts p ON tr.object_id = p.ID
		INNER JOIN sr_posts cp ON t.name = cp.ID
		INNER JOIN sr_term_relationships ctr ON cp.ID = ctr.object_id
		INNER JOIN sr_term_taxonomy ctt ON ctr.term_taxonomy_id = ctt.term_taxonomy_id
		INNER JOIN sr_terms ct ON ctt.term_id = ct.term_id
		INNER JOIN sr_posts ccp ON ct.name = ccp.ID
	  WHERE p.ID = $post_id
		AND cp.post_type = $post_type_b
		AND ccp.post_type = $post_type_c";

	$connected_posts = $wpdb->get_results( $sql );

	return $connected_posts;
}

I have a few questions for you.

• Is there a problem with not having the ‘p’ prefix?
• I only removed the ‘p’ prefix from the name column, not the slug column. Will that cause issues if they are different?
• What do you feel about including this in the core of P2P? Obviously some error checking should be added to my function above, but I think it makes sense.

In any case, thanks for your great plugin to work with.

After digging around a little more, I made a couple other changes. I tried changing the slug to match the name, but that didn’t work because there were conflicts with matching slugs. I didn’t clarify above, but the reason that I had to remove the ‘p’ prefix from the name column above was so that I could perform an INNER JOIN on it and the posts table.

Since the slugs weren’t changing, I changed the get_connected function in core.php to get terms by the name and not the slug.

FROM
$term = get_term_by( 'slug', reset( self::convert( 'term', $post_id ) ), self::TAX );
TO
$term = get_term_by( 'name', reset( self::convert( 'term', $post_id ) ), self::TAX );

Also, if you’re interested in testing what I did, here is the SQL code to remove the ‘p’ prefix from the name column.

UPDATE sr_terms t
INNER JOIN sr_term_taxonomy tt ON t.term_id = tt.term_id
SET t.name = SUBSTRING(t.name FROM 2)
WHERE tt.taxonomy = "p2p"

That’s a lot of inner joins! It’s precisely the reason I suggested a custom table. I will probably rewrite my plugin (again) with that schema.

Still, I admire your persistence. ??

Is there a problem with not having the ‘p’ prefix?

I added the ‘p’ prefix to avoid subtle errors: the taxonomy API acts differently when you pass it ints vs. strings.

That sounds great. A custom table would definitely improve performance. Any idea when you might get around to rewriting the plugin? I’m in the process of inputing potentially tens of thousands of links for my site, so having it set up would put my mind at ease.

Also, if I can make a suggestion on the rewrite, it would be great if there was a good way to edit the connection details right from that screen. With the custom table setup, it shouldn’t be necessary to ever have an in-between custom post type, depending on how you choose to store the connection.

With the custom table setup, it shouldn’t be necessary to ever have an in-between custom post type, depending on how you choose to store the connection.

Indeed. I was thinking of adding a p2p_meta table that could store arbitrary data about each connection.

Also, if I can make a suggestion on the rewrite, it would be great if there was a good way to edit the connection details right from that screen.

There will be no UI for editing connection details, since it’s arbitrary data, as I’ve mentioned above. However, I will make it easy enough to extend the existing metabox.

Any idea when you might get around to rewriting the plugin?

I’ve already started, but don’t know when I’ll finish.

I’m excited for the future of this plugin. I’m willing to help where needed. Adding a p2p_meta table is definitely worth doing.

However, I will make it easy enough to extend the existing metabox.

Being able to extend the box is great. I started writing some code to help make the extra cpt easier to make, but I had to hack into the admin.php file to do it.

Is there anything I can do to help with the further development of this plugin? The ability to link posts together is vital to my website, so this plugin is very important to me.

There’s a new development version (0.4-alpha4) available.

The best way to help is to test the hell out of it. Don’t forget to re-activate the plugin after updating.

Notes:

• the code to migrate the data from the taxonomy isn’t written yet
• most functions in the api.php file have an extra $data parameter. ??

You rock. Do I need to run an update like previous upgrades?

Hi Ploobers,

Would love if you would comment on this Trac ticket.

https://core.trac.www.ads-software.com/ticket/14513

I proposed the need for a table in WordPress core to address these kind of use-cases but several developers said that most people would not need it and thus called is an “edge case”. If you think it is not an edge case, maybe your voice would help.

-Mike