0

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.

2

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!

  • I think you are right, and thats what i was looking for. I cant test till tomorrow or late tonight. I will let you know. Thank you! – Daedalus Mar 8 '16 at 22:13
  • Thank you man, I think this is going to solve my issue but I am still having an issue. When I put leftJoin I get no results and if I just use join I get only calendar_items still, not all other nodes as well. I modified my question to show you the example. – Daedalus Mar 9 '16 at 13:39
0

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', '>=')
    );

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.