The List Query Object

In version 1.6, I introduced the use of an object to handle building the query that is used to generate a list of records. This gives the developer a way to change the query before it’s executed, similar to how the WordPress Query object can be altered before the query is executed.

All database queries for the List class are handled by a specialized class: PDb_List_Query. The purpose of the class is to make it easy to manage and alter a series of filters to be applied to the list display. Rather than having to use string manipulation on MySQL queries directly or assemble shortcode filter strings (which are the two other methods for altering the list query), you can easily add and remove filter statements. The class will handle parsing them into the correct query string.

There are several helper methods available:

  • PDb_List_Query::clear_field_filter ($field_name)
    this method clears all filters that have been placed on a particular field. This is often called before adding a set of filters that will be applied to the field.
  • PDb_List_Query::add_filter ($field_name, $operator, $search_term, $logic)
    this is used to add a filter to the object.
  • PDb_List_Query::get_field_filters($field_name)
    provides the array of PDb_List_Query_Filter objects that are currently acting on a particular field.
  • PDb_List_Query::is_search_result($set)
    true if the current query contains a search result. This means that filters were altered by the user in some way. The value of this property can also be changed by providing a boolean value.
  • PDb_List_Query::set_sort($fields, $sort)
    this allows the sorting of the query to be set. Each of the two arguments can be a comma-separated list of field names and sort directions (ASC or DESC).
  • PDb_List_Query::current_filter($key)
    this provides an overview of all the currently set filters as an array of values. The $key value can be used to show only part of the full set of return values.

Adding a Filter

The essential public method of the List Query class is add_filter($field_name, $operator, $search_term, $logic).

There are 4 arguments, the first three of which are necessary:

  • $field_name selects the column or field that the filter will act upon.
  • $operator defines how the value of the field will be compared to the search term.
  • $search_term is the string that is used in the comparison.
  • $logic indicates whether the filter is chained to the next filter with “AND” or “OR” logic.  $logic must be one of those two strings or omitted. It defaults to AND. It is ignored if there is no filter following the filter you are adding.

Operators

The operator determines how the term is compared to the value. A value is what is stored in the database for the field. A term is the search term that is being used.

  • = means the whole stored value must match the search term.
  • ~ means the search term must be in the field value somewhere.
  • ! means the search term must not be present in the field value. If “empty searches” are enabled and the search term is an empty string, this means that the field must have some value and can’t be empty.
  • != means the whole field value must not match the term
  • > and < define ranges of values, and as such, how they work depends on the type of data. If the data is numeric (such as dates) the comparison is made numerically. If the data is textual, the comparisons are made based on alphabetical order. These two modes are incompatible, so if you need to make quantitative comparisons, the data must be numeric, and the datatype defined for that field in the database must be numeric as well. You will not be able to include any non-numeric characters in the data.
  • >= or <= same as above, with the addition that it also matches if the term and the value are equal
  • WORD means the term must be found in the field value as a whole word, not part of a word.
  • NOT WORD means the term must not be found in the field value as a whole word.

How the Filter is Added

Filters added with this method are added to the end of the array of filters. The filters are compiled into the query in the order they were added to the array. Usually, you will clear the filters on a field before adding filters so you don’t create conflicts or have unwanted filters active. The filter “logic” is applied between the filter being added and the next one. If it is the last (or only) filter, the logic value is ignored.

Current Filters

The current_filters() method can be used to see what filters are currently set in the object. It returns an array of values:

Array ( 
  [sort_field] => date_updated 
  [sort_order] => DESC 
  [search_field] => 
  [search_term] => 
  [search_fields] => Array ( ) 
  [search_values] => Array ( ) 
)

“sort_field” and “sort_order” show the primary sort terms. “search_field” and “search_term” show the current search parameters. “search_fields” and “search_values” show the search parameters active when a multiple-field search or filter is active.

A Simple Example

The list query object must be modified using the ‘pdb-list_query_object’ hook. You can’t alter the query in a template because by the time the template is getting processed, the query has already  been executed.

For our example, this filter callback is placed in the theme functions file:

add_action('pdb-list_query_object', 'test_lqo_callback');

function test_lqo_callback($query) {
  $query->add_filter('first_name', 'LIKE', 'J*');
}

See how simple that is? There is no need to return anything because you’re altering the List Query Object directly. This example is pretty useless because it will affect all list displays. In a real application, you’d need to add whatever qualifying conditions you’d need to know whether to add the filter or not. For instance, if you had a page for a particular region, the list display on the page would only show results from that region:

add_action('pdb-list_query_object', 'test_lqo_callback');

function test_lqo_callback($query) {
if (is_page('washington')) {
    $query->clear_field_filter('state');
    $query->add_filter('state', '=', 'WA');
  }
}

Use in a Template

You cannot alter the query in a template; it’s too late by then, but you can check the state of the query in a template and use the results to alter the display. For instance to show a summary of the search values used, you can get the list of terms from the list query using the current_filter() method. For instance:

<?php $search_terms = $this->list_query->current_filter('search_terms') ?>
<h4>You searched for: <?php echo implode(', ', $search_terms) ?>.</h4>