php - Swapping array values with Eloquent model IDs

552

I need to make an import method that takes the CSV file and imports everything in the database.

I've done the parsing with one of Laravel's CSV addons and it works perfectly giving me a big array of values set as:

[
    'col1_name' => 'col1 value',
    'col2_name' => 'col2 value',
    'col3_name' => 'col3 value,
    '...' => '...'
]

This is also perfect since all the column names fit my model which makes the database inserts a breeze.

However - a lot of column values are strings that i'd like to set as separate tables/relations. For example, one column contains the name of the item manufacturer, and i have the manufacturer table set in my database.

My question is - what's the easy way to go through the imported CSV and swap the strings with the corresponding ID from the relationship table, making it compatible with my database design?

Something that would make the imported line:

[       
    'manufacturer' => 'Dell',
]

into:

[       
    'manufacturer' => '32',
]

I know i could just do a foreach loop comparing the needed values with values from the relationship models but I'm sure there's an easier and more clean way of doing it.

286

Answer

Solution:

I don't think theres any "nice" way to do this - you'll need to look up each value for "manufacturer" - the question is, how many queries will you run to do so?

A consideration you need to make here is how many rows you will be importing from your CSV file.

You have a couple of options.

1) Querying 1 by 1

I'm assuming you're going to be looping through every line of the CSV file anyway, and then making a new model? In which case, you can add an extra database call in here;

$model->manufacturer_id = Manufacturer::whereName($colXValue)->first()->id;

(You'd obviously need to put in your own checks etc. here to make sure manufacturers exist)

This method is fine relatively small datsets, however, if you're importing lots and lots of rows, it might end up sluggish with alot of arguably unnecessary database calls.

2) Mapping ALL your Manufacturers

Another option would be to create a local map of all your Manufacturers before you loop through your CSV lines;

$mappedManufacturers = Manufacturer::all()->pluck('id', 'name');

This will make$mappedManufacturers an array of manufacturers that hasname as a key,id as a value. This way, when you're building your model, you can do;

$model->manufacturer_id = $mappedManufacturers[$colXValue];

This method is also fine, unless you have tens of thousands of Manufacturers!

3) Where in - then re-looping

Another option would be to build up a list of manufacturer names when looping through your CSV lines, going to the database with 1whereIn query and then re-looping through your models to populate the manufacturer ID.

So in your initial loop through your CSV, you can temporarily set a property to store the name of the manufacturer, whilst adding it to another array;

$models = collect(); $model->..... = ....; $model->manufacturer = $colXValue; $models->push($colXValue);

Then you'll end up with a collection of models. You then query the database for ONLY manufacturers which have appeared:

$manufacturers = Manufacturer::whereIn('name', $models->lists('manufacturer'))->get()->keyBy('name')->toArray();

This will give you array of manufacturers, keyed by theirname.

You then loop through your$models collection again, assigning the correct manufacturer id using the map;

$model->manufacturer_id = $manufacturers[$model->manufacturer];

Hopefully this will give you some ideas of how you can achieve this. I'd say the solution mostly depends on your use case - if this was going to be a heavy duty ask - I'd definitely Queue it and be tempted to use Option 1! :P

People are also looking for solutions to the problem: sql - Adding two times together in php when value is greater than 24

Source