Custom Field Searching WordPress Using Sphinx

In my previous post on implementing a custom field search in WordPress, I showed how to modify the internal SQL LIKE search that is the default search engine of WordPress. There are other search engines that WordPress can make use of, most notably Sphinx Search.

Sphinx is a great open-source full-text search engine. I have used it several times to implement search functionality in content management systems. I didn't go into using Sphinx with WordPress initially because it is a lot more work. The default WordPress search is an SQL query performed by PHP. Sphinx is composed of an indexer, a search daemon, and a client library and the installation of the package is unavailable in shared-hosting environments.

Since more and more people are using WordPress on dedicated servers, I'm not surprised that questions on implementing custom field searching using Sphinx has arrisen. The good news is that it is very easy to implement! All you have to do is update the sphinx.conf to pull out the custom fields as fields in the sql_query of your sources.

In my previous example, I worked with the fields: bio, byline, kicker, and deck. I'll use these same fields again for continuity sake. I'm also using those fields because I have a site that uses these fields for custom search and I could test the changes needed for Sphinx.

In order to add each field, we have to do three things. Add the column to the select on the wp_post, add a left join to the select on the wp_post, and add a placeholder to the select on wp_comments.

Example Columns:
p.post_content as body, \ t.name as category, \ bio_meta.meta_value as bio, \ byline_meta.meta_value as byline, \ kicker_meta.meta_value as kicker, \ deck_meta.meta_value as deck, \ IF(p.post_type = 'post', 1, 0) as isPost, \ 0 as isComment, \
Example Joins:
inner join \ {wp_terms} t on (tt.term_id = t.term_id) \ left join \ {wp_postmeta} bio_meta on (p.ID = bio_meta.post_id and bio_meta.meta_key = 'bio') \ left join \ {wp_postmeta} byline_meta on (p.ID = byline_meta.post_id and byline_meta.meta_key = 'byline') \ left join \ {wp_postmeta} kicker_meta on (p.ID = kicker_meta.post_id and kicker_meta.meta_key = 'kicker') \ left join \ {wp_postmeta} deck_meta on (p.ID = deck_meta.post_id and deck_meta.meta_key = 'deck') \ where \
Example Placeholders:
c.comment_content as body, \ '' as category, \ '' as bio, \ '' as byline, \ '' as kicker, \ '' as deck, \ 0 as isPost, \
I have also attached the sphinx.conf distributed with the WordPress plugin so that you can see a more complete example of the implementation.

I hope everyone enjoys this as much as they did the last!