skip to Main Content

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


  1. 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;");
    }
    
    Login or Signup to reply.
  2. You can also use Laravel Schema Builder. Check documentation here

    Schema::drop('calculate_distance');
    Schema::dropIfExists('calculate_distance');
    Schema::create('calculate_distance', function($table)
    {
        $table->string('latitude');
    });
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search