ENUM DB type in Yii

Hello all,

Whats the best practice in Yii to work with database fields that its values are a list of items?

Currently, I’m using the ENUM type of MySQL, but I don’t know how Yii can work with this type. Is there any way to retrieve the values of the enum type and that Yii manages it?

Thanks in advance.

Regards.

Kike

This is how I do:




	public static function enumItem($model,$attribute)

	{

		$attr=$attribute;

		self::resolveName($model,$attr);

		preg_match('/\((.*)\)/',$model->tableSchema->columns[$attr]->dbType,$matches);

		foreach(explode(',', $matches[1]) as $value)

		{

			$value=str_replace("'",null,$value);

			$values[$value]=Yii::t('enumItem',$value);

		}

		

		return $values;

	}	



Maybe you can do better with regExp, but this code at least works and gives you an array with the items.

Thank you very much, I’m afraid of the performance with this solution. Maybe it takes too much time?

Regards:

Kike

I don’t know.

I have never had problem of performance with this, but I never had more than 4-5 dropdownlist from enum in a page, I use only in form.

Just pay attention to reuse the items if you have to place 500 times in a page.

I edited my previous post with a better one solution

@Zaccaria

Hi! Nice solution… I’ve seen others quite messed.

Sorry for my snoob question but I’m pretty new to Yii: ::)

Where it’s supposed to be sit that function? Is there a place where it must be or where it could be comfortable with the rest of the framework?

TIA,

Pampa

Ok, I can’t wait! :lol:

I’ve dropped your code (commented out the line resolveName()) in my model file.

Then, in the view I’ve called it in this way:


<?php echo CHtml::activeDropDownList( $model,'status',$model->enumItem($model, 'status') ); ?>

Where ‘status’ is an [font=“Courier New”]ENUM(‘Active’,‘Inactive’,‘Deleted’)[/font].

Hope to help someone :D

I usally put this code in a class that I call ZHtml and put in compoment.

In this class there are my helpers. As they are static, they can be called on the class itself:




class ZHtml extends CHtml

{

        public static function enumItem($model,$attribute)

        {

                $attr=$attribute;

                self::resolveName($model,$attr);

                preg_match('/\((.*)\)/',$model->tableSchema->columns[$attr]->dbType,$matches);

                foreach(explode(',', $matches[1]) as $value)

                {

                        $value=str_replace("'",null,$value);

                        $values[$value]=Yii::t('enumItem',$value);

                }

                

                return $values;

        }  


       public static function enumDropDownList($model, $attribute, $htmlOptions)

       {

          return CHtml::activeDropDownList( $model, $attribute,ZHtml::enumItem($model,  $attribute), $htmlOptions);

       

       

       }


}



This allow you to write in the view (without any change in the model):


<?php echo CHtml::activeDropDownList( $model,'status',ZHtml::enumItem($model, 'status') ); ?>

or , more tasty:


<?php echo ZHtml::enumDropDownList( $model,'status'); ?>

Thanks zaccaria for that stuff, it helped me too.

suppose I have ENUM(‘Active’,‘Inactive’,‘Deleted’)

How to get the value I saved in my text field when I want to modify the form?

If I select ‘Inactive’ and save then I noticed that ‘Active’ allways come first during any modification in the form.

Thanks!

Simply use ve helper, you don’t need any textfield.

Just write


<?php echo ZHtml::enumDropDownList( $model,'status'); ?>

and all will be fine.

If it is not saved the value, maybe is a problem of safe attributes.

Maybe I didn’t notice something but I can see it’s working now very well without any other modifications

That’s a great stuff!

Thanks

Yeahh!!

I was wondering what else could be wrong… this piece of code has 2 years and alway did his work.

Happy that it helped.

Heres a little snippet to get it to work with Postgresql9 as well.


<?php

class ZHtml extends CHtml

{

        public static function enumItem($model,$attribute)

        {

		$values = array();

                $attr=$attribute;

                self::resolveName($model,$attr);

		if( $model->tableSchema instanceof CPgsqlTableSchema ) {

			$sql=<<<EOD

SELECT enumlabel FROM pg_enum

JOIN pg_type ON pg_type.oid = pg_enum.enumtypid

WHERE pg_type.typname = :table

EOD;

			$command=Yii::app()->db->createCommand($sql);

			$command->bindValue(':table',$model->tableSchema->columns[$attr]->dbType);


			$values=$command->queryColumn();

			

			Yii::trace( "Enum values were: ".print_r( $values, true ) );

		

		} else {

			if( preg_match('/\((.*)\)/',$model->tableSchema->columns[$attr]->dbType,$matches) > 0 ) {

				foreach(explode(',', $matches[1]) as $value)

				{

					$value=str_replace("'",null,$value);

					$values[$value]=Yii::t('enumItem',$value);

				}

			}

		} 

                return $values;

        }  


       public static function enumDropDownList($model, $attribute, $htmlOptions = array())

       {

          return CHtml::activeDropDownList( $model, $attribute,ZHtml::enumItem($model,  $attribute), $htmlOptions);

       

       

       }


}

?>

Hey,

Thanks for the concise instructions, I had to make one modification though, as enumDropDownList takes 3 arguments, the last one being $htmlOptions, i had to add [color="#006400"]array()[/color] as an argument, eg


<?php echo ZHtml::enumDropDownList($model,'status', array()); ?>

I’m pretty new to this so if that is dangerous, let me know.

Cheers!

Generally, $htmlOptions is given a default argument $htmlOptions=array()

I changed it to the following when I used it:




public static function enumDropDownList($model, $attribute, $htmlOptions=array())

{

	return CHtml::activeDropDownList($model, $attribute, ZHtml::enumItem($model, $attribute), $htmlOptions);

}



I’ve just implemented your code… Really great stuff, even after 2 years! Thanks!

great helper class Zaccaria. Thanks! :D

To set the selected item when using in update forms add this snippet, (replace ‘attr’ in 2 places with your model attribute):


<?php echo ZHtml::enumDropDownList($model, 'attr',

                 array(

                         'options'=>array('$model->attr'=>array('selected'=>true)),

                 ))

?>

thanks zachri , it helped me

A wiki Article on dropdownlist version of this code [enumDropdownlist](http://www.yiiframework.com/wiki/303/drop-down-list-with-enum-values-for-column-of-type-enum-incorporate-into-giix/ "enumDropdownlist")

A wiki article on the RadioButtonlist version of this code [enumRadioButtonlist](http://www.yiiframework.com/wiki/334/radio-button-list-with-enum-values-for-column-of-type-enum-incorporate-into-giix/ "enumRadioButtonlist")