Skip rows that are not in Laravel relation table

0
votes

I'm having a problem skip row import excel / csv Laravel to sql database. There are two relation tables, namely the Brand Table and the Item Table. I use maatwebsite-excel.

Brand Table:
id  brand       desc
1   AA      details AA
2   AB      details AB
3   AC      details AC

Item Table
id  date        brand_id        buyer
1   09-02-21        3       Andy
2   09-02-21        1       Jhon
3   09-02-21        2       Daniel

Here is a file excel / csv for import that contains header:

(date)  (brand) (buyer)
10-02-21    AC  Reimon
10-02-21    AC  David
10-02-21    AB  Michel
10-02-21    ZA  Susy
10-02-21    AA  Edgar
10-02-21    NK  Rhein

Only the brands in the Brands Table are saved to the database. Brands that do not exist will be skipped. It's this type of data we want:

(date)  (brand) (buyer)
10-02-21    AC  Reimon
10-02-21    AC  David
10-02-21    AB  Michel
10-02-21    AA  Edgar

This my ItemImport.php

class ItemImport implements ToCollection, WithCalculatedFormulas, WithChunkReading, ShouldQueue, WithCustomCsvSettings
{
    public function collection(Collection $collection)
    {

    $collection = $collection->toArray();
    //dd($collection);

        foreach ($collection as $key => $row){
            if($key >= 0){

                $brand= Brand::where("brand","like","%".$row['1']."%")->first();
                $row['1'] = $brand->id;
                 
                Item::create([
                    'date' => $row['0'],
                    'brand_id' => $row['1'], 
                    'buyer' => $row['3']
                ]);
                           
            }
        }
    }
}

In the code, it turns out that there is no skip row function, so all rows are entered into the database. Even though I want to get rid of the following data:

(date)  (brand) (buyer)
10-02-21    ZA  Susy
10-02-21    NK  Rhein

Thank you very much to you who are willing to answer my difficulty. :)

2
laravelimportphpexcelmaatwebsite-excel

2 Answers

0
votes

You can simply put a check for the items you want to exclude the insertion into DB.

class ItemImport implements ToCollection, WithCalculatedFormulas, WithChunkReading, ShouldQueue, WithCustomCsvSettings
{
    public function collection(Collection $collection)
    {

    $collection = $collection->toArray();
    //dd($collection);

        foreach ($collection as $key => $row){
            if($key >= 0 && $row['1']!='ZA' && $row['1']!='NK'){

                $brand= Brand::where("brand","like","%".$row['1']."%")->first();
                $row['1'] = $brand->id;
                 
                Item::create([
                    'date' => $row['0'],
                    'brand_id' => $row['1'], 
                    'buyer' => $row['3']
                ]);
                           
            }
        }
    }
}
0
votes

Hope this will work for you, try it.

class ItemImport implements ToCollection, WithCalculatedFormulas, WithChunkReading, 
ShouldQueue, WithCustomCsvSettings
{
    public function collection(Collection $collection)
    {

    $collection = $collection->toArray();
    //dd($collection);
    $array_of_names_excluded=['ZA', 'NK'];

        foreach ($collection as $key => $row){
            if($key >= 0){

                $brand= Brand::whereNotIn("brand", $array_of_names_excluded)
                        ->where("brand","like","%".$row['1']."%")->first();

                if($brand){
                    $row['1'] = $brand->id;
                 
                    Item::create([
                        'date' => $row['0'],
                        'brand_id' => $row['1'], 
                        'buyer' => $row['3']
                    ]);
                }           
            }
        }
    }
}