« Change $form_state in AJAX callback
404s on all pages except homepage »

What is the best way to add an OR condition to an existing query?

I want to be able to search additional user fields instead of just the standard username. For instance, I would like users to be able to search an attached field with the users’ Real Name. The user search query can be found in user_search_execute(). I figured I would use hook_query_alter() to alter the database query generated by this function.

The problem I am running into is that if I add anything like this:

$arguments = $query->getArguments();
$query->condition('n.field_name_value',$arguments[':db_condition_placeholder_0'], 'LIKE');
$query->leftJoin('field_data_field_name', 'n', 'users.uid = n.entity_id');

Then drupal adds a “AND” to the list of conditions (instead of an OR).

I have even tried adding something like this:

$arguments = $query->getArguments();
$query->condition(db_or()->
  condition('n.field_name_value',$arguments[':db_condition_placeholder_0'], 'LIKE'));
$query->leftJoin('field_data_field_name', 'n', 'users.uid = n.entity_id');

However, this produces the same results.

thanks!

– Answer –

  • 22 February 2012: Answer by Clive for What is the best way to add an OR condition to an existing query? -

    Adding new conditions to the query won't overwrite the existing ones, you need to reset the existing array of conditions, build up a new one and add it to the query:

    // Get the query args and then the search term
$args = $query->getArguments();
$search = array_shift($args);

// Get a reference to the existing query conditions.
$conditions =& $query->conditions();

// Reset the condition array. It needs a default #conjunction for which AND is fine
$conditions = array('#conjunction' => 'AND');

// Build up your condition
$condition = db_or()
  ->condition('users.name', $search, 'LIKE')
  ->condition('n.field_name_value', $search, 'LIKE');

// Add the new OR condition to the query
$query->condition($condition);

// Join the relevant table
$query->leftJoin('field_data_field_name', 'n', 'users.uid = n.entity_id');

    I've just tested that to work on a local installation.

    There is a slight caveat...for users with the 'administer users' permission the default is to also search through users' email addresses. You can cater for this by adding the following lines immediately before $query->condition($condition);:

    if (user_access('administer users')) {
  $condition->condition('mail', $search, 'LIKE');
}

    It might also be wise to test the bundle and entity_type columns in the joined field table in case you ever attach the same field to another entity. This way you won't get any false results:

    $join = "users.uid = n.entity_id AND n.entity_type = 'user' AND n.bundle = 'user'";
$query->leftJoin('field_data_field_name', 'n', $join);

    EDIT

    As per Berdir's comment, to maintain as much compatibility as possible with other modules that might also alter this query, you probably want to loop through the conditions, find the one(s) that you need to change and do so on an individual basis.

    The above example is pretty rudimentary and works on a 'vanilla' Drupal install, hopefully it'll get you pointed in the right direction.

  • 22 February 2012: Answer by Charlie S for What is the best way to add an OR condition to an existing query? -

    The db_or() method should provide what you need. Here's an example, where we get all "blog" nodes that are either authored by uid=1 or are published:

    $query = db_select('node', 'n');
$query->fields('n', array('nid'))
  ->condition('n.type', 'blog')
  ->condition(db_or()->condition('n.uid', 1)->condition('n.status', 1));
$result = $query->execute();

    Basically I think you just need to chain your multiple conditions along within the db_or() method.

    Here is some additional reading on the topic:

    Edit: Take a look at the conditions() method to retrieve the list of conditions (by reference) and manipulate them - possibly removing them completely and adding them to your db_or() method.

  • 20 February 2012: What is the best way to add an OR condition to an existing query? -

    I want to be able to search additional user fields instead of just the standard username. For instance, I would like users to be able to search an attached field with the users' Real Name. The user search query can be found in user_search_execute(). I figured I would use hook_query_alter() to alter the database query generated by this function.

    The problem I am running into is that if I add anything like this:

    $arguments = $query->getArguments();
$query->condition('n.field_name_value',$arguments[':db_condition_placeholder_0'], 'LIKE');
$query->leftJoin('field_data_field_name', 'n', 'users.uid = n.entity_id');

    Then drupal adds a "AND" to the list of conditions (instead of an OR).

    I have even tried adding something like this:

    $arguments = $query->getArguments();
$query->condition(db_or()->
  condition('n.field_name_value',$arguments[':db_condition_placeholder_0'], 'LIKE'));
$query->leftJoin('field_data_field_name', 'n', 'users.uid = n.entity_id');

    However, this produces the same results.

    thanks!

This entry was posted on Thursday, February 23rd, 2012 at 3:21 am and is filed under Drupal. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply