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?
<?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;
}
