find(‘list’) with three (or combined) fields

Update 01/07/2010: Good news everyone, for those switching over to cake 1.3, there is a great new feature:
http://book.cakephp.org/view/1608/Virtual-fields
————–

How about a little trick to extend the find(‘list’) functionality?..

Let’s say we need to display a list of users, but instead of just User.id and User.name we need to have User.id, as well as User.name and User.email combined. Unfortunately find(‘list’) doesn’t have that type of functionality out-of-the-box. Well, that’s OK, we’ll make our own…

Before I continue, I’d like to thank grigri for a very creative way to use Set::combine() as well as cakebaker for his tips on extending the find() method functionality.

Alright, let’s add this to our app model:

 function find($type, $options = array()) {
        switch ($type) {
            case 'superlist':
                if(!isset($options['fields']) || count($options['fields']) < 3) {
                    return parent::find('list', $options);
                }

                if(!isset($options['separator'])) {
                    $options['separator'] = ' ';
                }

                $options['recursive'] = -1;              
                $list = parent::find('all', $options);

                for($i = 1; $i <= 2; $i++) {
                    $field[$i] = str_replace($this->alias.'.', '', $options['fields'][$i]);               
                }           

                return Set::combine($list, '{n}.'.$this->alias.'.'.$this->primaryKey,
                                 array('%s'.$options['separator'].'%s',
                                       '{n}.'.$this->alias.'.'.$field[1],
                                       '{n}.'.$this->alias.'.'.$field[2]));
            break;                      

            defau<              
                return parent::find($type, $options);
            break;
        }
    }

Now all we have to do in our controller is:

$this->User->find('superlist', array('fields'=>array('User.id',
                                                                  'User.name',
                                                                  'User.email'),
                                              'separator'=>' * '));

Which ultimately gives us something like:

...
<option value=&quot;1&quot;>Bob * bob@hotmail.com</option>
...

Let’s quickly go over what we’ve done…

We overrode the default Model::find() method by extending it with a new find type called ‘superlist’. We also allow a new key ‘separator’ to specify the delimiter between the fields (for the sake of the example I chose a ‘ * ‘).

The code is not very complicated, so I hope you can figure out what’s going just by examining our custom find() function… but just a few points to help you along:

  • Specify at least 3 fields, or the method will default to the regular find(‘list’)
  • Field order is important
  • We need to specify the primary key field to extract the appropriate value
  • Model’s primary key field is always used for the option value
  • Fields can be specified as ‘fieldName’ or ‘Model.fieldName’
  • If you do not specify the ‘separator’ key, it will default to a space character

Keep in mind that this is more of an exercise, and you can achieve the same results without any overrides, but this certainly helps to keep your controllers nice, clean and skinny.

  • http://ovalpixels.com Georgi Momchilov

    Very nice. I was just wondering – is there a reasoning behind the fact that you tend to use Model::find() instead of AppModel::find() ? Maybe avoid any specific conditions added/removed by AppModel::beforeFind() ? Just curious..

  • teknoid

    @Georgi Momchilov

    Thanks.

    I’m not sure I understand the question, I did recommend adding this method to app model, so it becomes available to all of your models.

  • http://blog.devayd.com daniel

    hello,

    a very nice solution to a common problem. thx!

    regards
    dz

  • teknoid

    @daniel

    Thanks, glad it helped you.

  • http://Www.brightstorm.co.uk Mikek

    Been wondering how to do exactly this since i stopped using generatelist. Top marks. Will this make it into rc3?

  • teknoid

    @Mikek

    find(‘list’) is a default in the core (take a look at the manual). However, it only works with two fields (i.e. User.id and User.name), which is fine for the vast majority of cases. This is just a supplement in case you need a slightly more robust method, therefore this particular approach will not and should not be in the core.

  • http://www.brightstorm.co.uk mikek

    @teknoid
    been using all varients of find but this is a much neater way to allow User.id => User.firstname User.lastname. I guess having a fields array in find(‘list’, $options) like you do already in find(‘all’,$options) could be confusing as both fields arrays do different things. Will look forward to implementing it. cheers.

  • teknoid

    @mikek

    Sounds good ;)

  • http://www.battez.org luke

    you could maybe use afterFind to do this also with a pseudo field ? Nice of you to publish this though – very, very useful!

  • teknoid

    @luke

    Thanks…

    Sure, afterFind() would work as well. There are certainly a few ways to handle this situation, I just tried to show something a little different and something that could be just a plug-n-play :)

  • Keith

    Thank you for providing this great technique.

  • teknoid

    @Keith

    No problem. Glad it helped.

  • Crambo

    THANK YOU!!! I am a total CakePHP noob, and finding this little ditty solved a problem that was stumping me for hours.

  • http://teknoid.wordpress.com teknoid

    @Crambo

    You’re welcome ;)

  • http://www.m3nt0r.de Kjell

    Very well done. Thanks for sharing!

    Suggestion: I would introduce a “format” option and map the fields to a variable string within the fields array.

    Example:
    ‘fields’ => array(‘value’ => ‘User.id’, ‘fname’ => ‘User.name’, ‘lname’ => ‘User.lastname’),
    ‘format’ => “{lname}, {fname}”

    Best,
    Kjell

  • http://teknoid.wordpress.com teknoid

    @Kjell

    Thank you. There are a few ways to improve this solution, but hopefully it sets you on the right track to implement further…

  • http://rafaelbandeira3.wordpress.com rafaelbandeira3

    Just to share,
    some time ago now, i’ve requested “virtual fields” support on Model::_findList(), what would allow such behavior in a more flexible-sql-based-built-in way. The ticket, patch and test are on https://trac.cakephp.org/ticket/5478.

    the usage would be
    $Project->find(‘list’, array(
    ‘fields’ => array(‘CONCAT(Project.id, ‘ – ‘, Project.title)’)
    ));

  • http://teknoid.wordpress.com teknoid

    @rafaelbandeira3

    Thanks for sharing, at some point, some kind of solution should definitely make its way to the core.

    Your proposal is nice and simple (and actually has a patch + test :) ), but there were a few others, which conceptually I found to be a little more robust, such as allowing formatting in the $displayField, which would basically take any fields from find(‘list’) and format them into a single display field as defined by the user.

    Either way, we’ll probably see some nice solution in 2.0 (or next alpha) at the earliest…

  • alex

    Imagine you use the superlist for multiple checkboxes.
    E.g.: $form->select(“Model.fieldname”, $users, null, array(“multiple” => “checkbox”));

    What do I have to do if I also like to link the email address (mailto:….)?

  • http://teknoid.wordpress.com teknoid

    @alex

    I see what you are trying to do there, and thinking about it to too quickly I’d imagine you modify the $users array to ensure that one of the relevant elements contains the mailto: link, rather than just an email address.
    This the first thing that comes to mind, so I’m not exactly sure how to get it working just right.

  • http://james.revillini.com james revillini

    Wow. I used this one as well. What an awesome way to handle this. I’d like to see it built into the core, but this is easy to add.

  • http://teknoid.wordpress.com teknoid

    @james revillini

    I’m glad you are finding useful things here :)
    There is a rumor that a similar, but a slightly more robust approach might wind up in the core soon… ish.

  • ykjow

    I got some error….when i tried to use it.
    Warning (2): vsprintf() [function.vsprintf]: Too few arguments [CORE\cake\libs\set.php, line 337]

  • http://teknoid.wordpress.com teknoid

    @ykjow

    Must have missed something, it’s hard to tell from that error.

  • http://phpexp.blogspot.com/ theix

    I made my own version of the function. Thanks for the inspiration. You can see my version on this post.

    http://phpexp.blogspot.com/2009/04/cakephp-12-versatile-model-findlist.html

    This one not only support 3 fields but also very dynamic in the sense that you format the display field of the options.

  • http://teknoid.wordpress.com teknoid

    @theix

    Nice, and thank you for sharing.
    Glad I was able to inspire further development :)

  • Andre

    I found that the line : $this->recursive = -1;
    set the models recursive behaviour to -1 and all queeries then used use the setting -1

    so I added change this from line 26:

    default:
    $this->recursive = 1; // Reset to default
    return parent::find($type, $options);
    break;

    Does eanyone else find this to be the case.?

  • http://teknoid.wordpress.com teknoid

    @Andre

    This might have changed in the core, since the writing of this article. You can also pass ‘recursive’ => -1 as part of the options to find().

  • scs

    First off thanks for this. I have been fighting with a solution to this for some time now and than I found this. I changed it though a little to help us use multiple models. For example a Teacher model has an User model and we want to use Teacher.id with User.first_name, User.last_name. First I removed the $options['recursive'] = -1; and pass it from the controller than I changed:
    $field[$i] = str_replace($this->alias.’.’, ”, $options['fields'][$i]);
    to:
    $field[$i] = $options['fields'][$i];
    This allows the model name to be where ever your info is coming from last I changed:
    ‘{n}.’.$this->alias.’.’.$field[1],
    {n}.’.$this->alias.’.’.$field[2]));
    to:
    ‘{n}.’.$field[1],
    ‘{n}.’.$field[2]));

    so in the end it looks like:
    function find($type, $options = array()) {
    switch ($type) {
    case ‘superlist':
    if(!isset($options['fields']) || count($options['fields']) < 3) {
    return parent::find('list', $options);
    }

    if(!isset($options['separator'])) {
    $options['separator'] = ' ';
    }

    $list = parent::find('all', $options);

    for($i = 1; $i alias.'.'.$this->primaryKey,
    array(‘%s’.$options['separator'].’%s’,
    ‘{n}.’.$field[1],
    ‘{n}.’.$field[2]));
    break;

    default:
    return parent::find($type, $options);
    break;
    }
    }

    • http://www.syseminent.com Raman Kumar

      Thanks dude, this was the solution I was looking for many hours…

  • http://teknoid.wordpress.com teknoid

    @scs

    Glad it set you on the right track.
    And thank you for sharing your code, hopefully it will help someone in the future, although I have a “feeling” a similar solution is coming to the core soon… ish.

  • http://www.cottser.com Scott Reeves

    Thanks again for another great post, teknoid.

    I’ve now officially subscribed to your blog, since this is the second time you’ve saved the day :)

  • http://teknoid.wordpress.com teknoid

    @Scott Reeves

    Alright, good to hear ;)

  • http://www.webtechnick.com Nick

    Thanks teknoid!

    I’ve extended this to combine more than two fields as well as pass in custom formats.

    I blogged about it here:
    http://www.webtechnick.com/blogs/view/220/CakePHP_Combine_List_Fields

    Looking at the previous posts I see Theix beat me to it, ah well. There it is anyway. =)

  • http://teknoid.wordpress.com teknoid

    @Nick

    Very cool. Thanks for sharing.

  • fabiox

    Dear Teknoid,

    i’m a CakePHP newbie and i’m handling on a quite similar topic for
    which i’ve opened a specific thread here:
    http://groups.google.it/group/cake-php/browse_thread/thread/405946857

    Basically i’ve got a check-boxes-section in the form of a baked
    edit.ctp, called with a basic statement:

    echo $form->input(‘Amenity’, array( ‘type’ => ‘select’, ‘multiple’ =>
    ‘checkbox’ ));

    The models refers to a join table with an additional field ‘specify':
    CREATE TABLE IF NOT EXISTS `amenities_properties` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `property_id` int(11) NOT NULL,
    `amenity_id` int(11) NOT NULL,
    `specify` varchar(100) NOT NULL default ‘yes’,
    PRIMARY KEY (`id`)
    );
    The idea is to set values like this:
    ‘no’ ………………..if unchecked
    ‘yes’ ………………if checked
    ‘whatever’……….. if checked and a string ‘whatever’ is inserted

    In other words I need to associate to each checkboxes an additional
    input text field to be eventually filled only if the checkboxes are
    previously checked

    Do you think this is possible somehow ?
    Could you maybe give me some little suggestion ?

    thanks a lot in advance

    fabio

  • http://teknoid.wordpress.com teknoid

    @fabiox

    Not sure what this question has to do with this post, but it is certainly possible.
    Please search here for another post, which talks about saving extra data in the join table.

  • Pingback: phpmysqlapache.com » Blog Archive » Cakephp find(’list’) and select

  • http://ifeghali.blogspot.com Igor

    Hello teknoid,

    Nice post. I would love to see something about “find(‘list’) with displayField set at the afterFind() time, and not in the database” too.

    Thank you.

  • http://teknoid.wordpress.com teknoid

    @Igor

    It isn’t concatenated in the DB, if that’s what you mean. Check a few other comments for extended examples of this code.

  • http://ifeghali.blogspot.com Igor

    Hello teknoid,

    Sorry I wasn’t clear enough. I would like to have my displayField set to an attribute (or array element) that is not in the database, but set in afterFind() time.

    Example: one model that has only usernames. after a find is made, afterFind() takes care of querying a LDAP/NIS/whatever for the other data of each user like its full name. So the display field for that model should be “full name” but find(‘list’) complains there is no such field in the database.

  • http://teknoid.wordpress.com teknoid

    @Igor

    You can use a similar method to combine the find(‘all’) queries using Set::combine()…
    You can also try to modify the model schema (look at _schema properties) to include the virtual field, however I have not tried that, so cannot be sure if it is or not going to work.

  • http://www.jeremy-burns.co.uk JB

    How could this be adapted so that the list contains (effectively) this:

    col1: model1.id
    col2: model2.name + model1.date

    In other words, the second column is a concatenation of fields from two models joined in the model by $belongsTo.

  • http://teknoid.wordpress.com teknoid

    @JB

    The approach would be the same.
    The only thing you need to do is look into the related model for the second field to combine.

  • Tt

    Thanks a lot for this solution.

    I was just searching for something like that.

    Tom

  • http://teknoid.wordpress.com teknoid

    @Tt

    Cool, enjoy ;)

  • http://adrenalin.wordpress.com/ Adrenalin

    Thanks a lot !
    Cakekphp wouldn’t be so great without it’s community ! ;)

  • http://adrenalin.wordpress.com/ Adrenalin

    I think this article deserve a direct link from the find(‘list’) page of the cake manual

  • http://adrenalin.wordpress.com/ Adrenalin

    I didn’t quite understood why do we need to pass the primary key as argument, when we already know it’s name from the model $this->primaryKey, and the “primary key” name is used even if passed another thing “Model’s primary key field is always used for the option value”.

    Made one line patch that makes passing the primary key unnecessary.

    After the line

    [code]
    case 'superlist':
    [/code]

    add

    [code]
    $options['fields'] = array_merge(array($this->alias.'.'.$this->primaryKey),$options['fields']);
    [/code]

    This way you don’t need to pass anymore the primary key, pass just the fields for the name..
    [code]
    $this->User->find('superlist', array('fields'=>array('User.name',
    'User.email'),
    'separator'=>' * '));
    [/code]
    Result is exactly the same..

    Greetings from the sunny country of Moldova ;)

  • http://teknoid.wordpress.com teknoid

    @Adrenalin

    Thanks, I think it’s a nice improvement. I agree, that cake community is doing a great job.

    p.s. Greetings from sunny Ukraine ;) (well Miami now, but born in Kiev)

    • http://adrenalin.wordpress.com/ Adrenalin

      In fact I’m not in my country either (France), but miss my home sweet home ;) By the way, got some Ukrainian students here at the university, doing some chit-chat every so often ;)

      By the way, as we are doing $options['recursive'] = -1; (i.e. no joins are performed)

      [code]
      $this->User->find('superlist', array('fields'=>array('name',
      'email'),
      'separator'=>' * '));
      [/code]
      should work just fine ;) oh those lazy programmers don’t like to type too much..

  • http://adrenalin.wordpress.com/ Adrenalin

    Another one line patch, to make formatting more flexible, let’s say you want enclose the second field in the parentheses, e.g “field1 (field2)”. We’ll introduce a “pattern” option.

    Replace
    [code]
    array('%s'.$options['separator'].'%s',
    [/code]
    With
    [code]
    array($options['pattern'],
    [/code]

    Now we should include the separator option, don’t need the “separator” anymore
    [code]
    $this->User->find('superlist', array('fields'=>array('name',
    'email'),
    'pattern'=>'%s (%s)'));
    [/code]

    The first %s will be replaced by the name and the second %s, email will go to the parentheses ;)

    In fact this is not my ideea, saw in the comments here http://bakery.cakephp.org/articles/view/multiple-display-field-3 ;o)

    “Play cakephp my friends” ;) http://www.youtube.com/watch?v=wWltrZKHCCk#t=36

  • http://teknoid.wordpress.com teknoid

    @Adrenalin

    Again, thanks for sharing… believe me, this is being noticed by a lot of people (and developers). So, as always, any contribution is quite helpful.

  • http://crushdev.com Tim Gurske

    Thanks! You’re a miracle worker!!!!!!!!!!!!!!!!!!!!!

  • http://teknoid.wordpress.com teknoid

    @Tim Gurske

    Cheers ;)

  • emka

    Hello everybody :)

    I wrote own function for generating lists. It can format list output very easy. Here is the code:

    /*******************************************************************
    * Function: generateList()
    * Params : $options – model options for data retrieve
    * $key – key field
    * $format – output data format for value
    */
    function generateList($options, $key, $format)
    {
    // Set recursive
    if (!empty($options['recursive']))
    {
    $this->recursive = $options['recursive'];
    }
    else
    {
    $this->recursive = -1;
    }
    // Retrieve all data
    $data = $this->find(‘all’, $options);
    if (!empty($data))
    {
    // Clear output array
    $list = array();
    // Get all fields
    $fields = $options['fields'];
    // Extract key model & field
    list($key_model, $key_field) = explode(‘.’, $key);
    // Read all items
    foreach($data as $item)
    {
    // Temp var for replacing field values
    $output = $format;

    // Read all field names
    foreach($fields as $field)
    {
    // Extract model & field from field’s name
    list($m, $f) = explode(‘.’, $field);
    // Get value for field from $data
    $value = $item[$m][$f];
    // Make string for replace
    $field = ‘%’ . $field . ‘%';
    // Replace %field% with $value
    $output = str_replace($field, $value, $output);
    }
    // Get value for key
    $key_value = $item[$key_model][$key_field];
    // Make output array item
    $list[$key_value] = $output;
    }
    // Return list
    return $list;
    }
    else
    {
    return false;
    }
    }

    All You need is to add this function to the app/app_model.php file.

    Example call:
    $options = array(‘recursive’ => 1, ‘fields’ => array(‘User.id’, ‘User.first_name’, ‘User.last_name’, ‘User.login’), ‘order’ => array(‘User.last_name’ => ‘desc’));
    $key = ‘User.id';
    $format = ‘%User.first_name% %User.last_name% (%User.login%)”;
    $this->User->generateList($options, $key, $format);

    This should output something like this:
    John Smith (john.smith)

    I hope You it will be usefull :) Any comment are welcome.

  • http://teknoid.wordpress.com teknoid

    @emka

    Thanks for sharing.

  • http://www.kangaroot.net Dieter@be

    Thanks for the article teknoid and thanks for the “multi model” improvent scs.

    I have a tiny fix for scs’ code though:

    in the loop, I put:
    for($i = 1; $i <= 2; $i++) {
    if($options['recursive'] alias.’.’ . $options['fields'][$i];
    } else {
    $field[$i] = $options['fields'][$i];
    }
    }

    this prepends the modelname automatically if you’re only working with one model. Otherwise you get an (harmless) notice in the Set::combine call.

  • http://teknoid.wordpress.com teknoid

    @Dieter@be

    Thanks for sharing.

  • http://dieter.plaetinck.be Dieter_be

    Warning!
    The find function from this appmodel assumes you only use the “new find style” with 2 params: find (type, params).
    But even if you don’t use the old style – find(conditions,fields,order,recursive) – yourself, some of cakes’s own functions (at least in 1.2) still use it. for example the field() function in libs/model/model.php to name one.
    Cake’s (1.2) find function works with both styles, but the one in this post only works with the new style. I found this out when debugging why my ‘$order’ param in my field() call didn’t work.

    The fix, luckily, is very simple. in your appmodel:
    change the function signature to something like this:
    function find($type, $options = array(), $p2 = null, $p3 = null) {

    and in the default (not-superlist) case, do this:
    return parent::find($type, $options, $p2, $p3);

    Dieter

  • Cody Lundquist

    Thanks dude :). I modified it a bit so I didn’t have to use ‘superlist’ but just ‘list’ and do the check for 3 fields to determine if I need to use your code or not.

  • teknoid

    @Cody Lundquist

    Nice, but sorry to hear you are still using 1.2

    It is much easier now in 1.3… ;)

  • http://www.opencanopi.com mparic

    Excellent post; after three years of developing in 1.1 I’m finally able to start a new project with 1.3. What a relief. Thanks again!

  • teknoid

    @mparic

    Thank the developers for putting this new nice feature (better late than never ;))

  • http://trihoprojects.com Tri

    Thanks so much! this is extremely helpful :)

  • http://pedroelsner.com Pedro Elsner

    Hello,
    It’s very perfect!
    If you want, do you can change line 12 and able the function to receive recursive param:

    if(!isset($options['recursive'])) {
    $options['recursive'] = -1;
    }

    Thanks!
    Good luck!

  • http://pedroelsner.com Pedro Elsner

    Hi!
    Sorry but to able recursive param, you need change after line #12:

    if(!isset($options['recursive'])) {
    $options['recursive'] = -1;
    }

    $list = parent::find(‘all’, $options);

    return Set::combine( $list, ‘{n}.’.$this->alias.’.’.$this->primaryKey, array( ‘%s’.$options['separator'].’%s’, ‘{n}.’.$options['fields'][1], ‘{n}.’.$options['fields'][2]) );

  • Pingback: CakePHP – Multiple Fields in Dropdown (select) List « Glen Draeger's Web Development Blog

  • Thomas

    Hi,
    This seems to be resolved in Cake 2.0. Just add in your model:

    public $virtualFields = array(“full_name”=>”CONCAT(first_name, ‘ ‘ ,last_name)”);
    public $displayField = ‘full_name';

  • teknoid

    @Thomas

    Virtual fields, as mentioned at the top of post were added later in 1.3