5

So I have users with a field_first_name and a field_last_name and I need my query condition to be something like:

"field_first_name + ' ' + field_last_name LIKE :q". 

Currently I have:

->condition(db_or()->condition('fn.field_first_name_value', $q, 'like')->condition('ln.field_last_name_value', $q, 'like'))

so if first name is "John" and last name is "Smith" and I search for "John S" I get nothing because it doesnt match the first OR last name, I need to check if it matches the firstname + the last name.

How do I do this in Drupal 7?

9

You can try something like the following. You build the name with a small sql expression and then can use it within the where clause.

$select = db_select('people_table', 'p');
$select->addField('id');
$select->addExpression("CONCAT(p.first_name, ' ', p.last_name)", 'full_name');
$select->condition('p.full_name', $some_name, 'LIKE');
$results = $select->execute();
|improve this answer|||||
  • Either that or alternatively, use $select->where() to pass an arbitrary condition to database. – Berdir Jul 20 '12 at 8:39
  • 4
    Thanks, i was able to get it to work by using $query->where("CONCAT(fn.field_first_name_value, ' ', ln.field_last_name_value) LIKE :q", array(':q'=>$q)) – travis Jul 20 '12 at 17:02
  • I think you can also do it with having: $query->addExpression("CONCAT(p.first_name, ' ', p.last_name)", 'full_name'); $query->havingCondition('full_name', $some_name, 'LIKE'); – user1359 Sep 8 '15 at 15:08
1

The approach added by @travis works, but the others do not.

Here's an example of how you could query for a first name and last name with wildcard string matching. In my case, I'm using the redhen_contact table.

$name = 'Andy G';
$select = db_select('redhen_contact', 'rc');
$select->fields('rc');
$select->range(0, 10);
$select->where("CONCAT(rc.first_name, ' ', rc.last_name) LIKE :q", array(':q'=> '%' . $name . '%'));
$results = $select->execute();
|improve this answer|||||
0

In my case, I have separates field for first and last name. I use addExpression with havingCondition:

$query->fields('u', array('uid'));
$query->addExpression("CONCAT(ln.field_last_name_value, ' ', fn.field_first_name_value)", 'full_name');
$query->join('field_data_field_first_name', 'fn', 'u.uid = fn.entity_id');
$query->join('field_data_field_last_name', 'ln', 'u.uid = ln.entity_id');
$query->havingCondition('full_name', '%' . $keyword . '%', 'LIKE');
$uids = $query->execute()->fetchCol();
|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.