doushi2047 2014-11-11 01:46
浏览 46
已采纳

Doctrine2 + Symfony2:在绑定到DQL查询之前转换值

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?

  • 写回答

4条回答 默认 最新

  • duanmi8349 2014-11-12 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!!

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行