Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

This logic would need to be applied manually since this logic does not exist in the query builder (or at least I couldn't find it). You'd need to go through, check the existence of a particular id and either insert or update the record as needed.

Last updated 2 years ago.
0

Doing it one by one is easy, I can simply use eloquent and it will solve it.

But I nead it in one query and one foreach

Last updated 2 years ago.
0

There are methods called firstOrCreate (save model) and firstOrNew (create but dont save) but those are eloquent methods and I don't think they will exactly solve your problem. Are you trying to find by ID only? Or is the ID + Temp pair important? Or are you finding by just temp? If you are querying by temp then you can use the firstOrCreate method:

Model::firstOrCreate(array('temp' => 1));
Last updated 2 years ago.
0

i think he's looking for something like codeigniters active records insert_batch() and update_batch()

i have been wondering the same thing myself!

Last updated 2 years ago.
0

@keevitaja can you explain the problem in more detail? I'm unfamiliar with codeigniter

Last updated 2 years ago.
0

I have several rows on a table I would like to insert/update when exists.

this is the table: item_id, key, val

the primary key is ( item_id, key)

now I would like to insert a bulk of rows at one query, but if one of the rows has an exiting row, i would like only to update the "val" column.

like this:

insert into table (item_id, key, val) values(1, "A", 19) on duplicate key update val=values(val)

if I wouldn't find a solution I would simply run a raw query, but then I should handle the security of the data, from sql injections.

Last updated 2 years ago.
0

now I use this method:

 foreach ( $sqlInsert as $row ) {
    
        DB::insert(
            'insert into `wedding_data` (`wedding_id`, `key`, `val`) values (?, ?, ?)
            on duplicate key update `val`=values(`val`)',
            array($row['wedding_id'], $row['key'], $row['val'])
        );

    }

this works just fine, just it perform one query for each row, and If I have 20 rows, I'll perform 20 queries instead of one.

Last updated 2 years ago.
0

Anyone please?

Last updated 2 years ago.
0

Did anyone come up with a solution for this, I am also looking to do the same.

When it's 20 rows, it's not great, but when you are needing to deal with potentially thousands of rows (or in my case tens of thousands), this is beyond bad.

At the moment I am using a raw sql query, then, as I need the objects, having to do a query after the insert so that I can get the updated objects back.....

Last updated 2 years ago.
0

Of course you can foreach, but each iteration will perform a sql query...... When you have in excess of tens of thousands of rows, thats just asking for trouble.

Last updated 2 years ago.
0
$sqlInsert = array(
    array('id'=>1, 'temp'=>1),
    array('id'=>2, 'temp'=>2),
    array('id'=>3, 'temp'=>3),
);

           // prepare one query
           $q = "UPDATE table_name SET temp= CASE id ";

           foreach($sqlInsert as $insert) {
               $q .= "WHEN '".$insert['id']."' THEN ".$insert['temp']." ";
           }

           $q .= "END WHERE id IN (";

           foreach($sqlInsert as $insert) {
                $q .= "'".$insert['id']."', ";
           }
           $q = rtrim($q, ", ");
           $q .= ")";
           
           // Update  
           DB::update(DB::raw($q));
Last updated 9 years ago.
0

akashvarlani said:

$sqlInsert = array(
   array('id'=>1, 'temp'=>1),
   array('id'=>2, 'temp'=>2),
   array('id'=>3, 'temp'=>3),
);

          // prepare one query
          $q = "UPDATE table_name SET temp= CASE id ";

          foreach($sqlInsert as $insert) {
              $q .= "WHEN '".$insert['id']."' THEN ".$insert['temp']." ";
          }

          $q .= "END WHERE id IN (";

          foreach($sqlInsert as $insert) {
               $q .= "'".$insert['id']."', ";
          }
          $q = rtrim($q, ", ");
          $q .= ")";
          
          // Update  
          DB::update(DB::raw($q));

could you explain your code ? thanks

0

For mass inserts you can do :

$input = array(....);
$data = [];       // $data is an array of arrays representing a series of row x columns to be inserted

foreach($input as $v) 
{
      $data[] = array(
    'row1' => $v[1], 
    'row2' => $v[2], 
    'row3' => $v[3], 
    'row4' => $v[4], 
    'row5' => $v[5], 
    'row6' => $v[6]);
}

Changes::insert($data);   // Changes is a eloquent model representing a table named 'change'

For mass updates :

Changes::updateOrCreate(array('search_key' => 'search_value'), array('key' => 'value'));
Last updated 8 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

tzookb tzookb Joined 9 Feb 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.