How to add a join to hook_query_alter

Question

I want to use hook_query_TAG_alter() for search system so I use mymodule_search_query_search_node_alter(QueryAlterableInterface $query).

What I want to essentially do is make sure that when a calendar_item, node type, is one of the results, to make sure that it's field field_date_value2 (this is the field that determines how long a calendar event or calendar item is valid, each calendar event starts at a certain date - field_date_value and ends at a particular date - field_date_value2) is greater than the current date.

So I imagine it would look something like:

function mymodule_search_query_search_node_alter(QueryAlterableInterface &$query) {
    $u_alias = $query->join('field_data_field_date', 'the_date');
    $query->fields('field_data_field_date', 'field_date_value2');
}

Now I am not adding a $query->condition(...); because this causes issues alone! I just need to know how to get in there and add the field to the query, then add a condition to that field's value!

Anyone able to steer me in the right direction? Please and thank you!

EDIT #1: You got me to where I needed to be! Thank you very much!! I have one more follow up question, if you do not mind.

The system search will find any node that has "Training" in it. This is great, but the nodes that are of type calendar_item, I want to make sure the aformentioned TO field (field_date_value2) has a date older than current. I have modified my code above.

function mymodule_search_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'));
     $query->condition("{fd}.field_date_value2", '2016-02-17T21:30:00', '>=');
}

My issue with my code above makes it so that it applies to all results, so I only grab calendar_items, I still want to grab everything, just if it is a calendar_item, to do the exclusion. I must need an outer Join?

Answer 1

Your query is a bit different compared to a regular query. A regular query is constructed like this (source):

$query = db_select('users', 'u');
$query->join('domain_editor', 'de', 'u.uid = de.uid');
$query->fields('u', array('name'))
  ->condition('u.name', '%' . db_like('john') . '%', 'LIKE')
  ->condition('de.domain_id', 25, '=')
  ->range(0, 20);
$result = $query->execute();

You have the base query:

$query = db_select('users', 'u');
$query = db_select($table, $alias);

Then the join:

$query->join('domain_editor', 'de', 'u.uid = de.uid');
$query->join($table, $alias, $condistion);

The fields to select:

$query->fields('u', array('name'))
$query->fields($table, array $fields)

And the conditions:

  ->condition('u.name', '%' . db_like('john') . '%', 'LIKE')
  ->condition($field, $value, $operator)

In your code it seems you did not take the table aliases into account. So you might want to try something like:

function mymodule_search_query_search_node_alter(QueryAlterableInterface &$query) {
    $u_alias = $query->join('field_data_field_date', 'fd', 'search.date = fd.field_date_value'); // No idea what the field is called, assuming 'search.date'.
    $query->fields('fd', array('field_date_value2'));
}

(You'll need to figure out what the name is of the field you want to join with, and substitute 'search.date' with that value.)