updateFields example


  public function testMergeUpdateExcept() {
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();

    $this->connection->merge('test_people')
      ->key('job', 'Speaker')
      ->insertFields(['age' => 31])
      ->updateFields(['name' => 'Tiffany'])
      ->execute();

    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
    $this->assertEquals($num_records_before$num_records_after, 'Merge updated properly.');

    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
    $this->assertEquals('Tiffany', $person->name, 'Name set correctly.');
    $this->assertEquals(30, $person->age, 'Age skipped correctly.');
    $this->assertEquals('Speaker', $person->job, 'Job set correctly.');
  }

  

                    }
                }
            }

            $alias = $this->QBOptions['alias'] ?? '"excluded"';

            if (strtolower($alias) !== '"excluded"') {
                throw new InvalidArgumentException('Postgres alias is always named "excluded". A custom alias cannot be used.');
            }

            $updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ?? [];

            $sql = 'INSERT INTO ' . $table . ' (';

            $sql .= implode(', ', $keys);

            $sql .= ")\n";

            $sql .= '{:_table_:}';

            $sql .= 'ON CONFLICT(' . implode(',', $constraints) . ")\n";

            
if (empty($constraints)) {
                if ($this->db->DBDebug) {
                    throw new DatabaseException('No constraint found for upsert.');
                }

                return ''; // @codeCoverageIgnore             }

            $alias = $this->QBOptions['alias'] ?? '"_upsert"';

            $updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ?? [];

            $sql = 'MERGE INTO ' . $fullTableName . "\nUSING (\n";

            $sql .= '{:_table_:}';

            $sql .= ") {$alias} (";

            $sql .= implode(', ', $keys);

            $sql .= ')';

            
$constraints = $this->QBOptions['constraints'] ?? [];

            if ($constraints === []) {
                if ($this->db->DBDebug) {
                    throw new DatabaseException('You must specify a constraint to match on for batch updates.'); // @codeCoverageIgnore                 }

                return ''; // @codeCoverageIgnore             }

            $updateFields = $this->QBOptions['updateFields'] ??
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
                [];

            $alias = $this->QBOptions['alias'] ?? '`_u`';

            $sql = 'UPDATE ' . $this->compileIgnore('update') . $table . "\n";

            $sql .= "INNER JOIN (\n{:_table_:}";

            $sql .= ') ' . $alias . "\n";

            $sql .= 'ON ' . implode(
                
return ''; // @codeCoverageIgnore             }

            $alias = $this->QBOptions['alias'] ?? '`excluded`';

            if (strtolower($alias) !== '`excluded`') {
                throw new InvalidArgumentException('SQLite alias is always named "excluded". A custom alias cannot be used.');
            }

            $updateFields = $this->QBOptions['updateFields'] ??
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
                [];

            $sql = 'INSERT INTO ' . $table . ' (';

            $sql .= implode(', ', array_map(static fn ($columnName) => $columnName$keys));

            $sql .= ")\n";

            $sql .= '{:_table_:}';

            $sql .= 'ON CONFLICT(' . implode(',', $constraints) . ")\n";

            

    protected function _upsertBatch(string $table, array $keys, array $values): string
    {
        $sql = $this->QBOptions['sql'] ?? '';

        // if this is the first iteration of batch then we need to build skeleton sql         if ($sql === '') {
            $updateFields = $this->QBOptions['updateFields'] ?? $this->updateFields($keys)->QBOptions['updateFields'] ?? [];

            $sql = 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ")\n{:_table_:}ON DUPLICATE KEY UPDATE\n" . implode(
                ",\n",
                array_map(
                    static fn ($key$value) => $table . '.' . $key . ($value instanceof RawSql ?
                        ' = ' . $value :
                        ' = VALUES(' . $value . ')'),
                    array_keys($updateFields),
                    $updateFields
                )
            );

            
$constraints = $this->QBOptions['constraints'] ?? [];

            if ($constraints === []) {
                if ($this->db->DBDebug) {
                    throw new DatabaseException('You must specify a constraint to match on for batch updates.');
                }

                return ''; // @codeCoverageIgnore             }

            $updateFields = $this->QBOptions['updateFields'] ??
                $this->updateFields($keys, false, $constraints)->QBOptions['updateFields'] ??
                [];

            $alias = $this->QBOptions['alias'] ?? '"_u"';

            // Oracle doesn't support ignore on updates so we will use MERGE             $sql = 'MERGE INTO ' . $table . "\n";

            $sql .= "USING (\n{:_table_:}";

            $sql .= ') ' . $alias . "\n";

            
Home | Imprint | This part of the site doesn't use cookies.