Yiisoft\Db\Pgsql\QueryBuilder::oldUpsert()

Complexity

Conditions	14
Paths	109

Size

Total Lines	102
Code Lines	65

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    private function oldUpsert(string $table, $insertColumns, $updateColumns, array &$params = []): string
    {
        /** @var Constraint[] $constraints */
        [$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns(
            $table,
            $insertColumns,
            $updateColumns,
            $constraints
        );

        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }

        if ($updateNames === []) {
            /* there are no columns to update */
            $updateColumns = false;
        }

        /** @var Schema $schema */
        $schema = $this->db->getSchema();

        if (!$insertColumns instanceof Query) {
            $tableSchema = $schema->getTableSchema($table);
            $columnSchemas = $tableSchema !== null ? $tableSchema->getColumns() : [];
            foreach ($insertColumns as $name => $value) {
                /**
                 * NULLs and numeric values must be type hinted in order to be used in SET assigments NVM, let's cast
                 * them all
                 */
                if (isset($columnSchemas[$name])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value;
                    $insertColumns[$name] = new Expression("CAST($phName AS {$columnSchemas[$name]->getDbType()})");
                }
            }
        }

        [, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);
        $updateCondition = ['or'];
        $insertCondition = ['or'];
        $quotedTableName = $schema->quoteTableName($table);

        foreach ($constraints as $constraint) {
            $constraintUpdateCondition = ['and'];
            $constraintInsertCondition = ['and'];
            foreach ($constraint->getColumnNames() as $name) {
                $quotedName = $schema->quoteColumnName($name);
                $constraintUpdateCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
                $constraintInsertCondition[] = "\"upsert\".$quotedName=\"EXCLUDED\".$quotedName";
            }
            $updateCondition[] = $constraintUpdateCondition;
            $insertCondition[] = $constraintInsertCondition;
        }

        $withSql = 'WITH "EXCLUDED" (' . implode(', ', $insertNames) . ') AS ('
            . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ')';

        if ($updateColumns === false) {
            $selectSubQuery = (new Query($this->db))
                ->select(new Expression('1'))
                ->from($table)
                ->where($updateCondition);
            $insertSelectSubQuery = (new Query($this->db))
                ->select($insertNames)
                ->from('EXCLUDED')
                ->where(['not exists', $selectSubQuery]);
            $insertSql = $this->insert($table, $insertSelectSubQuery, $params);

            return "$withSql $insertSql";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }

        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);

        $updateSql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $updates)
            . ' FROM "EXCLUDED" ' . $this->buildWhere($updateCondition, $params)
            . ' RETURNING ' . $this->db->quoteTableName($table) . '.*';

        $selectUpsertSubQuery = (new Query($this->db))
            ->select(new Expression('1'))
            ->from('upsert')
            ->where($insertCondition);

        $insertSelectSubQuery = (new Query($this->db))
            ->select($insertNames)
            ->from('EXCLUDED')
            ->where(['not exists', $selectUpsertSubQuery]);

        $insertSql = $this->insert($table, $insertSelectSubQuery, $params);

        return "$withSql, \"upsert\" AS ($updateSql) $insertSql";
    }