Adjust search system - return all nodes, but exclude a specific node type if a field is less than current date

Question

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 &$query) {
 $u_alias = $query->join('field_data_field_date', 'fd', 'n.nid = fd.entity_id'); // No idea what the field is called, assuming 'search.date'.
 $query->fields('fd', array('field_date_value2'));
 $query->condition("{fd}.field_date_value2", '2016-03-08T21:30:00', '>=');

}

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

function mymodule_query_search_node_alter(QueryAlterableInterface &$query) {
    $u_alias = $query->join('field_data_field_date', 'fd', 'n.nid = fd.entity_id');
    $query->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->condition("{$u_alias}.field_date_value2", '2016-03-08T21:30:00', '>=');
    $db_or->isNull("{$u_alias}.field_date_value2");
    // Pass the Or clause to the query object.
    $query->condition($db_or);

}

If I replace

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

with

$u_alias = $query->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.

Answer 1

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->leftJoin('field_data_field_date', 'fd', 'n.nid = fd.entity_id'); 
$query->fields('fd', array('field_date_value2'));
$db_or = db_or();
$db_or->condition("{fd}.field_date_value2", '2016-03-08T21:30:00', '>=');
$db_or->isNull("{fd}.field_date_value2");
$query->condition($db_or);

Cheers!

Answer 2

Stefanos Petrakis really helped here. He got me on the right track, but as I mentioned I was still dealing with issues. After reading this post I found the best solution was not creating a new db_or() and passing it through the $query's condition method, but instead:

$query->addJoin('LEFT','field_data_field_date', 'fd', 'n.nid = fd.entity_id'); 
$query->condition(
    db_or()->isNull("{fd}.field_date_value2")
    ->condition("{fd}.field_date_value2", '2016-05-20T08:00:00', '>=')
    );