Level 53
@gacho create new CitiesTableSeeder add your query there and call it as the first seeder in DatabaseSeeder.
public function run()
{
$this->call(CitiesTableSeeder::class);
// other seeders
}
Feb 10, 2020
15
Level 2
Is there a way to storage a .sql file when seeding database in Laravel?
Now the only way for my site to work properly is to do things in this order. First I type php artisan migrate:fresh command, then I manually add that query (by copying from script) in sql tab in phpmyadmin, and then I type php artisan db:seed command. Now, is there a way for me to automatically populate cities table without having to manually add that query. So that when I enter php artisan migrate:fresh --seed command it would first erase all data then populate cities table from .sql file and then finish seeding other tables? Any help is appreciated. Here is my seeder currently.
UserSeeder.php
<?php
use App\City;
use App\User;
use App\Gender;
use Carbon\Carbon;
use Faker\Factory as Faker;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Hash;
/**
* Class UserSeeder
*/
class UserSeeder extends Seeder
{
use ChunkSeeder;
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$genders = DB::table('genders')->insert([
[
'genders' => 'Woman',
],
[
'genders' => 'Woman Looking For Woman',
],
[
'genders' => 'Man',
]
]);
$genderIds = Gender::pluck('id');
$cityIds = City::pluck('id');
$seed = [];
for ($i = 0, $n = 150; $i < $n; $i++) {
$seed[] = factory(User::class)->make(
[
'gender_id' => $genderIds->random(),
'city_id' => $cityIds->random(),
'email_verified_at' => Carbon::now(),
'premium_purchased_at' => Carbon::now(),
'mobile_verified_at' => Carbon::now(),
'mobile_verification_id' => rand(0, 20),
]
)->toArray();
}
$this->seedChunks($seed, [User::class, 'insert']);
}
}
Level 2
Yeah, I know but query has many lines. Could that be problem for performance or something similar or not?
Level 53
@gacho well, you're currently executing it through phpmyadmin, so that's basically the same thing. If you don't want to bloat your seeder class, export it to a dedicated file and then load it in your seeder.
Level 2
Aha ok. I never done that. Can you show me how to export it and then how to load?
Level 53
@gacho this should work if you place the cities.sql file (with your SQL query inside) in the same directory: DB::unprepared(file_get_contents(__DIR__ . '/cities.sql'));
Level 53
@gacho can you show the whole error?
Level 53
@gacho just simplify the query, take away most of the cities and leave just a few so that you would be able to see the first error or configure your terminal to show the full output.
Level 2
Also in my phpmyadmin it works when I insert query but here it wont work
Level 53
@gacho works in fresh Laravel 6 app without any problems:
class CitiesTableSeeder extends Seeder
{
public function run()
{
DB::unprepared(file_get_contents(__DIR__ . '/cities.sql'));
}
}
class DatabaseSeeder extends Seeder
{
public function run()
{
$this->call(CitiesTableSeeder::class);
}
}
cities.sql:
INSERT INTO `cities` (`id`, `loc_id`, `zipcode`, `name`, `lat`, `lon`) VALUES (1,14308,'19348','Berge bei Perleberg',53.23746,11.87077), (2,22537,'85309','Pörnbach',48.6167,11.4667), (3,106968,'24790','Osterrönfeld Heidkrug, Gemeinde Osterrönfeld',54.27536,9.737535), (4,18324,'98646','Hildburghausen',50.43950110601,10.723922177567), (5,16590,'27336','Frankenfeld, Aller',52.76951,9.43078000000003), (6,19092,'19294','Karenz',53.23012,11.34384), (7,144118,'19395','Wendisch Priborn Tönchow',53.34181,12.3207600000001), (8,16355,'99628','Eßleben-Teutleben',51.1417,11.4583), (9,25953,'38486','Wenze',52.56471,11.13311)
Tinker:
>>> DB::table('cities')->get();
=> Illuminate\Support\Collection {#3039
all: [
{#3036
+"id": 1,
+"loc_id": 14308,
+"zipcode": "19348",
+"name": "Berge bei Perleberg",
+"lat": 53.24,
+"lon": 11.87,
},
{#3035
+"id": 2,
+"loc_id": 22537,
+"zipcode": "85309",
+"name": "Pörnbach",
+"lat": 48.62,
+"lon": 11.47,
},
{#3034
+"id": 3,
+"loc_id": 106968,
+"zipcode": "24790",
+"name": "Osterrönfeld Heidkrug, Gemeinde Osterrönfeld",
+"lat": 54.28,
+"lon": 9.74,
},
{#3022
+"id": 4,
+"loc_id": 18324,
+"zipcode": "98646",
+"name": "Hildburghausen",
+"lat": 50.44,
+"lon": 10.72,
},
{#3040
+"id": 5,
+"loc_id": 16590,
+"zipcode": "27336",
+"name": "Frankenfeld, Aller",
+"lat": 52.77,
+"lon": 9.43,
},
{#3042
+"id": 6,
+"loc_id": 19092,
+"zipcode": "19294",
+"name": "Karenz",
+"lat": 53.23,
+"lon": 11.34,
},
{#3043
+"id": 7,
+"loc_id": 144118,
+"zipcode": "19395",
+"name": "Wendisch Priborn Tönchow",
+"lat": 53.34,
+"lon": 12.32,
},
{#3044
+"id": 8,
+"loc_id": 16355,
+"zipcode": "99628",
+"name": "Eßleben-Teutleben",
+"lat": 51.14,
+"lon": 11.46,
},
{#3045
+"id": 9,
+"loc_id": 25953,
+"zipcode": "38486",
+"name": "Wenze",
+"lat": 52.56,
+"lon": 11.13,
},
],
}
1 like
Level 2
I just copied exactly like you and it says that there is syntax error.
Level 53
@gacho which version of Laravel you're using? Also what database?
Level 2
Laravel 6.6.2 mysql database
Level 53
@gacho works fine on Laravel 6.6.2.
Level 2
In exactly which folder did you put cities.sql?
Level 53
@gacho it shows that there is a syntax error in your query, so it reads your query.
database/seeds
1 like
Please or to participate in this conversation.