CODEWITHSUNDEEP
mysqlmodellaravellaravel-4eloquent
Bryan P
Bryan P

Reputation: 4202

Eloquent doesn't update all records in MySQL

What I want to do is update all of the records in field book_id of TableB to be equal to TableA book_id. But it only updates a few records, 19 out of 7862 to be precise.

I did this many times hoping that consequent 19 record updates will eventually reach 7862 but to my bewilderment it's still 19.

TableA:
book_id
ibooks_id -> equal to TableB.book_id

TableB:
book_id -> change to TableA.book_id

Codes I tried:

$books = TableAModel::join("TableB", "TableA.ibooks_id", '=', "TableB.book_id")
                    ->update(array("TableB.book_id" => "TableA.book_id"));

and also:

$tableBItems = TableBModel::all();
    TableBModel::unguard();
    foreach($tableBItems as $tableBitem) {
        $TableAItem = TableAModel::where('ibooks_id',$tableBitem->book_id)->first();
        if(isset($TableAItem->book_id)) {
            $tableBitem->book_id = $TableAItem->book_id;
            $tableBitem->save();
        }
    }

Do you have a better approach to this? Or maybe this can be done via pure mysql query, thanks.

Models:

class TableBModel extends Eloquent {

    public $table = 'TableB';
    public $timestamps = false;
}
class TableAModel extends Eloquent {

    protected $table = 'TableA';
    public $primaryKey = 'book_id';
}

Upvotes: 1

Views: 197

Answers (2)

Phrancis
Phrancis

Reputation: 2282

I say a good practice working with any kind of RDBMS/SQL is to let the SQL engine do as much of the SELECT, INSERT, UPDATE and DELETE as possible as it is highly optimized for this type of work. If you are planning to perform the same operation multiple times within your application, just write it into a procedure in MySQL as such example:

CREATE PROCEDURE sp_changeTableB_book_id();
DELIMITER //
BEGIN

-- you can leave out the code above if running ad-hoc query, but replace // below with ;

UPDATE TableB INNER JOIN TableA
ON TableB.book_id = TableA.ibooks_id
SET TableB.book_id = TableA.book_id//

-- you can leave out the code below if running ad-hoc query

END//
DELIMITER ;

Then when you want to use it (if not ad-hoc) just code in Eloquent to MySQL CALL sp_changeTableB_book_id

You may be interested in this post on Code Review to see some of the advantages of doing this using a stored procedure in SQL.

Upvotes: 1

adib.mosharrof
adib.mosharrof

Reputation: 1634

FIrst you need to check the rows that your query is returning. So rather than updating it directly, first do a select * to count the number of rows being returned, because these rows will be updated.

The sample is oin("TableB", "TableA.ibooks_id", '=', "TableB.book_id")

Select * from tableA join tableB on tableA.ibooks_id = TableB.book_id

Using this query you will be able to get the number of rows where your join clause is working.

Also the second approach that you tried, is extremely expensive, you are getting all the rows, then again you are running a for loop over all the rows, in which in every iteration you are performing another DB query.

Upvotes: 0

Related Questions