0

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?

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

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.