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?

  • Better to open a separate question for that, so we keep the site clean and the answers relative to the question. But since I am the only answer I'll update the answer for this time ;) – Neograph734 Mar 8 '16 at 18:51
  • Oh shoot. You are right. I am sorry, I can make a new post. If you want to answer it there. – Daedalus Mar 8 '16 at 18:52
  • No sorry, better create a new question. I am not sure about how to do this and cannot test it at this time. So perhaps someone else can help you better. – Neograph734 Mar 8 '16 at 19:01
  • 1
    Ok, Neograph. You have been a great help either way! – Daedalus Mar 8 '16 at 19:01

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.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.