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
– b.b3rn4rd, Commented Nov 11, 2014 at 2:58
Comprehensive answer under your related question on dba.SE: dba.stackexchange.com/a/82348/3684 — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 11, 2014 at 3: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
– b.b3rn4rd, Commented Nov 11, 2014 at 3:59
@b.b3rn4rd I'll assume that you are talking about this 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
– ReynierPM, Commented Nov 11, 2014 at 4:03

Pradeep · Accepted Answer · 2014-11-12 01:29:30Z

1

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

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

answered Nov 12, 2014 at 1:29

Pradeep

2,4971 gold badge18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

ReynierPM Over a year ago

Still not working for me yet, which PostgreSQL version do you have? I'm using 9.2.x latest

Pradeep Over a year ago

In the second try, why are you not adding the '%' to $anno? $qb->setParameter('anno', $anno, PDO::PARAM_INT);

Pradeep Over a year ago

And why is the type INT? you are sending a string when you concatenate.

ReynierPM Over a year ago

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

Pradeep Over a year ago

Try the second one but with PARAM_STR.

|

ReynierPM · Accepted Answer · 2014-11-12 21:02:24Z

1

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!!

answered Nov 12, 2014 at 21:02

ReynierPM

18.7k57 gold badges207 silver badges392 bronze badges

6 Comments

Erwin Brandstetter Over a year ago

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.

ReynierPM Over a year ago

@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

Erwin Brandstetter Over a year ago

There is always raw sql, ORMs are just crutches that don't always support the full range of functionality.

ReynierPM Over a year ago

@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

Erwin Brandstetter Over a year ago

If you use a prepared statement properly, you can rule out SQL injection completely. Example: stackoverflow.com/questions/18398822/…

|

Erwin Brandstetter · Accepted Answer · 2014-11-13 12:30:08Z

0

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.

answered Nov 13, 2014 at 12:30

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

KamikX · Accepted Answer · 2023-05-24 18:20:27Z

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, 2023 at 18:20

KamikX

363 bronze badges

Comments

Craig Ringer · Accepted Answer · 2014-11-13 00:31:40Z

-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, 2014 at 0:31

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

1 Comment

ReynierPM Over a year ago

That's already tried and didn't work since I come up with my solution

Collectives™ on Stack Overflow

Doctrine2 + Symfony2: Cast values before bind to DQL query

5 Answers 5

11 Comments

6 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

11 Comments

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related