Can I merge an .sql file that runs insert into and a Laravel factory seeder that creates dummy data on 2 only columns?

Question

I have a factory file that creates dummy data for 2 columns: code & barcode. However, I wanted to run an .sql file that replaces data after I run the factory command. With that, I guess a merge will happen and might delete some records, or add new ones. Is that possible to merge the data instead. Like automatic mapping?

Here's the content of my .sql file:

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 23, 2021 at 12:14 PM
-- Server version: 10.1.28-MariaDB
-- PHP Version: 7.1.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_zachmatic`
--

--
-- Dumping data for table `products`
--

REPLACE INTO `products` (`id`, `code`, `name`, `description`, `unit`, `cost`, `srp`, `supplier`, `qty_on_hand`, `category`, `delivery_date`, `created_at`) VALUES
(12, 'P-824830', 'magnum-v', '45/90-17 MV-360 tube type tires only', 'Pieces', '', '1246', 'HH All Ventures', 3, 'Tires', '', ''),
(13, 'P-232033', 'magnum-v', '50/100-17 MV-360 tube type tire only', 'Select Product ', '', '1246', 'HH All Ventures', 1, 'Tires', '', ''),
(14, 'P-73032309', 'magnum-v', '60/90-17 MV-360 Tube Type Tire Only', 'Pieces', '', '1341', 'HH All Ventures', 0, 'Tires', '', ''),
(15, 'P-0022252', 'magnum-v', '45/90-17 MV-329 Tube Type with Tube', 'Pieces', '', '1120', 'HH All Ventures', 9, 'Tires', '', ''),
(16, 'P-323694', 'magnum-v', '50/100-17 MV-329 tube type with tube', 'Select Product ', '', '1120', 'HH All Ventures', 0, 'Tires', '', ''),
(17, 'P-023202', 'magnum-v', '60/90-17 MV-329 tube type with tube', 'Pieces', '', '1499', 'HH All Ventures', 0, 'Tires', '', ''),
(18, 'P-4729320', 'magnum-v', '70/90-17 MV-329 tube type with tube', 'Pieces', '', '1678', 'HH All Ventures', 0, 'Tires', '', ''),
(19, 'P-3020323', 'magnum-v', '120/70-13 MV-119C Tubeless', 'Pieces', '', '1829', 'HH All Ventures', 5, 'Tires', '', ''),
(20, 'P-3220830', 'magnum-v', '130/70-13 MV-119C tubeless', 'Pieces', '', '2062', 'HH All Ventures', 0, 'Tires', '', ''),

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products` DROP COLUMN `id`;
ALTER TABLE `products` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

--
-- Drop `code` column to delete old barcodes
-- then re-add `code` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `code`;
ALTER TABLE `products` ADD COLUMN code VARCHAR(255) AFTER id;

--
-- Drop `delivery_date` column to delete old column
-- then re-add `delivery_date` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `delivery_date`;
ALTER TABLE `products` ADD COLUMN delivery_date DATETIME;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Then my factory file:

<?php

namespace Database\Factories;

use App\Models\Product;
use Illuminate\Database\Eloquent\Factories\Factory;
use Picqer;

class ProductFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Product::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        $code = $this->faker->bothify('PHZM-##########');
        $barcode = $this->generateBarcode($code);
        return [            
            'code' => $code,
            'barcode' => $barcode,
        ];
    }

    private function generateBarcode($code_to_convert) {
        $generator = new Picqer\Barcode\BarcodeGeneratorHTML();
        $barcode = $generator->getBarcode($code_to_convert, $generator::TYPE_CODE_128, 1, 15);
        return $barcode;
    }
}

Any help is appreciated.

score 0 · Accepted Answer · answered Jul 13 '21 at 14:08

0

I guess you must create seeder for running this script file like below:

<?php

use Illuminate\Database\Seeder;

class SqlFileSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {

        $path = public_path('sql/File.sql');
        $sql = file_get_contents($path);
        DB::unprepared($sql);
    }

}

and inside another seeder that your factory calls, you run this seeder too after calling factory

<?php

use Illuminate\Database\Seeder;

class FooSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Product::factory()->count(20)->create();
        $this->call([SqlFileSeeder::class]);
    }

}

answered Jul 13 '21 at 14:08

alirezadp10

183
8

Thanks this works well! Just another question, is it possible to merge the saved data from this line: `Product::factory()->count(20)->create();` and data from this line: `$this->call([SqlFileSeeder::class]);` – Jumar Juaton Jul 14 '21 at 13:05
Like is there something like `factory()->count(20)->update();` – Jumar Juaton Jul 14 '21 at 13:36
you're welcome. this method does not exist in factory class so you may probably do this for yourself, but I recommend take it easy just it works. – alirezadp10 Jul 15 '21 at 07:28

Jumar Juaton · Answer 2 · 2021-07-16T12:03:37.037

Thank you @alirezadp10 for the direction! I've managed to solve this by following his answer as well as exporting the products table in phpmyadmin with Update queries instead of default Insert Into as custom.

Screenshot: Export as Custom

Then uncheck the following:

Add DROP TABLE / TRIGGER statement
Add CREATE VIEW statement

Screenshot: Uncheck these settings

Select Update on the Function to use when dumping data dropdown.

Screenshot: Select Update on the dropdown

Then press Go.

After that, I've just replaced the field values using some regex in Sublime Text 3. Link: Regular expression search replace in Sublime Text 2

And renamed the fields to my needs. For @alirezadp10 answer to work, I've run php artisan db:seed.

Can I merge an .sql file that runs insert into and a Laravel factory seeder that creates dummy data on 2 only columns?

2 Answers2