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

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.

Ignoring batchSize.
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

What about updateBatch()?

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)

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);

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

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

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 #6294 or wait and submit a separate PR

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 #6294 or wait and submit a separate PR

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

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.

Closed by #6536

Navigation Menu

Repository navigation

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

PHP Version

CodeIgniter4 Version

CodeIgniter4 Installation Method

Which operating systems have you tested for this bug?

Which server did you use?

Database

What happened?

Steps to Reproduce

Expected Output

Anything else?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

PHP Version

CodeIgniter4 Version

CodeIgniter4 Installation Method

Which operating systems have you tested for this bug?

Which server did you use?

Database

What happened?

Steps to Reproduce

Expected Output

Anything else?

Activity

kenjis commented on Feb 10, 2022

kenjis commented on Feb 10, 2022

iRedds commented on Feb 10, 2022

iRedds commented on Feb 10, 2022

iRedds commented on Feb 10, 2022

sclubricants commented on Jul 29, 2022

kenjis commented on Jul 29, 2022

sclubricants commented on Jul 30, 2022

kenjis commented on Sep 27, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions