14
$cardQueryList = [];
foreach($cards as $cardName => $quantity) {
    $cardQueryList[] = [
        'username' => $user->username,
        'card_uid' => $card->uid,
        'have_quantity' => $quantity
    ];
}

Collection::insert($cardQueryList);

The above code creates new rows even if the row exists. How can I make it so if the row exists, it updates. And if it doesn't it creates the row? An Eloquent or Fluent answer would be optimal but I'm open to raw if there's no other way.

I would like to do a mass update/insert with a single query. Not a for loop of queries for every record. Ideally I'd like to hit the database once for obvious reasons.

Also I've already checked the following link:

Insert a new record if not exist and update if exist, laravel eloquent

The above works for a single record update/insert. Which if I ran with a for loop would be very slow. I'm looking for an answer that allows a mass insert/update in a single query.

Note: I'm using, both 'username' and 'card_uid' as my key. So basically when I find a row with said username and card_uid, I'd like to update the corresponding row. Otherwise create a new row.

Community
  • 1
  • 1
Howard
  • 3,648
  • 13
  • 58
  • 86
  • have u any idea how to do this with raw-sql? not eloquent or querybuilder – M0rtiis Jul 25 '15 at 15:55
  • @M0rtiis: That's a good question. It would take me a bit to figure out. MySQL isn't my forte. – Howard Jul 25 '15 at 15:58
  • you should read this https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html – M0rtiis Jul 25 '15 at 15:59
  • @M0rtiis: Thanks for the link. I'll check it out. – Howard Jul 25 '15 at 16:00
  • @M0rtiis: One reason that I'm trying to go through Eloquent and/or Fluent is because it's much harder to do an injection attack because Laravel checks to see if the values work. – Howard Jul 25 '15 at 16:05
  • i think there is no eloquent method to do that updateOrCreate is for one row i think. – Mustafa ASAN Aug 03 '15 at 00:44
  • You can just use `Eloquent::insert()` http://stackoverflow.com/questions/12702812/bulk-insertion-in-laravel-using-eloquent-orm – a45b Aug 03 '15 at 18:25

4 Answers4

5

Typically the sort of sql you would be using would be something along the lines of the following:-

    insert into `TABLE` ( `FIELD1`,`FIELD2`, `FIELD3` ) values ( 'VALUE1','VALUE2','VALUE3' )
    on duplicate key
        update
            `FIELD1`='VALUE1',
            `FIELD2`='VALUE2',
            `FIELD1`='VALUE3';

How you would use this with laravel I couldn't tell you! Oops - forgot the field names in the update part

Professor Abronsius
  • 33,063
  • 5
  • 32
  • 46
  • I appreciate the response though! Question, how would it check the key part of your code when I'm using, both 'username' and 'card_uid' as my key? So basically if a username with the card_uid exists? – Howard Jul 25 '15 at 16:03
  • 1
    This post might be of interest http://stackoverflow.com/questions/28078484/update-composite-key-on-duplicate-key – Professor Abronsius Jul 25 '15 at 16:06
4
<?php

$valuesArray = [];

foreach( $cards as $cardName => $quantity ) 
{
    $valuesArray[] = "('".$user->username."','".$card->uid."','".$quantity."')";   
}

$db->query 
(
    "INSERT INTO `TABLE` ( `username`,`card_uid`, `have_quantity` ) VALUES ".implode( ',', $valuesArray )."
    ON DUPLICATE KEY UPDATE `have_quantity` = VALUES(`have_quantity`);"
 );

Make sure you have a primary key on username and card_uid. Also don't forget to escape the values and to only run the query if $valuesArray is not empty.

vinz
  • 566
  • 3
  • 11
0

If you don't want duplicates have your DB schema prevent the entry of duplicate entries. Catch the error in this case and just continue on.

Sieabah
  • 954
  • 10
  • 11
0

You may try this:

Collection::firstOrNew([
    'username' => $user->username,
    'card_uid' => $card->uid,
    'have_quantity' => $cards['quantity']
])
->save();

To make it working, add $fillable property with mass assignable field names in your Collection model, for example:

protected $fillable = ['username', 'card_uid', 'have_quantity'];
The Alpha
  • 143,660
  • 29
  • 287
  • 307
  • My model currently has $fillable set up like this: protected $fillable = ['username', 'card_uid', 'have_quantity', 'want_quantity']; I'm not exactly sure I'm following for the save() part though? Can you provide an example with 2 or more rows being filled? – Howard Jul 30 '15 at 20:44
  • can be mass operation? Seems to be single only – itsazzad Apr 28 '16 at 13:40