Yii 1.1: ejui-autocomplete-fk-field

CJuiAutoComplete widget for FK field of a model
28 followers

Summary

The EJuiAutoCompleteFKField extension renders a CJuiAutoComplete field plus supporting form fields for a FK field. Typically it is used for a model with a foreign key field to a parent table that has too many records for a drop-down list to be practical.

For example it could be used in a Contact table, with a foreign key to a PostCode table with thousands of records, one for each city / postcode combination. The user would type the city name in the AutoCompleter, and the PostCodeId would be stored in the correct model column; while the display attribute (e.g. City, Province) is shown in the form.

The extension renders the following:

  • the model field itself, which may optionally be hidden or visible
  • a hidden field that holds the description field of the FK record, for redisplay if the user fails to choose a value from the autoCompleter
  • the AutoComplete field itself, which also displays the existing value from the related record
  • a 'delete' icon to clear all three fields
  • javascript to tie everything together

Requirements

Yii 1.1+

Questions

Questions can be posted to this Forum Topic

Usage

1) unzip the extension into ../extensions/

2) make sure config/main.php has:

import=>array(
          'application.extensions.*',
          ...
      ),

3) ensure the relationship exists in the model: in Contacts.php (example):

'relations'=>array(
      'Postcode'=>array(self::BELONGS_TO, 'PostCodes', 'PostCodeId'),
      ...
  );

4) in the related table, optionally create a pseudo-attribute for display purposes. For example in PostCodes.php:

public function getPostCodeAndProvince() {
      // presuming PostCode, City and Province are fields
      return $this->City . ', ' . $this->Province . ' (' . $this->PostCode . ')'; 
 }

5) in the _form.php for the main record (e.g. Contacts)

echo $form->labelEx($model, 'PostCodeId');
 $this->widget('EJuiAutoCompleteFkField', array(
      'model'=>$model, 
      'attribute'=>'PostCodeId', //the FK field (from CJuiInputWidget)
      // controller method to return the autoComplete data (from CJuiAutoComplete)
      'sourceUrl'=>Yii::app()->createUrl('/contacts/findPostCode'), 
      // defaults to false.  set 'true' to display the FK field with 'readonly' attribute.
      'showFKField'=>true,
       // display size of the FK field.  only matters if not hidden.  defaults to 10
      'FKFieldSize'=>15, 
      'relName'=>'Postcode', // the relation name defined above
      'displayAttr'=>'PostCodeAndProvince',  // attribute or pseudo-attribute to display
      // length of the AutoComplete/display field, defaults to 50
      'autoCompleteLength'=>60,
      // any attributes of CJuiAutoComplete and jQuery JUI AutoComplete widget may 
      // also be defined.  read the code and docs for all options
      'options'=>array(
          // number of characters that must be typed before 
          // autoCompleter returns a value, defaults to 2
          'minLength'=>3, 
      ),
 ));
 echo $form->error($model, 'PostCodeId');

6) in the Controller for the model, create a method to return the autoComplete data. NOTE: make sure to give users the correct permission to execute this method, according to your security scheme

in ContactsController.php (for example):

// data provider for EJuiAutoCompleteFkField for PostCodeId field
   public function actionFindPostCode() {
       $q = $_GET['term'];
       if (isset($q)) {
           $criteria = new CDbCriteria;
           //condition to find your data, using q as the parameter field
           $criteria->condition = '...', 
           $criteria->order = '...'; // correct order-by field
           $criteria->limit = ...; // probably a good idea to limit the results
           // with trailing wildcard only; probably a good idea for large volumes of data
           $criteria->params = array(':q' => trim($q) . '%'); 
           $PostCodes = PostCodes::model()->findAll($criteria);
 
           if (!empty($PostCodes)) {
               $out = array();
               foreach ($PostCodes as $p) {
                   $out[] = array(
                       // expression to give the string for the autoComplete drop-down
                       'label' => $p->PostCodeAndProvince,  
                       'value' => $p->PostCodeAndProvince,
                       'id' => $p->PostCodeId, // return value from autocomplete
                   );
               }
               echo CJSON::encode($out);
               Yii::app()->end();
           }
       }
   }

7) in the Controller loadModel() method, return the related record in ContactsController.php (for example)

public function loadModel() {
      ...
      if (isset($_GET['id']))
               // NOTE 'with()'
               $this->_model=Contacts::model()->with('Postcode')->findbyPk($_GET['id']); 
      ...
 }

Total 15 comments

#11096 report it
c@cba at 2012/12/16 03:38pm
@WebDevPT - autocomplete that allows new text

@WebDevPT
I think this extension (combobox) does what you want.
It is different from autocomplete-fk-field in that it does not suggest foreign-key-fields, but "already entered values" (like tags). Optionally, it allows alternative text to be entered.

@jeremy
Thanks for this great extension!

#10889 report it
WebDevPT at 2012/11/29 11:57am
Thank you again

Thank you again for a fast response. Right now I added a "add new" link near the text field, but on a particular form i would like users to input new values and if they don't pop up on the suggested values they should be added. But right now the "add new" link is working for me. I will try to implement a new way to add directly on the text field when I have more extra time.

Thanks again!

BTW: I forgot the "tutorial" on the yii blog series that includes this feature to add new tags for posts. I believe i will have my answer there. I will have to look again during the weekend.

#10888 report it
jeremy at 2012/11/29 11:35am
adding new values

@WebDevPT

But what if we need to add a new value instead of choosing one that exists? What do we need to change in order to add new values if they don't exist?

well, it's a foreign-key field. I don't know how your application works, but in my applications, if a value does not exist in the FK table, someone is responsible to create it, before it can be selected as a FK in the child table. None of my apps have ever allowed creation of a new value in the same place where the FK is used.

For instance, say you're creating an order. Typically the items being ordered would already exist in an Items table. The user entering the order would not be responsible for creating new items; that is someone else's job.

But if your app works differently, then you can figure out how to extend the widget. Currently it does not have the behavior you're requesting.

#10887 report it
WebDevPT at 2012/11/29 11:27am
Thank you!

Thank you for this great extension. Works like a charm.

But what if we need to add a new value instead of choosing one that exists?

What do we need to change in order to add new values if they don't exist?

Thanks in advance.

#10758 report it
jeremy at 2012/11/21 02:57pm
re: non-integer FK field

@mohamadaliakbari

Sorry for the delay in replying. I was away for 5 weeks.

> How can I use non-integer column as FK?

the extension works properly with a non-integer FK field. I am using it that way in my application.

> in my case FK field is VARCHAR with values like 004, 005, 00001 and ... _> and [this] extension remove[s] zeroes from string

the extension itself does nothing to the data values. Please look at the source. Are you certain your code is not removing the leading zeroes? I just checked my application and it works properly if the FK value has leading zeroes.

#10302 report it
mohamadaliakbari at 2012/10/18 06:22am
How can I use none integer column as FK?

in my case FK field is VARCHAR with values like 004, 005, 00001 and ...

and these extension remove zeroes from string

$this->widget('EJuiAutoCompleteFkField', array(
  'model' => $model,
  'attribute' => 'dpcio_membership_id',
  'sourceUrl' => Yii::app()->createUrl('/dpcioRequestMembership/default/membershipAutoComplete'),
  'showFKField' => FALSE,
  'relName' => 'membership',
  'displayAttr' => 'name',
  'autoCompleteLength' => 30,
  'options' => array(
    'minLength' => 1,
  ),
));
#8630 report it
jeremy at 2012/06/14 05:18pm
re: initial value of _display

@nineinchnick: sorry I missed this comment previously. interesting point. If 0 is a valid FK value, then your code is better. But I'm not 100% sure of the ramifications in all situations. In my db I'm transforming empty-string values of FK fields to nulls, because otherwise the db (in my case SQL Server 2008 R2) says it's an FK violation. But perhaps some db's allow empty-string values in FK fields. In which case, it would try to find the related record and fail. So for the moment I'm not publishing this change; but for those with zero as valid FK value, your solution is good. - Jeremy

#8629 report it
jeremy at 2012/06/14 05:03pm
text-field-remove image

@mohamadaliakbari : the image I'm using is from here: http://findicons.com/icon/179942/text_field_remove

It's freeware so I guess I could include in the .zip file, but there is no explicit license so I didn't.

#8616 report it
mohamadaliakbari at 2012/06/14 09:22am
image file is missed

/images/text_field_remove.png is missed

#7496 report it
jeremy at 2012/03/26 09:11am
version 1.4

version 1.4 includes the fix (v1.3 posted in forum) to use the widget on child rows; and add addslashes() on the display value of the attribute.

#6809 report it
nineinchnick at 2012/02/06 04:55pm
initial value of _display

I have a relation where I allow 0 in the FK and the condition in init() method on line 179:

if (!empty($this->model->{$this->attribute}))

is incorrect. Shouldn't it check for null? Like:

if ($this->model->{$this->attribute} !== null)

edit: after comparing with latest release I've noticed I've changed the ternary operator to a full 'if' but my point is still valid :-)

#3355 report it
Thiago Luís at 2011/04/05 02:18pm
I need help

Can i post the code here?

#3258 report it
jeremy at 2011/03/29 03:10pm
version 1.2

Fixes a problem with Chrome 10 validating field length on the AutoComplete field, by adding maxLength tag.

#3143 report it
jeremy at 2011/03/21 09:45am
version 1.1

version 1.1 of the extension has minor bug fixes. Thanks to @mubo & others.

  1. added parent::init();
  2. changed class name to EJuiAutoCompleteFkField (lower-case 'k')
  3. removed Yii::t() on 'relname' since t() function doesn't work on variables
#3136 report it
mubo at 2011/03/20 09:36am
small bug

As I couldn't post here before I leave this link for reference:

http://www.yiiframework.com/forum/index.php?/topic/17056-small-bug-in-ejuiautocompletefkfield/

Leave a comment

Please to leave your comment.

Create extension