doudouji2016 2016-10-04 01:37
浏览 58
已采纳

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 01: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条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥100 二维码被拦截如何处理
  • ¥15 怎么解决LogIn.vue中多出来的div
  • ¥15 优博讯dt50巴枪怎么提取镜像
  • ¥30 在CodBlock上用c++语言运行
  • ¥15 求C6748 IIC EEPROM程序固化烧写算法
  • ¥50 关于#php#的问题,请各位专家解答!
  • ¥15 python 3.8.0版本,安装官方库ibm_db遇到问题,提示找不到ibm_db模块。如何解决?
  • ¥15 TMUXHS4412如何防止静电,
  • ¥30 Metashape软件中如何将建模后的图像中的植被与庄稼点云删除
  • ¥20 机械振动学课后习题求解答
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部