Get enum options in laravels eloquent

Question

In my migration file, I gave my table pages a enum field with 2 possible values (as seen below). My question is, if it's possible to select these values with Laravels Eloquent?

$table->enum('status', array('draft','published'));

There are several Workarounds that I found, but there must be some "eloquent-native" way to handle this. My expected output would be this (that would be perfect!):

array('draft','published')

Thank you in advance!

score 24 · Answer 1 · edited Dec 07 '17 at 18:06

Unfortunately, Laravel does not offer a solution for this. You will have to do it by yourself. I did some digging and found this answer

You can use that function and turn it into a method in your model class...

class Page extends Eloquent {

    public static function getPossibleStatuses(){
        $type = DB::select(DB::raw('SHOW COLUMNS FROM pages WHERE Field = "type"'))[0]->Type;
        preg_match('/^enum\((.*)\)$/', $type, $matches);
        $values = array();
        foreach(explode(',', $matches[1]) as $value){
            $values[] = trim($value, "'");
        }
        return $values;
    }
}

And you use it like this

$options = Page::getPossibleStatuses();

If you want you can also make it a bit more universally accessible and generic.

First, create a BaseModel. All models should then extend from this class

class BaseModel extends Eloquent {}

After that, put this function in there

public static function getPossibleEnumValues($name){
    $instance = new static; // create an instance of the model to be able to get the table name
    $type = DB::select( DB::raw('SHOW COLUMNS FROM '.$instance->getTable().' WHERE Field = "'.$name.'"') )[0]->Type;
    preg_match('/^enum\((.*)\)$/', $type, $matches);
    $enum = array();
    foreach(explode(',', $matches[1]) as $value){
        $v = trim( $value, "'" );
        $enum[] = $v;
    }
    return $enum;
}

You call this one like that

$options = Page::getPossibleEnumValues('status');

Laravel should add this to every Eloquent Class maybe as a Trait — Amir Hassan Azimi, Mar 17 '17 at 18:45
I tried this solution, yes it works but how can I save the Index instead of the status itself. Example 1 for 'draft', then 2 for 'published' — Ikyong, Jul 08 '20 at 03:04

TheNatureBoy · Answer 2 · 2016-06-03T13:58:50.853

6

Made a small improvement to lukasgeiter's function. The foreach loop in his answer is parsing the string. You can update the regex to do that for you.

/**
 * Retrieves the acceptable enum fields for a column
 *
 * @param string $column Column name
 *
 * @return array
 */
public static function getPossibleEnumValues ($column) {
    // Create an instance of the model to be able to get the table name
    $instance = new static;

    // Pulls column string from DB
    $enumStr = DB::select(DB::raw('SHOW COLUMNS FROM '.$instance->getTable().' WHERE Field = "'.$column.'"'))[0]->Type;

    // Parse string
    preg_match_all("/'([^']+)'/", $enumStr, $matches);

    // Return matches
    return isset($matches[1]) ? $matches[1] : [];
}

edited Jun 03 '16 at 13:58

answered Jun 02 '16 at 21:31

TheNatureBoy

198
1
2
7

This throws an error if the column does not have enum values. So I added a small check in the code ` public static function getPossibleEnumValues ($column) { // Create an instance of the model to be able to get the table name $instance = new static; // Pulls column string from DB $enumStr = $arr[0]->Type; // Parse string preg_match_all("/'([^']+)'/", $enumStr, $matches); // Return matches return isset($matches[1]) ? $matches[1] : []; } ` – Gibtang Jun 02 '20 at 03:57

score 3 · Answer 3 · answered Jun 02 '20 at 04:03

This throws an error if the column does not exist. So I added a small check in the code

public static function getPossibleEnumValues ($column) {
// Create an instance of the model to be able to get the table name
    $instance = new static;

    $arr = DB::select(DB::raw('SHOW COLUMNS FROM '.$instance->getTable().' WHERE Field = "'.$column.'"'));
    if (count($arr) == 0){
        return array();
    }
    // Pulls column string from DB
    $enumStr = $arr[0]->Type;

    // Parse string
    preg_match_all("/'([^']+)'/", $enumStr, $matches);

    // Return matches
    return isset($matches[1]) ? $matches[1] : [];
}

score 2 · Answer 4 · edited Aug 21 '17 at 14:09

As of L5.17 Eloquent does not include this functionality, instead you need to fall back to native QL. Here's an example that will work with SQL and in one line - returning an array like you asked.

In the spirit of one liner complexity ;)

I threw this in one of my view composers - it fetches the column from the table, explodes it and assembles the values in an array.

I iterate over that in my views using a foreach.

explode (
    "','",
    substr (
      DB::select("  SHOW COLUMNS 
                    FROM ".(new \Namespace\Model)->getTable()." 
                    LIKE 'colName'"
      )[0]->Type,
      6,
      -2
    )
);

Get enum options in laravels eloquent

4 Answers4