Bug: [QueryBuilder] setInsertBatch() + insertBatch()

[iRedds]

PHP Version

7.4

CodeIgniter4 Version

dev

CodeIgniter4 Installation Method

Git

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

No response

What happened?

When passing data to the setInsertBatch() and insertBatch() methods.

1. Ignoring batchSize.
2. Duplicate field(s) in the query string.

Steps to Reproduce

        $db = db_connect();

        $set = [
            ['value' => 1,],
            ['value' => 2,],
            ['value' => 3,],
        ];

        $data = [
            ['value' => 4,],
            ['value' => 5,],
            ['value' => 6,],
        ];

        dd($db->table('test')->testMode()->setInsertBatch($set)->insertBatch($data, null, 1));

array (3) [
    0 => string (66) "INSERT INTO `db_test` (`value`, `value`) VALUES (1), (2), (3), (4)"
    1 => string (42) "INSERT INTO `db_test` (`value`) VALUES (5)"
    2 => string (42) "INSERT INTO `db_test` (`value`) VALUES (6)"
]

Expected Output

array (3) [
    0 => string (42) "INSERT INTO `db_test` (`value`) VALUES (1)"
    1 => string (42) "INSERT INTO `db_test` (`value`) VALUES (2)"
    2 => string (42) "INSERT INTO `db_test` (`value`) VALUES (3)"
    3 => string (42) "INSERT INTO `db_test` (`value`) VALUES (4)"
    4 => string (42) "INSERT INTO `db_test` (`value`) VALUES (5)"
    5 => string (42) "INSERT INTO `db_test` (`value`) VALUES (6)"
]

Anything else?

No response

[kenjis]

What about updateBatch()?

[kenjis]

Personally, I think it's enough if the following works:

$db->table('test')->testMode()->setInsertBatch($data)->insertBatch(null, null, 1)

$db->table('test')->testMode()->insertBatch($data, null, 1)

[iRedds]

What about updateBatch()?

$db->table('test')->testMode()->setUpdateBatch($set)->updateBatch($data, 'value', 1);
$db->table('test')->testMode()->setUpdateBatch($set)->updateBatch(null, 'value', 1);

//throws
// CodeIgniter\Database\Exceptions\DatabaseException : One or more rows submitted for batch updating is missing the specified index.
// CodeIgniter4\system\Database\BaseBuilder.php:2121

works only $db->table('test')->testMode()->updateBatch($data, 'value', 1);

[iRedds]

Situations can be different, so I think any valid behavior needs to be handled. After all, setInsertBatch() was added for something.

[iRedds]

I think in insertBatch() one can wrap $set in setInsertBatch(); string $value = '' in setInsertBatch() ??? for what?

[sclubricants]

I have a fix for this.

        $db = db_connect();

        $data = [
            ['id' => 1, 'value' => 11,],
            ['id' => 2, 'value' => 12,],
            ['id' => 3, 'value' => 13,],
        ];

        $moreData = [
            ['value' => 14, 'id' => 4,],
            ['value' => 15, 'id' => 5,],
            ['value' => 16, 'id' => 6,],
        ];

        $EvenMoreData = [
            ['value' => 17, 'id' => 7,],
            ['value' => 18, 'id' => 8,],
            ['value' => 19, 'id' => 9,],
        ];
        
        dd($db->table('test')->testMode()
            ->setInsertBatch($data)
            ->setInsertBatch($moreData)
            ->insertBatch($EvenMoreData, null, 1));

$db->table(...)->testMode()->setInsertBatch(...)->setInsertBatch(...)->insertBatch(...) array (9)
⇄0 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (1,11)"
⇄1 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (2,12)"
⇄2 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (3,13)"
⇄3 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (4,14)"
⇄4 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (5,15)"
⇄5 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (6,16)"
⇄6 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (7,17)"
⇄7 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (8,18)"
⇄8 => string (51) "INSERT INTO `db_test` (`id`, `value`) VALUES (9,19)"

I could add to https://github.com/codeigniter4/CodeIgniter4/pull/6294 or wait and submit a separate PR

[kenjis]

Why do you guys need to use both setInsertBatch() and insertBatch() with data set at a time? What's the benefit?

I think the following API is better:

$db->table('test')->testMode()
    ->setInsertBatch($data)
    ->setInsertBatch($moreData)
    ->insertBatch(1);

I could add to https://github.com/codeigniter4/CodeIgniter4/pull/6294 or wait and submit a separate PR

Please never add anything to it. It is already too big +1,165 −24.

[sclubricants]

Please never add anything to it. It is already too big +1,165 −24

It wasn't so many lines when my lines were kilometers long 😜

Looking at this gives me some good ideas of refactoring some things. I'll wait though on that.

[kenjis]

Closed by #6536