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;');
}
}
2
Answers
DB::raw
returns anExpression
to be used in the query builder. It doesn’t run sql.Use
DB::statement
for this.You can also use Laravel Schema Builder. Check documentation here