How to create a query builder update with inner join

Question

I have this query :

$query = $this->_em->createQuery(
        "UPDATE PublicBundle:Table1 t1
         JOIN PublicBundle:Table2 t2 WITH t1.ad_id = t2.id
         SET t1.status = :status
         WHERE t1.status IN (':statuses') 
         AND t2.updated_at < ':dateLimit'"
        )->setParameter('status', 1)
         ->setParameter('statuses', implode(",", $statuses))
         ->setParameter('dateLimit', new \DateTime(sprintf('-%d day', $date))
echo $query->getSQL();
$query->execute();

But I get the error :

Error: Expected Doctrine\ORM\Query\Lexer::T_EQUALS, got 't2');

Can you help me please ? Thx in advance

Answer 1

    $sql ="UPDATE Table1 t1
     JOIN Table2 t2 ON t1.ad_id = t2.id
     SET t1.status = :status
     WHERE t1.status IN (':statuses') 
     AND t2.updated_at < ':dateLimit'";
    $params=array(
       'status'=>1,
        'statuses'=> implode(",", $statuses)
'dateLimit'=>new \DateTime(sprintf('-%d day', $date)
    );
    return $this->getEntityManager()->getConnection()->executeQuery($sql, $params);   

Try to use this inside your custom repository.First do it hardcoded inside your phpmyadmin then try this sql on your script