How to perform a update operation in loop if there is no other option in laravel or any platform ?

Level 1

If I got it right why don't you just increment the counter on each transaction success ?

Level 2

@aliabdm those columns are not in same table sir one is transaction table one is bank table

so if i increment my counter on every transaction i guess it would be more worse as there may be millions of transactions in a day .

apart from that my concern was not how to do this accurately , but how to do some query or db related stufff when it comes to do in loops

what it the best practice.

aliabdm

Level 1

@rohansinghrawat yea I know , still doing it at the same function will be way more better approach , you dont need this schedule job

Best Answer

Level 5

@rohansinghrawat If your table bank_summary has the column bank_iin as primary or unique index, you can use upserts https://laravel.com/docs/9.x/queries#upserts and do that in one query.

You cannot avoid the loop but you can (and should) avoid the multiple queries

if ($aepsRecords->count()) {
	$records = [];

	foreach ($aepsRecords as $record) {
		$records[] = [
			'bank_iin' => $record->bankiin,
			'aeps_preference' => $record->count
		];
	}

	DB::transaction(function () use ($records) {
		DB::connection('ipay_app_bc__write')
			->table('flights')
			->upsert($records);
	});
}

1 like

Level 2

@achatzi yes upserts was in my mind too but what if there is no unique index because I have seen upserts doesn't works properly until it gets any unique index or primary keys related stuff

Level 5

@rohansinghrawat Then I don't think there is a way to do something like this.

Even if you tried raw sql

INSERT INTO bank_summary (bank_iin, aeps_preference) VALUES (1, 3), (2, 4), (3, 5) ON DUPLICATE KEY UPDATE aeps_preference = VALUES(aeps_preference)

you still need bank_iin to be unique and this is regardless of Laravel or any other framework.

Level 2

@achatzi I tried the same but i think it didn't worked properly I have also made my bank_iin column unique but something is wrong here see this is my query

 $updateRecord = [];
        foreach ($records as $value) {
            $updateRecord[] = [
                'bank_iin' => $value['bank_iin'],
                'aeps_preference' => $value['count'],
            ];
        }

        // dd($updateRecord);
        DB::enableQueryLog();
        $isUpdated = DB::table('ipay_app_bc.bank_summary')
            ->upsert($updateRecord, ['bank_iin'], ['aeps_preference']);

        dd($isUpdated);

but isUpdated is 0

Level 5

@rohansinghrawat From here https://github.com/laravel/framework/discussions/34979 it seems that what upsert returns is DB dependant.

In any case, check your data before and after upsert, to see if the update was done correctly.

For example, I made a small test and run this code

$data = [];
for ($i = 1; $i <= 5; $i++) {
    $data[] = [
        'bank_id' => $i,
        'amount' => rand(100, 1000),
    ];
}

$updated = app('db')
    ->table('bank_state')
    ->upsert($data, 'bank_id');

dd($updated);

The number I get is not always the same, sometimes is 10, sometimes is 9 or whatever. The data do get updated though each time.

1 like

Level 2

@achatzi hmm that helped me and solved my problem and thanks for your effort . I would really appreciate this .

But there is one thing which are missing and that thing is the discussion's main topic the solution you gave me would help me in this scenario but what if sometimes or someday there may not have any unique keys related stuff and I would be bound to use such queries in loop so in that case what would be the best solution to tackle multiple queries in loop as I have learned so far that running queries in loop is nothing but a curse for database .

Level 5

@rohansinghrawat I don't think that there is way to do that if you don't have a primary key or unique index.

The only thing that I can think of, is group the queries by values and make less updates.

For example, assuming you will need to run these queries

UPDATE some_table SET some_column = 10 WHERE some_other_column = 1;
UPDATE some_table SET some_column = 10 WHERE some_other_column = 2;
UPDATE some_table SET some_column = 11 WHERE some_other_column = 3;
UPDATE some_table SET some_column = 14 WHERE some_other_column = 4;
UPDATE some_table SET some_column = 10 WHERE some_other_column = 5;
UPDATE some_table SET some_column = 11 WHERE some_other_column = 6;

You could change that to

UPDATE some_table SET some_column = 10 WHERE some_other_column IN (1, 2, 5);
UPDATE some_table SET some_column = 11 WHERE some_other_column IN (3, 6);
UPDATE some_table SET some_column = 14 WHERE some_other_column IN (4);

Now you have less queries but it would still be more than one.

Furthermore, I really don't think that having a table with no primary key or unique index is a good idea when so many updates would happen to it.

1 like

Level 2

@achatzi yes sir what you are suggesting is totally okay but it somehow points that we don't have much control over looping queries as I was looking for any best way to reduce loops and do in a single query . One more thing I have in mind -> is transaction related to db connection ? I mean the code which i have posted is from someone else and according to him this code will make a connection to db once and all the queries will run under that same connection

DB::transaction(function () use ($aepsRecords) {
                foreach ($aepsRecords as $record) {
                    DB::connection('ipay_app_bc__write')
                        ->table('bank_summary')
                        ->where('bank_iin', $record->bankiin)
                        ->update(['aeps_preference' => $record->count]);
                }
            });

so Do you have any ideas about this ?

Level 5

@rohansinghrawat Do be honest I am not sure, but either way you will still make multiple queries.

The transaction makes sure that if one of those queries fails for some reason, no changes will be made to the database (everything will be rolled back).