Doctrine2 + Symfony2: Cast values before bind to DQL query

Question

I'm building a function for filter some records based on four parameters: $codigo, $anno, $term and $comite_tecnico. This is what I build until now:

public function filtrarNorma($codigo = null, $anno = null, $term = null, $comite_tecnico = null)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb
            ->select('n')
            ->from("AppBundle:Norma", "n");

    if ($codigo != NULL) {
        $qb->where($qb->expr()->like('n.numero', ':codigo'));
        $qb->setParameter('codigo', '%' . $codigo . '%');
    }

    if ($anno != NULL) {
        $qb->orWhere($qb->expr()->like('n.anno', ':anno'));
        $qb->setParameter('anno', '%' . $anno . '%');
    }

    if ($term != NULL) {
        $qb->orWhere($qb->expr()->like('n.nombre', ':term'));
        $qb->setParameter('term', '%' . $term. '%');
    }

    if ($comite_tecnico != NULL) {
        $qb->orWhere($qb->expr()->like('n.comite_tecnico', ':comite_tecnico'));
        $qb->setParameter('comite_tecnico', '%' . $comite_tecnico . '%');
    }

    return $qb->getQuery()->getResult();
}

Any time I try to perform a query I get this error:

An exception occurred while executing 'SELECT n0_.numero AS numero0, n0_.anno AS anno1, n0_.id AS id2, n0_.nombre AS nombre3, n0_.activo AS activo4, n0_.comite_tecnico_id AS comite_tecnico_id5 FROM nomencladores.norma n0_ WHERE n0_.numero LIKE ? OR n0_.anno LIKE ?' with params ["34", 45]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown LINE 1: ...dores.norma n0_ WHERE n0_.numero LIKE $1 OR n0_.anno LIKE $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

That's telling me that I need to cast some of those parameters before send it to the PgSQL DB and execute the query to get results but my question is, how I do that on Doctrine2 DQL? It's possible? Any workaround or trick or something else? I've found this documentation but don't know which function apply and also how, can any give me some help or advice around this?

Edit with new tests

After users suggestions I made some changes to my code and now it looks like:

public function filtrarNorma($codigo = null, $anno = null, $term = null, $comite_tecnico = null)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb
            ->select('n')
            ->from("AppBundle:Norma", "n");

    if ($codigo != NULL) {
        $qb->where($qb->expr()->like('n.numero', ':codigo'));
        $qb->setParameter('codigo', '%'.$codigo.'%', PDO::PARAM_STR);
    }

    if ($anno != NULL) {
        $qb->orWhere($qb->expr()->like('n.anno', ':anno'));
        $qb->setParameter('anno', $anno, PDO::PARAM_INT);
    }

    if ($term != NULL) {
        $qb->orWhere($qb->expr()->like('n.nombre', ':term'));
        $qb->setParameter('term', '%'.$term.'%', PDO::PARAM_STR);
    }

    if ($comite_tecnico != NULL) {
        $qb->orWhere($qb->expr()->like('IDENTITY(n.comite_tecnico)', ':comite_tecnico'));
        $qb->setParameter('comite_tecnico', '%'.$comite_tecnico.'%', PDO::PARAM_INT);
    }

    return $qb->getQuery()->getResult();
}

But once again, get the same error:

An exception occurred while executing 'SELECT n0_.numero AS numero0, n0_.anno AS anno1, n0_.id AS id2, n0_.nombre AS nombre3, n0_.activo AS activo4, n0_.comite_tecnico_id AS comite_tecnico_id5 FROM nomencladores.norma n0_ WHERE n0_.numero LIKE ? OR n0_.anno LIKE ?' with params ["%4%", "4"]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown LINE 1: ...dores.norma n0_ WHERE n0_.numero LIKE $1 OR n0_.anno LIKE $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

And as you may notice in this case params are passed as should be: ["%4%", "4"] but why the error? Still not getting where it's

Another test

So, getting ride of Doctrine Query Builder and applying some Doctrine Query Language I moved the query from the code above to this one:

    $em = $this->getEntityManager();
    $query = $em->createQuery("SELECT n from AppBundle:Norma n WHERE n.numero LIKE '%:codigo%' OR n.anno LIKE '%:anno%' OR n.nombre LIKE '%:term%' OR IDENTITY(n.comite_tecnico) LIKE '%:comite_tecnico%'");
    $query->setParameters(array(
            'codigo' => $codigo,
            'anno' => $anno,
            'term' => $term,
            'comite_tecnico' => $comite_tecnico
        ));

    return $query->getResult();

But in this case I get this message:

Invalid parameter number: number of bound variables does not match number of tokens

If the query is made by OR should be the four parameters required?

`setParameter` third argument allows you to specify type, using `PDO::PARAM_* or \Doctrine\DBAL\Types\Type::* constant` — b.b3rn4rd, Nov 11 '14 at 02:58
Comprehensive answer under your related question on dba.SE: http://dba.stackexchange.com/a/82348/3684 — Erwin Brandstetter, Nov 11 '14 at 03:49
@ErwinBrandstetter, concatenating `'%' . $term. '%'` will result in a `string`. What confuses me is that params are passed without `%` sign (based on the error message: `with params ["34", 45]`) — b.b3rn4rd, Nov 11 '14 at 03:59
@b.b3rn4rd I'll assume that you are talking about [this](http://www.doctrine-project.org/api/orm/2.2/source-class-Doctrine.ORM.QueryBuilder.html#325) but having Erwin answer at SDA I'm complete lost, maybe is not supported at Query Builder level and I need to write a raw query and transform later using `createQuery()` but again that relies on `setParameter()` so what's your advice? — ReynierPM, Nov 11 '14 at 04:03
@b.b3rn4rd: I am an expert with Postgres. Not so much with Doctrine or Symphony. Those concatenate a query string for you, but you can always just execute raw SQL. That's talking the native language of the RDBMS instead of having an interpreter do the work for you (more or less reliably). — Erwin Brandstetter, Nov 11 '14 at 04:10
The remaining error is *not the parameter* for the pattern, but the column `anno` itself being type `integer`. You need to get in the cast to `text`. — Erwin Brandstetter, Nov 11 '14 at 04:19
@ErwinBrandstetter having the DDL exposed on [this](http://dba.stackexchange.com/questions/82343/how-to-cast-a-value-in-postgresql-for-use-in-where-with-like-statement/) question, how the cast for each column should be? I'm still getting more and more errors of the same type and don't know where I'm mess things — ReynierPM, Nov 11 '14 at 04:26
@ReynierPM, change `$qb->setParameter('comite_tecnico', '%'.$comite_tecnico.'%', PDO::PARAM_INT);` to `$qb->setParameter('comite_tecnico', '%'.$comite_tecnico.'%', PDO::PARAM_STR);` — b.b3rn4rd, Nov 11 '14 at 04:27
@b.b3rn4rd the same error but `.... with params ["4", "4", "5", "2"]` :-\ — ReynierPM, Nov 11 '14 at 04:31

score 1 · Answer 1 · answered Nov 12 '14 at 01:29

1

Your first try actually works for me all the time. You can convert your integers using strval()'.

'%' . strval($anno) . '%';

answered Nov 12 '14 at 01:29

Pradeep

2,469
1
18
27

Still not working for me yet, which PostgreSQL version do you have? I'm using 9.2.x latest – ReynierPM Nov 12 '14 at 03:23
In the second try, why are you not adding the '%' to $anno? $qb->setParameter('anno', $anno, PDO::PARAM_INT); – Pradeep Nov 12 '14 at 15:51
And why is the type INT? you are sending a string when you concatenate. – Pradeep Nov 12 '14 at 15:52
well I remove it doing test I could add back again and all are test, none works for me, I'm still trying to find which one is the right approach – ReynierPM Nov 12 '14 at 15:53
Try the second one but with PARAM_STR. – Pradeep Nov 12 '14 at 16:03
nothing didn't work the same error `An exception occurred while executing 'SELECT n0_.numero AS numero0, n0_.anno AS anno1, n0_.id AS id2, n0_.nombre AS nombre3, n0_.activo AS activo4, n0_.comite_tecnico_id AS comite_tecnico_id5 FROM nomencladores.norma n0_ WHERE n0_.numero LIKE ? OR n0_.anno LIKE ?' with params ["%3%", "%3%"]: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown LINE 1: ...dores.norma n0_ WHERE n0_.numero LIKE $1 OR n0_.anno LIKE $2 ^` – ReynierPM Nov 12 '14 at 16:06
Postgres does not have automatic type casting. What you are doing is trying to use the LIKE on an integer column (anno). I am guessing this is the issue (anno is an integer). You need to cast the field like this: $qb->orWhere($qb->expr()->like('CAST(n.anno AS text)', ':anno')); http://stackoverflow.com/a/4930396/82265 – Pradeep Nov 12 '14 at 16:53
and for that reason is what in the second test I use `if ($anno != NULL) { $qb->orWhere($qb->expr()->like('n.anno', ':anno')); $qb->setParameter('anno', $anno, PDO::PARAM_INT); }` trying to set the parameter as INT and still not working, didn't know what else to try here – ReynierPM Nov 12 '14 at 16:59
Can you try this? $qb->orWhere($qb->expr()->like('CAST(n.anno AS text)', ':anno')); – Pradeep Nov 12 '14 at 17:20
didn't work `[Syntax Error] line 0, col 63: Error: Expected known function, got 'CAST'` – ReynierPM Nov 12 '14 at 17:45
I think you need to enable implicit casting in postgres. – Pradeep Nov 12 '14 at 17:59

score 1 · Accepted Answer · answered Nov 12 '14 at 21:02

After a deep research I've found the solution to my problem and want to share with others too. I should said also thanks to @ErwinBrandstetter, @b.b3rn4rd for their time and support and to @Pradeep which finally give me the idea for research and finally get problem fixed and I did by enabling implicit casting support in PostgreSQL.

For enable implicit casts you must therefore execute the following commands in your PostgreSQL console when connected to the template1 database, so that any database created afterward will come with the required CASTs (if your database is already created, execute the commands in your database as well):

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

That's all, after running that on the current DB I'm using and also on template1 for future ones and keeping conditions on my code as follow, all works fine and without any errors:

if ($codigo != null) {
    $qb->where($qb->expr()->like('n.numero', ':codigo'));
    $qb->setParameter('codigo', '%'.$codigo.'%', PDO::PARAM_STR);
}

if ($anno != null) {
    $qb->orWhere($qb->expr()->like('n.anno', ':anno'));
    $qb->setParameter('anno', '%'.$anno.'%', PDO::PARAM_STR);
}

if ($term != null) {
    $qb->orWhere($qb->expr()->like('n.nombre', ':term'));
    $qb->setParameter('term', '%'.$term.'%', PDO::PARAM_STR);
}

if ($comite_tecnico != null) {
    $qb->orWhere($qb->expr()->like('IDENTITY(n.comite_tecnico)', ':comite_tecnico'));
    $qb->setParameter('comite_tecnico', '%'.$comite_tecnico.'%', PDO::PARAM_STR);
}

Happy coding!!

I would advice **against** introducing implicit casts for basic types like that. It can have unforeseen side effects. If there is no such cast defined for basic types, there is probably a good reason. — Erwin Brandstetter, Nov 13 '14 at 10:07
@ErwinBrandstetter that's the only way I found for fix the issue, I've already try everything you see on the main post and comments, perhaps ORM doesn't support this at all or I'm doing something wrong and I don't know what else to do — ReynierPM, Nov 13 '14 at 11:19
There is always raw sql, ORMs are just crutches that don't always support the full range of functionality. — Erwin Brandstetter, Nov 13 '14 at 11:29
@ErwinBrandstetter yes, I know that too, but if I'm already using the ORM for the rest of the application wont be recomendable switch at 50% of development to raw SQL and also it's not recommended at all due to SQL injections and security holes and some more much, anyway I keep looking for a better solution and keep waiting for others to participate on this topic and see if any comes with a best solution — ReynierPM, Nov 13 '14 at 11:31
If you use a prepared statement properly, you can rule out SQL injection completely. Example: http://stackoverflow.com/questions/18398822/doctrine-dbal-querybuilder-as-prepared-statement — Erwin Brandstetter, Nov 13 '14 at 11:47
@ErwinBrandstetter that's exactly what I'm using here "a prepared statement" look into my code, I'm using already the same syntax through Doctrine Query Builder — ReynierPM, Nov 13 '14 at 12:14

score 0 · Answer 3 · answered Nov 13 '14 at 12:30

I think in your last try the raw SQL string should look like this:

$query = $em->createQuery("SELECT n.*
    FROM  nomencladores.norma n
    WHERE n.numero LIKE '%' || :codigo || '%' OR
          cast(n.anno AS text) LIKE '%' || :anno || '%' OR
          n.nombre LIKE '%' || :term || '%' OR
          IDENTITY(n.comite_tecnico) LIKE '%' || :comite_tecnico || '%'");

Any other column here not text or varchar? Cast it, too.
Don't know the IDENTITY() function. A spillover from Doctrine, as well?
Still, I don't know much about Doctrine.

score 0 · Answer 4 · answered May 24 '23 at 18:20

0

With doctrine/orm version 2.15.1 + PG version 15 and this doctrine extension which provides CAST extension this code works like charm.

 $qb->where($qb->expr()->like('CAST('c.id' AS text)', ':search'));

answered May 24 '23 at 18:20

KamikX

36
2

score -1 · Answer 5 · answered Nov 13 '14 at 00:31

-1

You're trying to use LIKE on an integer, which doesn't make sense.

Cast the integer to its text representation. This might work:

$qb->where($qb->expr()->like('CAST(n.numero AS text)', ':codigo'));

answered Nov 13 '14 at 00:31

Craig Ringer

307,061
76
688
778

That's already tried and didn't work since I come up with my solution – ReynierPM Nov 13 '14 at 00:33

Doctrine2 + Symfony2: Cast values before bind to DQL query

5 Answers5