Creating MYSQL FUNCTION in Laravel 4 Migrations

Question

I am working on laravel 4.1. I want to create MYSQL function with migration but it doesn't work with php artisan migrate. even if it shows Migrated: 2017_01_10_140002_create_MYSQL_UNAVAILABLE_PRODS_FUNCTION in the console. and when i copy/past the same creation code in MYQL client, it works and the function is created. and when i run php artisan migrate:rollback the function is removed.
Any help would be appreciated.

the migration file :

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateMYSQLUNAVAILABLEPRODSFUNCTION extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $available = Constant::PRODUCT_AVAILABLE_TRUE | Constant::PRODUCT_AVAILABLE_STOCK_TRUE;

        $sql = 'DROP FUNCTION IF EXISTS UNAVAILABLE_PRODS;
                DELIMITER $$
                CREATE FUNCTION UNAVAILABLE_PRODS(CONFIG_ID INT, PROD_TYPE INT)
                  RETURNS TEXT
                  LANGUAGE SQL
                BEGIN
                    DECLARE RES TEXT;

                    SET GROUP_CONCAT_MAX_LEN = 2048;

                    SET RES = (SELECT GROUP_CONCAT(ID SEPARATOR \'|\') FROM PRODUCT_BASE WHERE `PRODUCT_TYPE` & PROD_TYPE AND `PRODUCT_BASE`.`AVAILABLE` <> ' . $available . ');

                    RETURN CONCAT(CONFIG_ID, \'=(\', RES, \')\');
                END;
                $$
                DELIMITER ;
                ';

        DB::unprepared($sql);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::unprepared('DROP FUNCTION IF EXISTS UNAVAILABLE_PRODS;');
    }

}

i have a field called info in DB with data in that form **config_id1=prod_id1|config_id2=prod_id2|...** so instead of using php to do some test by sending a list of hundreds of IDs.. for performance matters it's better doing it in MYSQL side. and i need to check if that field has a pair key=value from a list of values eg : 41=(202|101..) with : `SELECT... WHERE info REGEXP UNAVAILABLE_PRODS(41, 64)`. the result after function execution will be like this `SELECT... WHERE info REGEXP '41=(prod_id1|prod_id2...)'`: — Eissa, Jan 10 '17 at 17:55

score 5 · Accepted Answer · answered Jan 12 '17 at 10:36

5

I figured out what the problem was, it's the delimiters. it worked after i removed the lignes DELIMITER $$ , $$ and DELIMITER ;

answered Jan 12 '17 at 10:36

Eissa

325
2
11

score 0 · Answer 2 · answered Jan 10 '17 at 15:41

0

I think the mistake is here \'|\') this quotes are outside of your sql(there are not green). So php tries to execute them in some way screenshot of your code

answered Jan 10 '17 at 15:41

Onix

2,129
2
17
26

i replaced the single quote with double and still not working. if that was the problem i would have an error shown in the console. – Eissa Jan 10 '17 at 17:31

Creating MYSQL FUNCTION in Laravel 4 Migrations

2 Answers2