Doctrine QueryBuilder:使用别名时出现/ where错误 - Doctrine QueryBuilder: having / where error using alias

我有Symfony 2.5.1.

EntityRepository

class TbActivityRepository extends EntityRepository
    {   
        public function findSchoolActivities($school_id)
        {
                $qb = $this->_em->createQueryBuilder();
                $qb->select("a.name, a.datefrom, a.dateto, a.description, (SELECT COUNT(DISTINCT p1.school) 
                                    FROM DomestosAdminBundle:TbPhoto p1 
                                WHERE p1.activity = a.id) AS active_schools,
                            CASE 
                                WHEN CURRENT_TIMESTAMP() >= a.datefrom AND CURRENT_TIMESTAMP() <= a.dateto THEN 'open'
                                WHEN CURRENT_TIMESTAMP() > a.dateto THEN 'close'
                                ELSE 'new'
                            END AS actual_status")
                   ->from("DomestosAdminBundle:TbActivity", "a")
                   ->leftJoin("a.photo", "p")
                   ->where("p.school = :id")
                   ->setParameter("id", $school_id);
                return $qb;
        }

在控制器我调用

$this->qb = $this->getDoctrine()
                ->getRepository('DomestosAdminBundle:TbActivity')
                ->findSchoolActivities($school_id);

之后

public function ajaxTable($flag = false){
        if ( isset( $this->get['iDisplayStart'] ) && $this->get['iDisplayLength'] != '-1' ){
            $this->qb->setFirstResult( (int)$this->get['iDisplayStart'] )
                ->setMaxResults( (int)$this->get['iDisplayLength'] );
        }
        /*
        * Ordering
        */
        if ( isset( $this->get['iSortCol_0'] ) ){
            for ( $i=0 ; $i<intval( $this->get['iSortingCols'] ) ; $i++ ){
                if ( $this->get[ 'bSortable_'.intval($this->get['iSortCol_'.$i]) ] == "true" ){
                    $this->qb->orderBy($this->aColumns[ (int)$this->get['iSortCol_'.$i] ], $this->get['sSortDir_'.$i]);
                }
            }
        }
        /*
        * Filtering
        */
        if ( isset($this->get['sSearch']) && $this->get['sSearch'] != '' ){ // main search
            $aLikeWhere = array();
            $aLikeHaving = array();
            for ( $i=0 ; $i<count($this->aColumns) ; $i++ ){
                if ( isset($this->get['bSearchable_'.$i]) && $this->get['bSearchable_'.$i] == "true" ){
                    if($this->alias[$i] != null) {
                        $aLikeWhere[] = $this->qb->expr()->like($this->aColumns[$i], '''%'. $this->get['sSearch'] .'%''');
                    } else {
                        $aLikeHaving[] = $this->qb->expr()->like($this->aColumns[$i], '''%'. $this->get['sSearch'] .'%''');
                    }
                }
            }
            if(count($aLikeWhere) > 0) $this->qb->andWhere(new Expr'Orx($aLikeWhere));
            else unset($aLikeWhere);
            if(count($aLikeHaving) > 0) $this->qb->andHaving(new Expr'Orx($aLikeHaving));
            else unset($aLikeHaving);
.....
$rResult = $this->ajaxTable(true)->getArrayResult();

和我的错误是:

[Syntax Error] line 0, col 756: Error: Expected '.' or '(', got 'active_schools'
500 Internal Server Error - QueryException
1 linked Exception:
QueryException »
[2/2] QueryException: [Syntax Error] line 0, col 756: Error: Expected '.' or '(', got 'active_schools'   +
[1/2] QueryException: SELECT a.name, a.datefrom, a.dateto, a.description, (SELECT COUNT(DISTINCT p1.school) 
FROM DomestosAdminBundle:TbPhoto p1 
WHERE p1.activity = a.id) AS active_schools,
CASE 
WHEN CURRENT_TIMESTAMP() >= a.datefrom AND CURRENT_TIMESTAMP() <= a.dateto THEN 'open'
WHEN CURRENT_TIMESTAMP() > a.dateto THEN 'close'
ELSE 'new'
END AS actual_status FROM DomestosAdminBundle:TbActivity a LEFT JOIN a.photo p WHERE p.school = :id AND (a.name LIKE '%3%' OR a.datefrom LIKE '%3%' OR a.dateto LIKE '%3%' OR a.description LIKE '%3%') HAVING active_schools LIKE '%3%' OR actual_status LIKE '%3%' ORDER BY a.name asc   +

我尝试了它没有有，只有在子句，并返回它也是一个错误。我读到*db别名*必须通过调用。在我的MySQL数据库，它的工作原理。但不是在这里。你知道有人建议我解决这个问题吗?

谢谢

Doctrine使用DQL(而不是SQL)构建查询，因为它支持所有类型的数据库软件(SQL Server, MySQL, PostgreSQL等)，因此您仅限于特定的功能集。

您想要使用NativeQuery类生成SQL查询，您可以在doctrine文档

中找到更多信息。

这里有一些指示(我没有测试，但应该根据文档工作):

use Doctrine'ORM'Query'ResultSetMapping;
class TbActivityRepository extends EntityRepository
{   
    public function findSchoolActivities($school_id)
    {
        $rsm = new ResultSetMapping();
        $query = $this->_em->createNativeQuery('SELECT * FROM table WHERE schoolId = ?', $rsm);
        $query->setParameter(1, $school_id);
        return $query->getResult();
    }
}

如果您需要比上面的示例更多的内容，您将需要检查文档。值得注意的是，一旦你进行本地查询，你的应用将绑定到特定的数据库软件。