Symfony3 - 在单个 sql 查询中更新多个实体 [英] Symfony3 - Update multiple entities in single sql query

问题描述

从这篇旧帖子反弹我正在寻找一种方法来更新几个 Symfony 实体一个 sql 查询 - 出于优化原因.我有一个 Content 实体和一个服务中的方法，它使用 setCurrent(0) 更新所有兄弟"内容.这是目前的工作代码:

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

那行得通.但是因为我有 38 个同级内容，所以我得到了 38 个 SQL 查询，例如:

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
...

我想使用 Doctrine 的干净"实体系统，以某种方式进行如下查询:

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.

编辑

为了记录，这是我想出的 - 我喜欢它冗长:)使用 $qb 作为查询构建器.

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

推荐答案

批处理 正是您要找的.

Doctrine2 中有两种批量更新方法.

There are two bulk update methods in Doctrine2.

第一个是 DQL 更新查询，它可能最适合您.它会是这样的:

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

这篇关于Symfony3 - 在单个 sql 查询中更新多个实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！