How to add db_select condition by using two concatenated fields?

Question

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?

Answer 1

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();

Answer 2

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();

Answer 3

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();