How do you build up a LIKE
condition in a db_select()
query?
I know how to do this with a db_query()
API call, but is there a special syntax / method for doing this with db_select()
?
Drupal Answers is a question and answer site for Drupal developers and administrators. It only takes a minute to sign up.
Sign up to join this communityHow do you build up a LIKE
condition in a db_select()
query?
I know how to do this with a db_query()
API call, but is there a special syntax / method for doing this with db_select()
?
After digging through Drupal Documentation found a solution in db_like API documentation page and SelectQuery:condition handler doc page..
For example
$result = db_select('field_data_field_name', 'f')->fields('f', array(
'entity_id',
'field_name_value'
))
->condition('entity_type', 'user')
->condition('bundle', 'user')
->condition('deleted', 0)
->condition('field_name_value', '%' . db_like($last_item) . '%', 'LIKE')
->distinct()
->range(0, 10)
->execute();
Condition to use for like query is
->condition('field_name_value', '%' . db_like($last_item) . '%', 'LIKE')
db_like()
is needed to properly escape the argument, and (2) you need to explicitly add the wildcard characters. It will also use the default collation on the database, and I don't think there is any way to specify a different one.
– mpdonadio♦
May 8 '14 at 11:08
You can also use Drupal\Core\Database\Database when creating "LIKE" query. This is Drupal 8 alternate syntax since db_select() is deprecated.
$database = Database::getConnection();
$query = $database->select('TABLE NAME', 'u')
->fields('u', array('column1','column2'));
$query->condition('column1', '%'.$database->escapeLike($search_phrase) . '%', 'LIKE');
Or add multiples with OR query.
$DB_OR = $query->orConditionGroup()
// find match anywhere in field
->condition('column1', '%' . $database->escapeLike($search_phrase) . '%', 'LIKE')
// find match starting at beginning
->condition('column2', $database->escapeLike($search_phrase) . '%', 'LIKE');
// find match at end of field
->condition('column1', '%' . $database->escapeLike($search_phrase), 'LIKE')
$query->condition($DB_OR); // Add OR object as condition
$result = $query->execute();
For using "like" in db_select
is as below and worked for me...
$search_value = "testvalue@xmail.com";
$query = db_select('TABLE NAME', 'u');
$query->fields('u', ['ROW1', 'ROW2']);
$query->condition('ROW1', '%' . db_like($search_value) . '%', 'LIKE');
$result = $query->execute();
Here exact value in ROW1 is "testvalue@xmail.com" and by using LIKE
condition in db_select
I got the output as "testvalue@xmail.com".
it works in Drupal8 Version 1
$exp = "1";
$connection = \Drupal::database();
$query = $connection ->select('node', 'n');
$query->fields('n');
$query->condition('nid', '%'.db_like($exp), 'LIKE');
$records = $query->execute();
foreach ($records as $record) {
ksm($record);
}
Version 2
$connection = \Drupal::database();
$query = $connection->query("SELECT *FROM {node} WHERE nid like '%' :nid '%'", [
':nid' => "1",
]);
$records = $query->fetchAll();
foreach ($records as $record) {
ksm($record);
}
Since db_like() is deprecated and was removed in Drupal 9, the documentation suggests to use Connection::escapeLike() instead.
Here is a working example for Drupal 8 and 9:
$connection = Database::getConnection();
$query = $connection->select('your_table_name', 'table_alias')
->fields('table_alias', ['whatever_field', 'other_field'])
->condition('my_field_name', '%' . $connection->escapeLike($yourSearchString) . '%', 'LIKE');
$result = $query->execute()->fetchAll();
db_query
as an argument placeholder, you can also pass as an argument to...Query::condition
. You won't find documentation for each individual operator for each different type of query, as it wouldn't make sense to do that. It all goes through PDO at the end of the day, ifdb_like
prepares a variable correctly fordb_query
, then by definition it will prepare the same variable correctly fordb_select
– Clive♦ May 8 '14 at 9:07