Symfony - addHaving - concat + regexp

Question

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.

which database will you run the query at? – Julio Aug 23 '18 at 15:36 — Julio, Aug 23 '18 at 15:36

Julio · Accepted Answer · 2018-08-24T11:52:50.117

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.

No, there are on Symfony a command ->concat. How to use in querybuilder? — Marin Sagovac, Aug 30 '18 at 12:16
Perhaps you could use something like this: `$queryHaving = "CONCAT(trim(firstfield), ' ', trim(secondfield), ' ', trim(thirdfield), ' ', trim(fourthfield)) regexp 'value'";` — Julio, Aug 30 '18 at 14:39
Yes, but you should put literal() var in querybuilder. In these scenario with literatal due we have "concat(...) regexp " and putting into having into query builder throws errors like you can't these combine with outside of regexp. I will mark you as resolved due no one has answer or none found using literal + querybilder + having — Marin Sagovac, Aug 31 '18 at 08:41

Symfony - addHaving - concat + regexp

1 Answers1