Symfony3 QueryBuilder update with Subquery

Question

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?

Try wrapping your `IN` subquery with parentheses `->where('teachingEvent.id IN(' . $subQuery . ')')` Also the `setParameters` must be set on the main `$query`, not `$subquery`. Lastly mysql can not select from the same table in a subquery during an `INSERT`, `UPDATE` or `DELETE` statement. http://dev.mysql.com/doc/refman/5.0/en/subqueries.html — Will B., Jan 01 '18 at 05:25

score 1 · Answer 1 · answered Jan 01 '18 at 06:37

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();

Yes. I know. I did it now this way. But I thougt it coudl work like that. It would be more fast for database-performance issues. — Slowwie, Jan 01 '18 at 21:34
@Slowwie Don't really say for performance, But in doctrine update query there is no support of joins and using sub query in update from same table would result you in this error [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause), So as per my knowledge this is your option — M Khalid Junaid, Jan 02 '18 at 05:21

Symfony3 QueryBuilder update with Subquery

1 Answers1