symfony2 教义 expr 子查询:错误:参数号无效

2022-01-23 00:00:00 subquery php symfony doctrine

试图获得用户喜欢的状态.

公共函数 getLikedStatuses(User $user){$qb = $this->_em->createQueryBuilder();$qb->选择('s.id')->from('WallBundle:喜欢','l')->innerJoin('l.status', 's')->where('l.user = :user')->setParameter('user', $user)->orderBy('s.id','DESC');$qb2= $this->_em->createQueryBuilder()->选择('st')->from('WallBundle:Status','st');$qb2->andWhere($qb2->expr()->in('st.id',$qb->getDQL()));return $qb2->getQuery()->getResult();}

<块引用>

错误:无效的参数号:绑定变量的数量与标记的数量不匹配

顺便说一句:当我转储 $qb->getDQL() 时:

string 'SELECT s.id FROM TBWBundleEntityLikes l LEFT JOIN l.status s WHERE l.user = :user' (length=87)

BTW2:当我将 '$qb->getDQL()' 替换为 (12073)(状态 ID)时,它可以工作......

解决方案

由于使用子查询时学说不支持限制(看看我的评论)一种可能的解决方案是执行两个单独的查询,这并不理想但有效.

/*** @param 用户 $user* @param int $limit* @param int $offset* @return 用户[]*/公共函数 getUserFollowers(用户 $user, $limit = 20, $offset = 0){$_followersIds = $this->getEntityManager()->createQueryBuilder()->select('IDENTITY(r.followeduser)')->from($this->getEntityName(), 'r')->where('r.followeeuser = :user')->andWhere('r.followeduser !=:user')->setParameter('user', $user)->orderBy('r.id', 'DESC')->setFirstResult($offset)->setMaxResults($limit)->getQuery()->getResult();$_usersQb = $this->getEntityManager()->createQueryBuilder();$_usersQb->选择('你')->from('UserBundle:User', 'u')->where('u.id IN (:followersIds)')->setParameter('followersIds', array_values($_followersIds));return $_usersQb->getQuery()->getResult();}

trying to get liked statuses by user.

public function getLikedStatuses(User $user)
{
    $qb = $this->_em->createQueryBuilder();
                $qb
                ->select('s.id')
                ->from('WallBundle:Likes','l')
                ->innerJoin('l.status', 's')
                ->where('l.user = :user')
                ->setParameter('user', $user)
                ->orderBy('s.id','DESC')
            ;

    $qb2=  $this->_em->createQueryBuilder()
        ->select('st')
        ->from('WallBundle:Status','st');

       $qb2 ->andWhere($qb2->expr()->in('st.id',$qb->getDQL()));


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

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

BTW: when i dump the $qb->getDQL():

string 'SELECT s.id FROM TBWBundleEntityLikes l LEFT JOIN l.status s WHERE l.user = :user' (length=87)

BTW2: when i replace the '$qb->getDQL()' for (12073) (id of status) it works...

解决方案

As doctrine doesn't support a limit when using subquery (look at my comment) one possible solution is to execute two separate queries which is not ideal but works.

/**
 * @param User $user
 * @param int $limit
 * @param int $offset
 * @return User[]
 */
public function getUserFollowers(User $user, $limit = 20, $offset = 0)
{
    $_followersIds = $this->getEntityManager()
        ->createQueryBuilder()
        ->select('IDENTITY(r.followeduser)')
        ->from($this->getEntityName(), 'r')
        ->where('r.followeeuser = :user')
        ->andWhere('r.followeduser !=:user')
        ->setParameter('user', $user)
        ->orderBy('r.id', 'DESC')
        ->setFirstResult($offset)
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();

    $_usersQb = $this->getEntityManager()->createQueryBuilder();
    $_usersQb
        ->select('u')
        ->from('UserBundle:User', 'u')
        ->where('u.id IN (:followersIds)')
        ->setParameter('followersIds', array_values($_followersIds));

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

相关文章