Adjust search system - return all nodes, but exclude a specific node type if a field is less than current date I am hoping to adjust the search system results. It works great, but i have an issue with some of the results. When a user searches "Training" I pull up all node's that have "Training" in their titles, bodies, tags, etc. 

I want to continue to pull up all node types. But if one of the node types (calendar_item) pops up, I got to check one of it's fields for a value, if it has a particular value then we exclude it. I found the best way of checking if the field has a value is by going to a different table (field_data_field_date) for a value in it's field (field_date_value2). To join that table to our current query we check entity_id field (in field_data_field_date) and compare it to the node.nid. Below is an example, the only issue is it actually excludes all other node types, so the only thing I bring up is calendar_items. Anyone know How to still bring up all nodes, but if it's this particular node type do the conditional check?

Please and thank you!

function mymodule_query_search_node_alter(QueryAlterableInterface &amp;$query) {
 $u_alias = $query-&gt;join('field_data_field_date', 'fd', 'n.nid = fd.entity_id'); // No idea what the field is called, assuming 'search.date'.
 $query-&gt;fields('fd', array('field_date_value2'));
 $query-&gt;condition("{fd}.field_date_value2", '2016-03-08T21:30:00', '&gt;=');


}  

EDIT #1: 
I have now adjusted the code to the following:

function mymodule_query_search_node_alter(QueryAlterableInterface &amp;$query) {
    $u_alias = $query-&gt;join('field_data_field_date', 'fd', 'n.nid = fd.entity_id');
    $query-&gt;fields('fd', array('field_date_value2'));
    // OR clause. The default is AND. To use or, you need to use:
    $db_or = db_or();
    // Add the conditions for the OR clause
    $db_or-&gt;condition("{$u_alias}.field_date_value2", '2016-03-08T21:30:00', '&gt;=');
    $db_or-&gt;isNull("{$u_alias}.field_date_value2");
    // Pass the Or clause to the query object.
    $query-&gt;condition($db_or);


}

If I replace 

$u_alias = $query-&gt;join('field_data_field_date', 'fd', 'n.nid = fd.entity_id');


with 

$u_alias = $query-&gt;leftJoin('field_data_field_date', 'fd', 'n.nid = fd.entity_id');


I get no results! But just a simple join gives me only calendar_items.
I think you need a different kind of SQL Join there, your (inner) join will produce a result set of only nodes that have an respective entry in the field_data_field_date table.  

I would try a left outer join and add an OR condition for field_date_value2 being NULL. An example:

$u_alias = $query-&gt;leftJoin('field_data_field_date', 'fd', 'n.nid = fd.entity_id'); 
$query-&gt;fields('fd', array('field_date_value2'));
$db_or = db_or();
$db_or-&gt;condition("{fd}.field_date_value2", '2016-03-08T21:30:00', '&gt;=');
$db_or-&gt;isNull("{fd}.field_date_value2");
$query-&gt;condition($db_or);


Cheers!