Mass assignment issue

Question

I have a form with a lot of fields and each of them has to be added in a table as a different row.

My table looks like this:

| Category | Device |       Value |
|----------|:-------|-------------|
| 2        |    1   |  some value |
| 3        |    1   | other value |
| 7        |    3   |         etc |

The Category and Device are actually foreign keys from the Categories and Devices tables. Also they should be unique, meaning that there can't be Category: 2 and Device: 1 twice. If they exists already, the value should be updated.

The categories and value are retrieved from the form and it looks like this:

{"2":"some value","3":"other value","5":"etc","6":"something","8":"can be empty"}

The device also comes from the form but it will be the same.

Now I need to enter everything in my database and I'm looking for a simple solution.

But it will do about 100 queries (one for each input) and I'm sure there must be a better solution.

If one of the values that comes from the form is empty, it should be ignored.

Here's my currently working code, maybe you can understand better:

public function postSpecs(Request $request)
    {
        $specs = $request->except(['_token', 'deviceid']);

        foreach($specs as $key=>$val)
        {
            if($val == '') continue;
            if(Spec::where('category', $key)->where('device', $request->deviceid)->exists())
            {
                $spec = Spec::where('category', $key)->where('device', $request->deviceid)->first();
                $spec->value = $val;
                $spec->save();
            }
            else 
            {
                $spec = new Spec;
                $spec->category = $key;
                $spec->device = $request->deviceid;
                $spec->value = $val;
                $spec->save();
            }
        }
    }

Answer 1

use the insert method like:

$model->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

See also: http://laravel.com/docs/5.1/queries#inserts

EDIT:

Updated to your code:

public function postSpecs(Request $request)
{
    $specs = $request->except(['_token', 'deviceid']);

    $data = array();
    foreach($specs as $key=>$val)
    {
        if($val == '') continue;
        if(Spec::where('category', $key)->where('device', $request->deviceid)->exists())
        {
            $spec = Spec::where('category', $key)->where('device', $request->deviceid)->first();
            $spec->value = $val;
            $spec->save();
        }
        else
        {
            $data[]['category'] = $key;
            $data[]['device'] = $request->deviceid;
            $data[]['value'] = $val;
        }
    }

    Spec::insert($data);
}

While this is not perfect, it will save you a lot queries. Else you have to use raw query something like (untested!):

INSERT INTO spec (id,category,device,value) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE id=LAST_INSERTED_ID(id)

Answer 2

A little more information on the on duplicate key update method because I think it deserves to be a possible solution if this is something that's going to happen a lot...

First, you would need to create the unique key. This will force those columns to be unique. I'd highly suggest adding the unique key no matter how you handle the inserts. It may help preserve your sanity in the future.

In a migration, you would do...

$table->unique(['Category', 'Device']);

Or in plain sql...

alter table category_device add unique index unique_category_device (Category, Device);

Now to insert into the table, you would simply use the query...

insert into category_device (Category, Device, Value) values ($category_id, $device_id, $value)
    on duplicate key udpate Value = VALUES(Value)

If it's not a duplicate entry, mysql will simply insert the new record. If it is a duplicate, mysql will update the value column with whatever you tried to insert as the new value. This is very performance friendly as you will not be required to check for the existence of duplicates before trying to do the insert as it will simpy act like an update statement in the event there is a duplicate. The drawback here is laravel does not support on duplicate key update so you would need to actually write the SQL. I would suggest you still use data bindings though which can make it somewhat easier.

$sql = "insert into category_device (`Category`, `Device`, `Value`) values (:category_id, :device_id, :value)
            on duplicate key udpate Value = VALUES(Value)";

$success = \DB::insert($sql, [
    'category_id' => $category_id,
    'device_id' => $device_id,
    'value' => $value
]);

Also please note, if you have over 100 entries, you could loop through them and keep adding to the query and it should work just the same...

insert into category_device (`Category`, `Device`, `Value`) 
    values 
(1, 1, 'some value'), (1, 2, 'some other value'), (1, 3, 'third value')...
    on duplicate key udpate Value = VALUES(Value)";

If you are using this method, I would probably not worry about the data binding and just insert the values right into the query. Just make sure they are properly escaped beforehand.

If you had a hundred items to insert, which would be 200 queries (one for checking the existence of the record, and another for inserting/updating), this would turn that 200 queries into 1 query which obviously would be a huge performance gain.