Get enum options in laravels eloquent
Solution 1
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');
Solution 2
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] : [];
}
Solution 3
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] : [];
}
Solution 4
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
)
);
Related videos on Youtube
barfoos
Software Engineering student at Vienna University of Technology. Open for starting a PhD in Computer Science.
Updated on July 10, 2022Comments
-
barfoos almost 2 years
In my migration file, I gave my table
pages
aenum
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!
-
ozanmuyes over 8 yearsWhy not use a dedicated trait for this?
-
lukasgeiter over 8 yearsSure you can use a trait if you want
-
Amir Hassan Azimi about 7 yearsLaravel should add this to every Eloquent Class maybe as a Trait
-
Gibtang almost 4 yearsThis 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] : []; } `
-
Ikyong almost 4 yearsI 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'