We’ve been looking at how to create a simple CRM system in WordPress. In the last part of this series, we added code to our plugin which allowed us to display our Advanced Custom Fields in the WordPress List Table (WP_List_Table), and sort our data alphabetically by those new fields.
Today we’ll cover how to extend the search functionality to include the data stored in our Custom Fields.
Search Functionality
Each Post Type that has an Administration Interface comes with a search box:
By default, when a user searches for Post(s) in the WordPress Administration interface, WordPress will search the wp_posts table to find any partial content matches across the following fields:
- Title
- Content
- Excerpt
If we try to search for a partial phone number, we'll see that no results appear:
For our Contact Custom Post Type, this isn't very useful if we want to find a contact by phone number or email address!
Advanced Custom Fields stores its data in the wp_postmeta table, which WordPress doesn't search by default. We need to use two of WordPress's provided filters to allow WordPress's search functionality to also search Advanced Custom Fields data. These filters will:
- perform an SQL JOIN between the WordPress Post Meta table and the WordPress Posts table
- append an SQL WHERE clause to the WordPress Post Query to search our WordPress Posts Meta table
Performing an SQL JOIN
Let's start by adding the posts_join filter to our plugin class's construct to join the WordPress:
/**
* Constructor. Called when plugin is initialised
*/
function __construct() {
add_action( 'init', array( &$this, 'register_custom_post_type' ) );
add_action( 'plugins_loaded', array( &$this, 'acf_fields' ) );
add_filter( 'manage_edit-contact_columns', array( &$this, 'add_table_columns' ) );
add_action( 'manage_contact_posts_custom_column', array( &$this, 'output_table_columns_data' ), 10, 2 );
add_filter( 'manage_edit-contact_sortable_columns', array( &$this, 'define_sortable_table_columns' ) );
if ( is_admin() ) {
add_filter( 'request', array( &$this, 'orderby_sortable_table_columns' ) );
add_filter( 'posts_join', array ( &$this, 'search_meta_data_join' ) );
}
}
We also need to define our search_meta_data_join() function, which tells WordPress which table we want to join to the main WordPress Posts table:
/**
* Adds a join to the WordPress meta table for license key searches in the WordPress Administration
*
* @param string $join SQL JOIN statement
* @return string SQL JOIN statement
*/
function search_meta_data_join($join) {
global $wpdb;
// Only join the post meta table if we are performing a search
if ( empty ( get_query_var( 's' ) ) ) {
return $join;
}
// Only join the post meta table if we are on the Contacts Custom Post Type
if ( 'contact' != get_query_var( 'post_type' ) ) {
return $join;
}
// Join the post meta table
$join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";
return $join;
}
get_query_var() is a function which returns the appropriate query variable stored in the WP_Query class. WP_Query is a WordPress class which provides:
... information defining the current request... what type of query it's dealing with (possibly a category archive, dated archive, feed, or search), and fetches the requested posts. It retains a lot of information on the request, which can be pulled at a later date.
get_query_var() is the magic that lets us 'pull' that information. In this case, we check the query variable 's', telling us what search term (if any) the user has requested. We also use this same function to check which Post Type(s) the user is requesting—we only want to extend our search if the user is looking at the Contact Custom Post Type.
If these conditions are met, we join the wp_postmeta table to the main wp_posts table.
$wpdb is also used here, and it's a defined class which:
... contains a set of functions used to interact with a database. Its primary purpose is to provide an interface with the WordPress database, but can be used to communicate with any other appropriate database.
In short, $wpdb lets us access the MySQL database, get configuration settings and perform SQL queries.
In this case, we use $wpdb to get the names of the Post and Post Meta tables, because these can be modified by each WordPress installation. For example, one installation might set its table name prefix to wp_ (which is the default), whilst another installation might set it to my_awesome_site_. We can't hard code table names, as we can't guarantee they'll always be wp_posts and wp_postmeta, so we use $wpdb->posts and $wpdb->postmeta, which contain the actual table names specific to that WordPress installation.
Appending to the SQL WHERE Clause
With our SQL JOIN complete, we now need to tell WordPress to search the joined Post Meta table.
Go back to the plugin's __construct(), and add a new function to the posts_where filter:
/**
* Constructor. Called when plugin is initialised
*/
function __construct() {
add_action( 'init', array( &$this, 'register_custom_post_type' ) );
add_action( 'plugins_loaded', array( &$this, 'acf_fields' ) );
add_filter( 'manage_edit-contact_columns', array( &$this, 'add_table_columns' ) );
add_action( 'manage_contact_posts_custom_column', array( &$this, 'output_table_columns_data' ), 10, 2 );
add_filter( 'manage_edit-contact_sortable_columns', array( &$this, 'define_sortable_table_columns' ) );
if ( is_admin() ) {
add_filter( 'request', array( &$this, 'orderby_sortable_table_columns' ) );
add_filter( 'posts_join', array (&$this, 'search_meta_data_join' ) );
add_filter( 'posts_where', array( &$this, 'search_meta_data_where' ) );
}
}
We also need to define our search_meta_data_where() function, which tells WordPress to search our Post Meta data:
/**
* Adds a where clause to the WordPress meta table for license key searches in the WordPress Administration
*
* @param string $where SQL WHERE clause(s)
* @return string SQL WHERE clauses
*/
function search_meta_data_where($where) {
global $wpdb;
// Only join the post meta table if we are performing a search
if ( empty ( get_query_var( 's' ) ) ) {
return $where;
}
// Only join the post meta table if we are on the Contacts Custom Post Type
if ( 'contact' != get_query_var( 'post_type' ) ) {
return $where;
}
// Get the start of the query, which is ' AND ((', and the rest of the query
$startOfQuery = substr( $where, 0, 7 );
$restOfQuery = substr( $where ,7 );
// Inject our WHERE clause in between the start of the query and the rest of the query
$where = $startOfQuery .
"(" . $wpdb->postmeta . ".meta_value LIKE '%" . get_query_var( 's' ) . "%' OR " . $restOfQuery .
"GROUP BY " . $wpdb->posts . ".id";
// Return revised WHERE clause
return $where;
}
In the same way as we did in search_meta_data_join(), we again check that the WordPress Query is a search on the Contacts Custom Post Type. If it isn't, we return the $while clause without modification.
If we need to modify the $while clause, we do this by:
- getting the start of the WHERE clause:
' AND((' - getting the remainder of the WHERE clause
- injecting our WHERE clause to search the Post Meta table's
meta_valuecolumn for any instance of our search term - adding an OR condition to the end of our WHERE clause, appending the rest of the query to it
- grouping the results by the Post ID
We need to group the results because there will typically be more than one entry in the Post Meta table for a given Post ID. Because we set up a JOIN between the Posts and their Post Meta, if we didn't group the results, we'd get the same Post repeating in our table.
To check that our JOIN and WHERE clauses have worked, reload your Contacts Table, and try to search for one of your contacts by part of their phone number:
If it works, congratulations! You can now search by any Advanced Custom Fields you specify in your CRM system.
Up Next...
In the next article, we're going to restrict and hide WordPress Administration functionality and menu items that we don't need for our CRM.
Comments