Как сделать нативный SQL запрос для UPDATE с WHERE IN условием Doctrine 2?

Question

[Алексей Юдов]

Задача: сделать запрос следущего вида

UPDATE some_table SET some_field = ? WHERE IN (?, ?, ?, ..., ?)

;
Кейс: удаление Воронки №1 из CRM и массовое перемещение лидов из Воронки №1 в Воронку №2;

Comments

[Алексей Юдов]

оставил этот кейс тут, так как провозился с ним очень долго

Answer 1

[Алексей Юдов]

Решение под Symfony 4 и Doctrine 2
Делаем свой абстрактный слой для базы данных
https://www.doctrine-project.org/projects/doctrine...

Настраиваем конфигурацию для DBAL

# the DBAL wrapperClass option
wrapper_class: App\DBAL\MyConnectionWrapper

https://symfony.com/doc/current/reference/configur...

В кастомном DBAL делаем следущую реализацию, также незабываем перенести все private свойства и методы из донора Doctrine\DBAL\Connection:

public function update($table, array $data, array $criteria, array $types = [])
    {
        $columns = $values = $conditions = $set = [];

        foreach ($data as $columnName => $value) {
            $columns[] = $columnName;
            $values[]  = $value;
            $set[]     = $columnName . ' = ?';
        }

        $this->addCriteriaCondition($criteria, $columns, $values, $conditions);

        if (is_string(key($types))) {
            $types = $this->extractTypeValues($columns, $types);
        }

        $sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $set)
                . ' WHERE ' . implode(' AND ', $conditions);

        return $this->executeStatement($sql, $values, $types);
    }

    private function addCriteriaCondition(
        array $criteria,
        array &$columns,
        array &$values,
        array &$conditions
    ): void {
        $platform = $this->getDatabasePlatform();

        foreach ($criteria as $columnName => $value) {
            if ($value === null) {
                $conditions[] = $platform->getIsNullExpression($columnName);
                continue;
            }

            if(is_array($value)) {
                $this->addInCriteriaCondition($columns, $values, $conditions, $value, $columnName);
                continue;
            }

            $columns[]    = $columnName;
            $values[]     = $value;
            $conditions[] = $columnName . ' = ?';
        }
    }

    private function addInCriteriaCondition(
        array &$columns,
        array &$values,
        array &$conditions,
        array $valuesArray,
        $columnName
    ) {
        $condition = $columnName . ' IN (';

        foreach($valuesArray as $index => $value) {
            $columns[]    = $columnName;
            $values[]     = $value;
            $condition    .= '?';

            if($index != count($valuesArray) - 1) {
                $condition    .= ', ';
            }
        }
        
        $conditions[] = $condition . ')';
    }

Comments

[BoShurik]

К чему такие сложности?

$this->getEntityManager()
->getConnection()
->getWrappedConnection()
->exec('UPDATE some_table SET some_field = ? WHERE IN (?, ?, ?, ..., ?)');

[Алексей Юдов]

BoShurik, ваш пример точно рабочий? Проверили или от болды написали?

[BoShurik]

TCloud. Сам запрос, конечно, нет. Но способ получения и использования PDO - вполне.

[Алексей Юдов]

BoShurik, exec() это депрекейт метод, executeStatement() сейчас в ходу. exec() просто пока не выпили.

// Doctrine/DBAL/Connection
/**
     * Executes an SQL statement and return the number of affected rows.
     *
     * @deprecated Use {@link executeStatement()} instead.
     *
     * @param string $sql
     *
     * @return int The number of affected rows.
     *
     * @throws Exception
     */
    public function exec($sql)

[BoShurik]

TCloud, Речь о \Doctrine\DBAL\Driver\Connection

[BoShurik]

TCloud, но в вашем случае можно остановится и на Doctrine/DBAL/Connection, да. В любом случае идея в том, что вы как-то все усложнили

[Алексей Юдов]

BoShurik, последнее чтобы хотелось отметить. Стандартный DBAL в методе update() неподдерживает такие условия как WHERE IN или WHERE OR (см. тут), так что если проект планируется поддерживать долгое время стоит расширить возможность DBAL. В моем решении описан лишь примерный подход того как можно использовать DBAL, так как ресерч в инете ничего не дал.

* имхо: наличие вручную написанных SQL запросов в прошлом уже "стреляло мне в ногу", так что избегаю такого подхода на проде.

[Алексей Юдов]

BoShurik, \Doctrine\DBAL\Driver\Connection нет смысла в моем кейсе использовать. Так как у меня в 50+ контроллерах переодически используется $this->em->getConnection()->update(...), то есть \Doctrine\DBAL\Connection

[BoShurik]

TCloud, У вас же в вопросе идет речь о нативном SQL: "Как сделать нативный SQL запрос для UPDATE с WHERE IN условием Doctrine 2?"

[Алексей Юдов]

BoShurik, да, но это чисто для SEO :)

[Алексей Юдов]

BoShurik, это не DQL, там почти напрямую через PDO SQL запрос вызывается )

[BoShurik]

"Хочешь получить ответ, запости неправильный" в действии :)

[Алексей Юдов]

BoShurik, не понимаю о чем вы :)

Answer 2

[BoShurik]

Doctrine это умеет из коробки

Можно через DBAL с помощью обычного SQL (это именно то, что спрашивается в вопросе)

$ids = [1, 2, 3, 4];

$this->getEntityManager()->getConnection()
    ->executeStatement('UPDATE some_table SET some_field = ? WHERE id IN (?)', [
        'value',
        $ids,
    ], [
        1 => Connection::PARAM_INT_ARRAY,
]);

Можно через QueryBuilder

$builder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$builder
    ->update('some_table')
    ->set('some_field', ':value')
    ->where($builder->expr()->in('id', ':ids'))
    ->setParameter('value', 'value')
    ->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY)
;

$builder->execute()

Если все-таки используется DQL и ORM, то можно и через нее:

$this->getEntityManager()
    ->createQuery('UPDATE App\Entity\SomeEntity se SET se.someValue = :value WHERE se.id IN (:ids)')
    ->execute(new ArrayCollection([
        new Parameter('value', 'value'),
        new Parameter('ids', $ids),
    ]))
);

Либо через QueryBuilder

$builder = $this->createQueryBuilder('se');
$builder
    ->update()
    ->set('se.someValue', ':value')
    ->where($builder->expr()->in('uu.id', ':ids'))
    ->setParameter('value', 'value')
    ->setParameter('ids', $ids)
;

$builder->getQuery()->execute();

Comments

[Алексей Юдов]

some_field будет определен если в вашей сущности есть такое поле. В моем случае его нет, есть только линка на другую сущность, но её ID хранится в some_table.some_field_id. Также в моем случае при использовании вашего кода у меня будет ошибка has no field or association named

[BoShurik]

TCloud, вы уж определитесь, вы про DQL или SQL? Судя по комментариям в вашем ответе - речь про SQL. Тогда откуда конвертация some_field => some_field_id, если это доступно только для DQL?

[BoShurik]

TCloud, Если речь о DQL, то все еще проще:

// EntityRepository
$builder = $this->createQueryBuilder('st');
$builder
    ->update()
    ->set('st.someField', ':value')
    ->where($builder->expr()->in('st.id', ':ids'))
    ->setParameter('value', 1)
    ->setParameter('ids', $ids)
;

При этом в коде сущности может быть:

// Entity
/**
 * @ORM\Column(type="string", name="some_another_field")
 */
private string $someField;