How to Fetch the ENUM Options of a Field - The CakePHP Enumerable Behavior
Posted on 8/9/09 by Tim Koschützki
Hey folks,
for a current client project of ours we had to build an extensive ACL-like permission system. It had to support roles permissions and specific user permissions, possibly overriding the roles permissions. So, to create a user, you had to first define his "role" or "level" as I called it in a dropdown box in a form.
However, the field users.level is an enum type and can have the values 'guest', 'user', 'admin', 'superadmin' and 'root'. The problem is that it could be possible that new levels were added in the future. There was no need though to have a seperate user_levels table and a UserLevel model there, since the system should simply not be as generic to allow that and adding a new level in the future would require a complete other version of the software. So I went the easy way.
Besides, as we use uuids, the users.level field would contain them and after all we want our database to be readable in our favorite db management system.
So what I came up with is a very simple behavior that can extract the options for any ENUM field. It uses simple caching in order for the query to not be run all the time, so make sure to clear your cache as you update your enum field options in the db.
Here is the behavior:
/**
* Behavior with useful functionality around models containing an enum type field
*
* Copyright (c) Debuggable, http://debuggable.com
*
* @package default
* @access public
*/
class EnumerableBehavior extends ModelBehavior {
/**
* Fetches the enum type options for a specific field
*
* @param string $field
* @return void
* @access public
*/
function enumOptions($model, $field) {
$cacheKey = $model->alias . '_' . $field . '_enum_options';
$options = Cache::read($cacheKey);
if (!$options) {
$sql = "SHOW COLUMNS FROM `{$model->useTable}` LIKE '{$field}'";
$enumData = $model->query($sql);
$options = false;
if (!empty($enumData)) {
$patterns = array('enum(', ')', '\'');
$enumData = r($patterns, '', $enumData[0]['COLUMNS']['Type']);
$options = explode(',', $enumData);
}
Cache::write($cacheKey, $options);
}
return $options;
}
}
?>
To put that into the form I did in the controller:
$this->set(compact('enumOptions'));
and then in the form:
Enjoy and please give feedback.
-- Tim Koschuetzki aka DarkAngelBGE
Did you like this blog post? If so, please consider subscribing to the Blog RSS feed.
You can skip to the end and add a comment.
Hmm, it's going wrong when you write testcases!! because enum fields is not a standard or something like that... Then you get a warning!
Yeah, nice.
You wrote your own parser for nothing.
Why not just to make this field tinyint, and distinguish levels in the code (using constants etc)? You said you do not need separate table.
Ok, good. Do not add it, use code logic. Also, having ENUMs could be areal nightmare when you change it.
Approach is cheap, but bad by his nature.
2Adam: Your approach is possible, but it is certainly not DRY. If you pass your app to anyone and he would add user role (as new enum), he should (in good app) get new role without any code change.
But still I don't know Tim. I read (a lot) about enum + CakePHP and I thought the "right" solution is to have another table and relation to it. I know, you said it is unprobable that roles will be added. But now you can't use counterCache on how many admins you have. It is hard to say that every "root" get redirected to page "xy" when logged in (via another column in role-table). And so on.
So I think that for simple things (like yours) it is acceptable. But generally I really would like to see related table (even for simple situations). My opinion.
Would appreciate if anyone proves I'm wrong.
@Adam: as picca said, your approach is possible, but the code should be as independent as possible from your user roles.
@picca: Yes of course the best solution is the most reusable one, and that would mean a separate table, some nice counterCache declarations and possibly having some permission-related stuff also in that table if need be.
As I said though in the article, I had a very simple case and I implemented permissions in a different way. I just needed a dropdown of possible user roles so I can populate my dropdown accordingly. For people that face a similarly simple problem I wanted to show how you can easily read the possible enum options. I will refactor when I have to.
If I were to release a plugin with a fully-fledged auth/roles/permission system I would do it differently of course. That was not the purpose of the blogpost though.
@alex: I didn't write a parser for nothing. I solved my problem right? ;)
I don't want to use constants because I want my db readable when I look at it in my favourite DB management system. How in your opinion would ENUMs be a nightmare when I change them? That's a simple UPDATE query, at least from what I see now. If I had a separate table and possible uuids it could (!) become more than that.
@Eppo: Please explain the scenario.
@all: Thanks for your feedback. It's always nice to have discussions about approaches.
@tim:
1. your solution is not really in right way
2. you teach other people this solution
readable: yes, but that's it (sometimes it's worth it, of course).
if you have > 10 devs, and anyone can change table structure, it's very easy to get a mess if you used not the latest version of DB structure to write your ALTER table (this is in short, but u got an idea).
anyway, as I said: this is cheap, but I'd rather recommend to not do like this.
@alex: If I have > 10 devs I better have a damn good way to exchange DB Structure updates ... be it db migrations, a ticket in < insert your favorite ticket management system > whom everybody is subscribed to, or something else.
Anyways, if you think the solution is cheap, that's fine with me. I tried to explain when and when not to use this.
Thanks for sharing Tim.
I've not used enum fields in my db since I've been using 1.2 as last time I checked, enums weren't supported in the Cake Schema shell, which I use quite a lot.
When I used them in 1.1 apps though, the enum options were available in the model schema property (or whatever it was in 1.1) - is that no longer the case in 1.2? If the options are available, you'd not need to do the sql call to get them?
Now I use a similar approach mentioned to one above where I have a property in my model called $enum, which looks something like this:
protected $enum = array(
'role' => array(
'admin',
'user',
'...'
),
'status' => array(
'enabled',
'disabled',
),
);
and I typically store the whole string in the DB, after all, storage is cheap, so this gives me the best of both worlds, readability in the DB and ability to use the schema shell, and easily sharing new options with other devs.
As for counting how many admins there are in the system, let's be honest, it's not a frequent requirement is it, and if it is, you can use some other form of caching than a counter cache.
Anyway, in my controller, I have a protected method called _setEnumLists() which I call from add() and edit() actions, which sets view variables according to the plural variable version of the field name, e.g. for 'role', the variable is $roles. If it was user_level, the var would be $userLevels. This way, the form helper automatically displays a drop down for these fields.
Thank you for this behavior. I will most likely use it in one of my projects.
Since that project will use PostgreSQL as DBMS, I updated your code ( http://bin.cakephp.org/view/746957234 ) so the behavior will work with both MySQL and PostgreSQL.
This works? i mean, it returns a numeric index array with each enum option, but, at the time of the insert with save() cake try to insert the numeric index and not the enum option, so dont works... the array should have the same enum string as array key and value.
This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.
Would it not have been easier (and cleaner/more portable) to add a new variable to the User Model?
[code]
< ?php
< ?php
class User extends AppModel {
var $name = 'User';
....
var $_enum = array(
'level' => array(
'admin' => 'Administrator'
'superadmin' => 'Super Administrator'
'root' => 'Root User'
);
);
....
}
?>
[/code]