5

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!

  • For the code block in this question. Where did you add these code to override the default search? Was this in your own custommodule_search_execute() ? – pal4life Oct 13 '14 at 0:07
  • @pal4life since I wanted to modify the existing user_search_execute(), I implemented hook_query_alter() in my own module. The block of code would go in YOURCUSTOMMODULE_query_alter(). see api.drupal.org/api/drupal/modules!system!system.api.php/… for more details. – David Barratt Oct 14 '14 at 1:24
8
+50

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.

  • 3
    You probably want to loop over the conditions and just drop condition on the users.name field instead of clearing it completely. Maybe another module does add another condition or something like that. – Berdir Feb 22 '12 at 23:33
  • 1
    @Berdir That's a good point I'll add some more to the answer – Clive Feb 22 '12 at 23:35
  • thanks for this, thought I'd add an example I derived from this code that I used in conjunction with the LinkIt module: drupal.org/node/2287703#comment-12167429. – cdmo Jul 13 '17 at 18:48
2

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.

  • 1
    I don't think you completely understood the question. the question is how to modify an existing query, not a new one. You cannot call db_select the $query object provided by hook_query_alter(). At that point the $query object is already a structured with an "OR" statement already used. – David Barratt Feb 22 '12 at 22:54
  • My apologies. The 2nd link I posted details some clever usage of db_or() that may help, but still for you to alter the existing conditions (which we are not aware of at this moment) take a look at the conditions() method that I linked to above. – Charlie Schliesser Feb 22 '12 at 23:08
  • 1
    Yes, this should work combined with a $conditions = &$query->conditions() where you can either directly replace the existing simple condition with a db_or() object (try with a simple example how that needs to be structured) or remove the condition you want to replace and then add a new one using condition(). – Berdir Feb 22 '12 at 23:15
0

Instead of manually rebuilding the existing conditions as in the answer from @Clive , you can do this automatically.

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

  // Build a new AND condition from the existing conditions.
  $and = db_and();
  foreach ($conditions as $key => $condition) {
    // Skip the '#conjunction' special key.
    if (is_numeric($key)) {
      $and->condition($condition['field'], $condition['value'], $condition['operator']);
    }
  }

  // Reset the conditions. 
  // It needs a default #conjunction that will end up being ignored.
  $conditions = array('#conjunction' => 'OR');

  // Create a new condition from the rebuilt conditions OR our new condition.
  $or = db_or();
  $or->condition('foo.my_field', $bar);
  $or->condition($and);

  // Add our new condition.
  $query->condition($or);

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.