I have sql function that I need to migrate to phpmyadmin in order for my code to work. I make migration where I insert code for sql function and then I do php artisan migrate:fresh –seed it successfully completes and fills all table and it shows that migration worked.
Migrating: 2022_01_28_115051_add_calculate_distance_function Migrated: 2022_01_28_115051_add_calculate_distance_function (0.07ms)
But when I go in phpmyadmin it didn’t create function. I never worked with this sql functions before, so any help is much appreciated. I use Laravel 8.65. Here is my migration.
migration
<?php use IlluminateDatabaseMigrationsMigration; use IlluminateDatabaseSchemaBlueprint; use IlluminateSupportFacadesDB; use IlluminateSupportFacadesSchema; class AddCalculateDistanceFunction extends Migration { /** * Run the migrations. * * @return void */ public function up() { DB::raw('DROP FUNCTION IF EXISTS calculate_distance; CREATE FUNCTION calculate_distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS float READS SQL DATA DETERMINISTIC BEGIN RETURN 111.111 * DEGREES(ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lon1 - lon2)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)))) ; END '); } /** * Reverse the migrations. * * @return void */ public function down() { DB::unprepared('DROP FUNCTION IF EXISTS calculate_distance;'); } }
Advertisement
Answer
DB::raw
returns an Expression
to be used in the query builder. It doesn’t run sql.
Use DB::statement
for this.
public function up() { DB::statement("DROP FUNCTION IF EXISTS calculate_distance;"); DB::statement("CREATE FUNCTION calculate_distance(lat1 FLOAT, lon1 FLOAT, ..."); } public function down() { DB::statement("DROP FUNCTION IF EXISTS calculate_distance;"); }