join - 使用 concat 更新 QueryBuilder

Question

我有以下原始 SQL 查询:

UPDATE mySock s1 
LEFT JOIN mySock s2
 ON s1.parentId = s2.id
SET
   s1.status = 1
   s1.mylevel = (s2.mylevel + 1),
   s1.parentString = CONCAT(s2.parentString, ':' CONCT(s1.id as char))
WHERE
    s1.zz = 0;

并在 Symfony 3.2 中创建

public function updateParentNew($idParent)
{
    return $this->createQueryBuilder('s1')
        ->update('MyBundle:Stock', 's1')
        ->leftJoin(''MyBundle:Stock', 's2', 'WITH', 's2.id = s1.parentId')
        ->set('s1.zz', 1)
        ->set('s1.leveltask', 's2.leveltask + 1')
        ->set('s1.parentString', '?par2_string')
        ->where('s1.zz = 0')
        ->andWhere('s1.parentId = ?par1')
        ->setParameter('par1', $idParent)
        ->setParameter('par2_string', s2.parentString + ':' + (string)s1.id)
        ->getQuery()
        ->getSingleScalarResult();
}

这是行不通的。 Concat值(字符串和数字)的方法是什么？

Answer 1

我会尝试这样的事情:

public function updateParentNew($idParent)
{
    $qb = $this->createQueryBuilder('s1')
        ->update('MyBundle:Stock', 's1')
        ->leftJoin('MyBundle:Stock', 's2', 'WITH', 's2.id = s1.parentId')
    ;

    $qb->set('s1.zz', 1)
        ->set('s1.leveltask', $qb->expr()->sum('s2.leveltask', 1))
        ->set('s1.parentString',
            $qb->expr()->concat(
                $qb->expr()->literal('s2.parentString'),
                $qb->expr()->concat(':', $qb->expr()->literal('s1.id'))
        ))
        ->where('s1.zz = 0')
        ->andWhere('s1.parentId = :par1')
        ->setParameter('par1', $idParent)
        ->getQuery()
        ->getSingleScalarResult()
    ;

    return $qb;
}