如何在DQL中嵌套查询中设置参数 [英] How to set parameters in nested query in DQL

问题描述

嗨，
我想要获取用户创建的文章数，查询用于工作，直到我添加了一些使用fromDate和toDate过滤查询的参数日期，这是我的查询：

Hi, I'd like to get number of created articles by a user, the query used to work until I added some parameters filtering the query by "fromDate" and "toDate" dates, here's my query :

// query String
$dql = 'SELECT u.idUser,
               u.lastName,
               u.email,
               u.mobile,
               (SELECT AVG(n.note)
                FROM MyBundle:Note n
                WHERE n.noteFor = u.idUser) AS note,
               (SELECT COUNT(a) 
                FROM MyBundle:Article a 
                WHERE (a.createdBy = u.idUser) AND (a.createdAt BETWEEN :fromDate AND :toDate)) AS articles
         FROM MyBundle:User u';

// create the actual query
$users= $em->createQuery($dql);
// set filter date parameter
$users->setParameter('fromDate', $fromDate.'00:00:00');
$users->setParameter('toDate', $toDate.'23:59:59');

我不断收到此错误：参数号无效：绑定变量的数量不符合令牌的数量。
我尝试在doctrine文档中搜索如何在嵌套查询中设置参数，而无需查找任何内容。首先我需要知道是否可以这样做，然后找出错误来自哪里，请帮助！

I keep getting this error : Invalid parameter number: number of bound variables does not match number of tokens. I tried searching in the doctrine documentation for how to set parameters in nested queries without finding anything. first I need to know if it's possible to do that then find where the error come from, Please Help !

推荐答案

所以在进行了一些测试之后，我发现查询的方式很好，问题不在。
我正在使用 KnpPaginatorBundle 分页我的查询，似乎问题是它可以不要对复杂的查询进行分页，例如在嵌套查询中传递多个参数。所以我找到了一个解决方案。
所以这是旧代码：

So after performing some tests, I found out that the query worked well the way it was and the problem wasn't there. I'm using KnpPaginatorBundle to paginate my queries, it seems that the problem was that it couldn't paginate complex queries like passing multiple parameters in the nested query. so I found a solution.
So this is the old code :

// Pagination
$paginator = $this->get('knp_paginator');
$users     = $paginator->paginate($users, 1, 10);

这是新代码：

// Pagination
$paginator = $this->get('knp_paginator');
$users     = $paginator->paginate($users, 1, 10, array('wrap-queries' => true) );

感谢Nisam和Matteo的时间，希望这有助于某人

Thanks to Nisam and Matteo for their time, hope this helps someone

这篇关于如何在DQL中嵌套查询中设置参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！