doudouji2016
2016-10-04 09:37
浏览 56
已采纳

Symfony3 - 在单个sql查询中更新多个实体

Bouncing from this old post i am looking for a way to update several Symfony entites in one sql query - for optimisation reasons. I have a Content entity, and a method in a service, that updates all "sibling" contents with setCurrent(0). Here's the working code so far:

/**
 * Set given content the latest regarding its siblings
 */
public function setLatestContent(Entity\Content $content)
{
    // Get siblings entities
    $siblings = $this
        ->em
        ->getRepository('LPFrontRteBundle:Content')
        ->findBySibling($content);

    foreach ($siblings as $sibling_content) {
        $sibling_content->setCurrent(0);
    }

    $this->em->flush();
}

Well that works. But since i have 38 sibling contents, i get 38 SQL queries like:

UPDATE mr_content SET current = 0 WHERE id = 55
UPDATE mr_content SET current = 0 WHERE id = 56
UPDATE mr_content SET current = 0 WHERE id = 57
...

I would like to use Doctrine's "clean" Entity system, in a way to have one query like:

UPDATE mr_content SET current = 0 WHERE id = 55 OR id = 56 OR id = 57 ...

Any thoughts on how to achieve that - or a smarter workaround, would be greatly appreciated.


EDIT

For the record, here's what i came up with - i like it verbose :) With $qb as query builder.

    $qb->update('LPFrontRteBundle:Content', 'c')
        ->set('c.current', 0)

        ->where('c.keyword = :keyword')
        ->setParameter('keyword', $content->getKeyword())

        ->andWhere('c.locale = :locale')
        ->setParameter('locale', $content->getLocale())

        ->andWhere('c.id != :id')
        ->setParameter('id', $content->getId())

        ->getQuery()->execute();
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongzhuo1498 2016-10-04 09:53
    已采纳

    Batch processing is what you're looking for.

    There are two bulk update methods in Doctrine2.

    First one is DQL Update Query, which will best probably best for you. It would be something like:

    $q = $em->createQuery("UPDATE LPFrontRteBundle:Content c SET c.current = 0 WHERE id IN (:ids)")
            ->setParameter(':ids', $ids, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
    $numUpdated = $q->execute();
    
    评论
    解决 无用
    打赏 举报
  • 查看更多回答(1条)

相关推荐 更多相似问题