How to add Replace (mysql) function in view query condition field

Question

I am using EntityReference Autocomplete with filter by entity reference view. And I want to ignore space in search filed.

For example, column username in DB has value first name it should popup when user types first or firstname. To achieve this I wrote hook_views_query_alter to change the condition, I would like to pass the condition as REPLACE(username, ' ', '') LIKE '%firstname%'.

Even if I set field value in condition to REPLACE(username, ' ', '') its getting changed to REPLACEusername. Even I tried running the query separately, like below in node,

$query = db_select('node', 'n')
              ->fields('n', array('title'))
              ->condition('type', 'article', '=')
              ->condition('REPLACE(n.title, \' \', \'\')', $category, 'LIKE')

When I output query prints like below,

    SELECT n.title AS title FROM {node} n WHERE (type = 'article') AND (REPLACEn.title LIKE '%articleone%' ESCAPE '\\')

How can I set it correctly? Is there any other way to do?

Answer 1

I don't think condition is what you want, rather expression. And then, to use that expression as a condition, you might need to use havingCondition-- but I'm not sure, test it out. Also note that you need % for a wildcard matching in SQL, so you might have to do ->havingCondition('title', '%' . $category . '%', 'LIKE');.

So, your final code might look something like this:

$query = db_select('node', 'n')
  ->addExpression('REPLACE(n.title, \' \', \'\')', 'title')
  ->condition('type', 'article', '=')
  ->havingCondition('title', '%' . $category . '%', 'LIKE');