Filtering Search Results On CCK Fields

Photo of Greg Harvey
Wed, 2009-06-03 15:01By greg

This is a quick HOWTO on altering the core Search module's database query in Drupal 5.x and 6.x. I think this will be different in 7.x, as there seem to be new, better hooks for manipulating queries, but we're not there yet.

For now, the hook you need is hook_db_rewrite_sql(). This allows you to add JOINs and WHERE clauses to existing SQL queries being generated by other Drupal modules.

Let's imagine for a moment that "published" and "unpublished" is not enough for us on the default 'story' content type. We have a requirement for several document states. So, we created a CCK field called "Status", named 'field_story_status', and gave it some values, say "approved", "in progress" and "pending".

We've used Views to manage lists of content, so we can filter on the CCK field to remove everything except the "approved" items. We've used the default views provided by modules like OG and Taxonomy to output them, so they can be filtered too. The only problem is our search results ignore our CCK status. They only react to the core Published setting under workflow.

So we need to add something to our Search database query to make it filter on our CCK field, but first let's put our hook in place and isolate the Search query from all the queries being executed:

/**
* Rewrite search query to search only show results where
* the "status" CCK field is "approved"
*/
function mymodule_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == ''
&& $primary_table == 'n'
&& $primary_field == 'nid'
&& empty($args)
) {

// we'll do something here in a moment
}
}
?>

What's going on there?? Jeff Eaton from Lullabot explains why quite nicely here: http://www.lullabot.com/articles/hiding-content-drupals-search-system

Modules that implement hook_db_rewrite_sql() receive a couple important pieces of information about each query. The most important is the 'primary table' parameter -- you don't want to add a SQL WHERE filter intended for nodes when the primary table is 'user', for example. Due to some curious code inside the node module, however, the query that's passed in is treated as empty. While that's a pretty big violation of Drupal's own coding standards, it makes it easy to intercept just the node search queries.

So, we first check to see whether the incoming query is empty, the table is 'n', and the primary field is 'nid'. If those conditions are matched, the rest is easy: we grab a list of node types that we want to hide from the search results and build a WHERE condition that hides them. That's it!

Ok, so we've isolated the core Search query and we know how that works. Now let's filter on a specific CCK field. We need to do two things - firstly JOIN the appropriate CCK table on to the query and secondly add a WHERE clause to do the filtering:

/**
* Rewrite search query to search only show results where
* the "status" CCK field is "approved"
*/
function mymodule_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == ''
&& $primary_table == 'n'
&& $primary_field == 'nid'
&& empty($args)
) {

$return['join'] = " LEFT JOIN {content_type_story} s ON $primary_table.nid = s.nid ";
$return['where'] = " (s.field_story_status_value = 'approved' AND s.vid = n.vid)";
return $return;
}
}
?>

We build an array of elements to return, which has two items in it - join and where. The join element is obvious - we're joining the CCK content type table where the node IDs match.

Now, I created a CCK field called 'field_story_status' so CCK created a column in the 'content_type_story' table to hold that data called 'field_story_status_value'.

All I need to do is make sure that value is 'approved' and, crucially, make sure we're dealing with the latest revision, otherwise if *any* of your revisions once had a status of 'approved' they will appear, even if they don't right now. That's what the s.vid = n.vid bit is doing.

Finally we need to return the array we built.

But there is a further problem here. This will now exclude any content types that *don't* have the CCK field, and we don't necessarily want that. So let's explicitly allow back in other content types we still want to search on without using our CCK filter:

/**
* Rewrite search query to search only show results where
* the "status" CCK field is "approved"
*/
function mymodule_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == ''
&& $primary_table == 'n'
&& $primary_field == 'nid'
&& empty($args)
) {

$return['join'] = " LEFT JOIN {content_type_story} s ON $primary_table.nid = s.nid ";
$return['where'] = " ((s.field_story_status_value = 'approved' AND s.vid = n.vid) OR (n.type = 'video' OR n.type = 'image'))";
return $return;
}
}
?>

Notice the additional OR (n.type = 'video' OR n.type = 'image') bit? This will prevent those content types from being affected by the filter, because they don't have the field but I still want them to appear in search results.

And that's that. You just filtered your search results on the value of a CCK field. Cool, huh?

IMPORTANT: You have more work to do if you want to deny access to a node based on a CCK field. This just deals with removing content from search, it doesn't prevent node access if someone knows the node ID.

Just one other thing, by way of a footnote. You might want to add some additional logic to unfilter the superuser or a specific role. Here is an example that allows user 1 (the Drupal superuser) and two roles (website administrator and editor) to view search results unfiltered by simply adding an extra && to the if statement:

/**
* Rewrite search query to search only show results where
* the "status" CCK field is "approved"
*/
function mymodule_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
if ($query == ''
&& $primary_table == 'n'
&& $primary_field == 'nid'
&& empty($args)
&& ($user->uid != 1 && !in_array('website administrator', $user->roles) && !in_array('editor', $user->roles))
) {

$return['join'] = " LEFT JOIN {content_type_story} s ON $primary_table.nid = s.nid ";
$return['where'] = " ((s.field_story_status_value = 'approved' AND s.vid = n.vid) OR (n.type = 'video' OR n.type = 'image'))";
return $return;
}
}
?>