[wp-hackers] Time for a wp_post_relationships table?

[Mike Schinkel]

On Aug 3, 2010, at 8:03 PM, scribu wrote:
> I think one of the reasons you're getting hung up on this is that you're
> trying to do it all in one giant query, instead of using the available APIs.

That's not at all the case, but I can see how you might think that.  My preference is to use the WordPress APIs whenever even remotely practical. I provided the SQL as proof-of-concept and to show exactly what I was proposing as opposed to discussing things in abstract and then have people (including me) misunderstand each other.

> Here's how I'm going to do it in my plugin:
> __________
> 
> // Given a post id, get a list of connected posts, either *to*, or
> *from*that post
> function get_connected( $post_id, $direction ) {
>        if ( 'to' == $direction )
>            return get_objects_in_term( self::to_term( $post_id ), 'p2p' );
>        else
>            return wp_get_object_terms( $post_id, 'p2p', 'fields=names' ) )
> );
> }
> 
> // Get all connected posts to a movie
> $connected_ids = get_connected( $movie_id, 'to' );
> 
> // Next, get only the actors
> $posts = get_posts( array( 'post_type' => 'actor', 'post__in' =>
> $connected_ids ) );
> __________
> 
> It's a little bit out of context ( 'p2p' is the name of the glue taxonomy ),
> but I hope you get the idea.

When I see that type of code like above, it saddens me.  That potentially runs two queries (assuming no cache hit) when it could run easily one. WordPress is starting to add more MySQL queries per page load and query drain performance; better not to propagate them it possible. And if the number of "connected records" is large, it could generate a huge query being sent to MySQL not the least of which if it has to loop through all the records once to build the array of connected ids and a second time loop through all the post records yet again, this time to collect up the posts. Things like that add up.

A more performant way to do that would be to use a posts_join and posts_where hook to add a "related_to" and/or "related_from" arguments that appends the correct join and where clauses reducing to a single query what should be a single query.  Better yet would be to add wp_post_relationships to WordPress core and add those parameters into query() itself.

-Mike