CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormsql-updatesubquery
Slowwie
Slowwie

Reputation: 1246

Symfony3 QueryBuilder update with Subquery

I tried following stuff:

 $subQuery = $this->createQueryBuilder('teachingEvent')->select('teachingEvent.id')
        ->leftJoin('teachingEvent.timeTableEntry', 'timeTableEntry')
        ->leftJoin('timeTableEntry.course', 'course')
        ->where('course.school = :school' )
        ->andWhere('teachingEvent.date >= :fromDate')
        ->andWhere('teachingEvent.date <= :toDate')
        ->setParameters(array(':fromDate' => $from, ':toDate' => $to, ':school' => $school))
        ->getDQL();


    $query = $this->createQueryBuilder('teachingEvent')
        ->update()
        ->set('teachingEvent.schoolFreeDate', ':schoolFreeDate')
        ->where('teachingEvent.id IN ' . $subQuery)
        ->getQuery();

    return $query->getResult();

When I dump out the subquery I get this:

"SELECT teachingEvent.id FROM AppBundle\Entity\TeachingEvent teachingEvent LEFT JOIN teachingEvent.timeTableEntry timeTableEntry LEFT JOIN timeTableEntry.course course WHERE course.school = :school AND teachingEvent.date >= :fromDate AND teachingEvent.date <= :toDate

For me it seems that is should work now - but I get the following error-message

[Syntax Error] line 0, col 136: Error: Expected Literal, got 'teachingEvent'

How can I find the error?

Upvotes: 0

Views: 537

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You could also do the same by first get your objects from first query builder and then do the required update 

$teachingEvents =  $this->createQueryBuilder('teachingEvent')
                        ->select('teachingEvent')
                        ->leftJoin('teachingEvent.timeTableEntry', 'timeTableEntry')
                        ->leftJoin('timeTableEntry.course', 'course')
                        ->where('course.school = :school' )
                        ->andWhere('teachingEvent.date >= :fromDate')
                        ->andWhere('teachingEvent.date <= :toDate')
                        ->setParameters(array(':fromDate' => $from, ':toDate' => $to, ':school' => $school))
                        ->getQuery()
                        ->getResult();

foreach($teachingEvents as $teachingEvent){
    $teachingEvents->setSchoolFreeDate($someDate);
    $em->persist($teachingEvents);
    /* $em->flush(); flush here or after loop */
}

$em->flush();

Upvotes: 1

Related Questions