I'm using the basic Drupal Search module (search.module) and I want to customize the SQL of the search. Note: I want to keep using the basic search module if possible because I've already customized much of its use with the tpl files and template.php; my clients also like the 'advanced search' feature.

There are two things I want to edit in the SQL:

1) Have the search return ONLY nodes with start dates (event_start_date) AFTER now OR items with no dates.

And

2) Return results in ascending order.

Pretty basic stuff. I know 'Views' is a possibility, but for the reasons outlined above I'd rather use the search API.

Ok, so here's what I've got working so far. I have implemented hook_query_alter in my template.php file to read as follows:

function themename_query_alter(QueryAlterableInterface $query) {
	if (get_class($query) == 'PagerDefault') {
		foreach ($query ->getTables() as $table) {
			if ($table['table'] == 'search_index') {
				$query->join('field_data_field_event_dates', 'fdfed', 'n.nid = fdfed.entity_id');
				$query->where('fdfed.field_event_dates_value >= CURRENT_DATE OR fdfed.field_event_dates_value IS NULL')
					->orderBy('fdfed.field_event_dates_value, n.title');
					
				break;
			}
		}
	}
}

That works pretty well in the sense that it doesn't return items that are before today's date...but for some reason the following part isn't returning items that have no date set:

$query->where('fdfed.field_event_dates_value >= CURRENT_DATE OR fdfed.field_event_dates_value IS NULL')

Anyone know why this SQL isn't working?

Comments

junestag’s picture

I'm happy to report I've solved this problem. I edited the syntax to read as follows:

$query->leftjoin('field_data_field_event_dates', 'fdfed', 'n.nid = fdfed.entity_id');
$query->where('fdfed.field_event_dates_value >= CURRENT_DATE OR fdfed.field_event_dates_value IS NULL')
					->orderBy('n.title');

The 'leftjoin' allows other nodes to be returned as well.