CODEWITHSUNDEEP
regexdoctrine-ormconcatenationquery-builderdoctrine-dbal
Marin Sagovac
Marin Sagovac

Reputation: 3972

Symfony - addHaving - concat + regexp

Raw query:

select firstfield, secondfield, phone_number, thirdfield 
from table 
having CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value' 
   and CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value2' 
   and CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value3'
   and CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value4'

Querybuilder

    $qb->select(
        'firstfield',
    'secondfield',
    'thirdfield',
    'fourthfield',
    )->from(Table, 'u');


$queryHaving = "CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value'";
$qb->andhaving($queryHaving);

$queryHaving = "CONCAT(firstfield, ' ', secondfield, ' ', thirdfield, ' ', fourthfield) regexp 'value2'";
$qb->andhaving($queryHaving);

Problem:

How to collect concat with regexp not as function? Tried using literal() function but it is not possible to create due error throws on not possible to assign into.

Upvotes: 0

Views: 197

Answers (1)

Julio
Julio

Reputation: 5308

The query seems to work for me for MySQL with any of these 2 forms:

select *
from test
having concat(field1, field2) regexp '^[FB].*' and
       concat(field1, field2) regexp 'o$';

select *
from test
where concat(field1, field2) regexp '^[FB].*' and
      concat(field1, field2) regexp 'o$';

See demo here

I'm just thinkging about the problem could be with CHAR columns

So, for example, one column would have FOO<space><space> on a CHAR(5) instead of FOO at VARCHAR(5). So when concatenating you would have something similar to FOO<space><space>BAR<space><space> and thus the regex would fail.

However, with SQLFiddle it doesn't seem to be the case. It does not seem to add spaces. See here.

Anyways, it may be worth trying on your app: Are you using chars or varchars? Could you try adding trims at the columns, like this:

select *,concat(trim(field1), trim(field2))
from test
having concat(trim(field1), trim(field2)) regexp '^[FB].*' and
       concat(trim(field1), trim(field2)) regexp 'o$';


select *,concat(trim(field1), trim(field2))
from test
where concat(trim(field1), trim(field2)) regexp '^[FB].*' and
      concat(trim(field1), trim(field2)) regexp 'o$';

Demo here.

Upvotes: 0

Related Questions