No support for multi INSERT with ON DUPLICATE KEY UPDATE clause

[dakujem]

Version: 5.0.2, 4.x

Multiple insert is documented and works as follows:

$db->query(
	'INSERT INTO users',
	[
		'name' => 'Jim',
		'year' => 1978,
	],
	[
		'name' => 'Jack',
		'year' => 1987,
	]
);

But once one needs to add something after the values, Dibi offers no support.
Most commonly ON DUPLICATE KEY UPDATE clause.

Take this collection of updates

    $updates = [];
    $updates[] = [
        'id' => 123,
        'value' => 'aaaa',
    ];
    $updates[] = [
        'id' => 345,
        'value' => 'Boo',
    ];
    $updates[] = [
        'id' => 1_678,
        'value' => rand(),
    ];

To INSERT it, it's pretty simple:

    $db->test(
        'INSERT INTO %n', 'foobar', 
        ...$updates, 
    );

That's it. Brilliant. Here is the result:

INSERT INTO `foobar` (`id`, `value`)
VALUES (123, 'aaaa') , (345, 'Boo') , (1678, 1659064768)

To UPSERT this data, though, one needs to use one of the following:

// Use the spread operator and array_merge
    $db->test(
        'INSERT INTO %n', 'foobar',
        ...array_merge($updates, [
            'ON DUPLICATE KEY UPDATE',
            '`value` = VALUES(`value`)',
        ]),
    );

// Construct the query manually using array_keys and array_map
    $db->test(
        'INSERT INTO %n', 'foobar',
        '(%n)', array_keys($updates[array_key_first($updates)]),
        'VALUES', array_map(fn(array $row) => new Expression('%l', $row), $updates),
        'ON DUPLICATE KEY UPDATE',
        '`value` = VALUES(`value`)',
    );

Both are non-trivial and look ugly compared to other Dibi queries.

Both produce the expected result:

INSERT INTO `foobar` (`id`, `value`)
VALUES (123, 'aaaa'), (345, 'Boo'), (1678, 1659064768)
ON DUPLICATE KEY
UPDATE `value` = VALUES(`value`)

Yes, one can abstract a helper function to faciliate it, but still this feels like a missing feature in Dibi.

    $makeMultiInsertExpression = fn(array $values) => new Expression(
        '(%n)', array_keys($values[array_key_first($values)]),
        'VALUES', array_map(fn(array $row) => new Expression('%l', $row), $values),
    );
    $db->test(
        'INSERT INTO %n', 'foobar',
        $makeMultiInsertExpression($updates),
        'ON DUPLICATE KEY UPDATE',
        '`value` = VALUES(`value`)',
    );

Furthermore, array modifier %m (multi) does not work as one would expect and is frankly useless:

    $db->test(
        'INSERT INTO %n', 'foobar',
        '%m', $updates,
        'ON DUPLICATE KEY UPDATE',
        '`value` = VALUES(`value`)',
    );

result:

INSERT INTO `foobar` (`x`, `y`, `z`)  // **
VALUES (123, 345, 1678), ('aaaa', 'Boo', 685396701) // props grouped together...
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)

The modifier groups individual indexes as rows, which is useless in a real app.
If I need to transpose the data prior to a query, then it's easier to just go with one of the other approaches I mention above.

** For the record, I needed to add indexes x, y, z to the data collection for this example just to work.

I did expect the %m modifier do what I now need to do manually.

I propose another modifier like %mv ("multiple values") that would do what $makeMultiInsertExpression does, what %m should have been doing in the first place.