0

I am using the Coauthors plugin:

https://wordpress.org/plugins/co-authors-plus/

I have written some SQL in order to grab posts from authors whose display name matches the search query when searching posts. It's actually working on a really small test blog, but it takes forever (literally so long that our host throws a 502) on a production blog.

There is only one part of this endeavor that I don't understand, which is writing an SQL query to do what I want:

  • "author" is a custom taxonomy from the coauthors plugin (see link above).
  • Each coauthor assigned to a post is a term from that taxonomy.
  • Match the search term against the term description for coauthors.
  • Get the posts that carry matching terms.

The key is that I need the results to be merged into the normal search results. This is not a widget or a template tag or a shortcode, this needs to be the main query for the search page. I'm achieving that via the posts_request filter (I don't have enough rep to paste a link to that filter).

Here's my SQL. Is there anything I can do to make this more performant?

http://pastebin.com/TZA7tqCv

<?php

    /**
     * Create an SQL statement for searching posts by coauthor name.
     *
     * @todo I'm having trouble getting $wpdb -> prepare to work, although I don't think it's necessary since I am sanitizing the data very strictly.
     * @todo I really have no idea if this is actually how you do a JOIN.
     *
     * @param string $s The search term.
     * @param string $matching_users A comma-sep list of user ID's.
     * @param int $limit The maximum number of results.
     * @param int $offset The number of results to skip, as per pagination.
     * @return string SQL for searching posts by coauthor name.
     */
    private function sql( $s, $matching_users, $limit, $offset ) {

            // Sanitize the search term and matching users, although replacing illegal chars with a wildcard.
            $s              = $this -> sanitize( $s, '_' );
            $matching_users = $this -> sanitize( $matching_users, '_' );

            // And then hit them with esc_sql because I'm paranoid.
            $s              = esc_sql( $s );
            $matching_users = esc_sql( $matching_users );

            // Sanitize the LIMIT making sure it's an int, defaulting to 10.
            $limit = absint( $limit );
            if( empty ( $limit ) ) { $limit = 10; }

            // Sanitize the the OFFSET, making sure it's an int, defaulting to 0.
            $offset = absint( $offset );
            if( empty ( $offset ) ) { $offset = 0; }

            // We're gonna need this to build our table names.
            global $wpdb;

            // Shorten the name of the psots table for easier reading.
            $p  = $wpdb -> posts;
            $tt = $wpdb -> term_taxonomy;
            $tr = $wpdb -> term_relationships;
            $t  = $wpdb -> terms;

            $sql = <<<SQL

                    /* We're selecting posts in order to fulfill the search query. */
                    SELECT

                    /* This will prevent multiple copies of the same post, though I don't totally understand why we'd otherwise be getting them. */
                    DISTINCT

                    /* Populates a value so you can determine how many rows you would have gotten without the LIMIT clause. */
                    SQL_CALC_FOUND_ROWS

                    /* We want every column from the posts table. */
                    $p.*

                    /* I want results from the posts table, but I have to invoke these other tables for when I am sort of using them later in WHERE clause.  */
                    FROM $p, $tr, $t, $tt

                    WHERE (

                        /* See if the search term matches the post title. */
                        (
                            $p.post_title LIKE '%$s%'

                        /* See if the search term matches the post content. */
                        ) OR (
                            $p.post_content LIKE '%$s%'

                        /* See if the post author is in the array of author ID's that I have from my script. */
                        ) OR (
                            $p.post_author IN ( $matching_users )

                        /* This is the part where I'm hazy. */
                        ) OR (

                            /* Search the TT table for terms from the 'author' taxonomy ...  */
                            (
                                $tt.taxonomy = 'author'

                            /* ... and whose term description matches the search term ...  */
                            ) AND (
                                $tt.description LIKE '%$s%'

                            /* ... and whose ID matches the ID in the terms table where ...  */
                            ) AND (
                                $tt.term_id = $t.term_id

                            /* ... the term_id matches the TR table where ...  */
                            ) AND (
                                $tr.term_taxonomy_id = $t.term_id

                            /* ... the object_id in the TR table matches the post ID ... */
                            ) AND (
                                $tr.object_id = $p.ID
                            )

                        /* End the part where I'm hazy. */
                        )

                    /* Limit results to the normal post types. */
                    ) AND $p.post_type IN (
                        'post', 'page'

                    /* Limit results to the normal post status. */
                    ) AND (
                        $p.post_status = 'publish'

                    /* WP Core does this and I have no idea why. */
                    ) AND (
                            1 = 1
                    )

                    /* Get results in reverse cron, like usual. */
                    ORDER BY $p.post_date DESC

                    /* Of course we only want our normal posts per page. */
                    LIMIT $limit

                    /* And we need to account for pagination. */
                    OFFSET $offset

SQL;

            return $sql;
    }
  • Please add your code to your question in an edit. Do not use third party services to host your code. Thank you :-) – Pieter Goosen Mar 11 '15 at 4:14
  • Oh, my apologies. Did not realize that. Seems like a big block to read in this format, but if you say so. – Scott Fennell Mar 11 '15 at 16:45
0

I ended up figuring this out by realizing that I could build a list of coauthor id's using the template tags that ship with the coauthors plugin. Next, I observed how WordPress core does joins to query by taxonomy, but var_dumping the posts_request on a term archive page. At that point, I just had to swap in my coauthor ID's and it works great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.