Editing multiple records with saveAll()

Another interesting topic was brought up on IRC channel today…

(By the way if you don’t know about the awesome CakePHP IRC channel, you really should visit, it is a great place to get help and learn a thing or two… irc://irc.freenode.net/cakephp)

Anyways, moving on… How do we edit multiple records at once by using the good ol’ saveAll() method?

One immediate problem is that when we do $this->SomeModel->find(‘all’);, the data array is formatted like this:

Array
(
    [0] => Array
        (
            [Profile] => Array
                (
                    [id] => 21
                    [name] => bob 3
                    [created] => 2008-10-27 13:01:30
                )

        )

    [1] => Array
        (
            [Profile] => Array
                (
                    [id] => 20
                    [name] => larry 5
                    [created] => 2008-10-27 13:01:30
                )

        )

However, we know that in order for saveAll() to work correctly the data should be formatted like this:


[Profile] => Array
        (
            [21] => Array
                (
                    [id] => 21
                    [name] => bob 3
                    [created] => 2008-10-27 13:01:30

                )

            [20] => Array
                (
                    [id] => 20
                    [name] => larry 5
                    [created] => 2008-10-27 13:01:30
                )

Notice the difference of the key placement and the array structure?

Alright, let’s examine a super-easy way to get what we need by using the lovely Set class, and namely the Set::combine() method.

First, we build our edit action keeping in mind that we need the data in a form to follow a specific format for saveAll():

[code language=”html”]
function edit() {
if(!empty($this->data)) {
$this->Profile->saveAll($this->data[‘Profile’]);
}
else {
$this->data[‘Profile’] = Set::combine($this->Profile->find(‘all’), ‘{n}.Profile.id’, ‘{n}.Profile’);
}
}
[/cc]

We use Set::combine() to easily reformat the return of find(‘all’) (to look just like an example array above) and, of course, assign it to $this->data, which is going to be used by the Form Helper.

Now, all we need to do is build our form (let’s make something really simple):

[code language=”php”]
echo $form->create(‘Profile’, array(‘action’=>’edit’));

foreach($this->data[‘Profile’] as $key => $value) {
echo $form->input(‘Profile.’.$key.’.name’);
echo $form->input(‘Profile.’.$key.’.id’);
}

echo $form->end(‘Save All Profiles’);
[/cc]

This form allows us to rename multiple Profiles at once. It is built from our $this->data array and follows exactly the right format, which allows the saveAll() method to work correctly.

m4s0n501
  • Phally

    Great to see you are doing something with question from the IRC channel! It is good that this is finally clearly documentated. Things like these can really bring up frustrations.

  • http://www.neilcrookes.com Neil Crookes

    Do you have any thoughts on how you should go about validating the data submitted in each profile?

  • http://teknoid.wordpress.com teknoid

    @Phally

    I steal most of ideas for the posts from the questions on IRC ;)

  • http://teknoid.wordpress.com teknoid

    @Neil Crookes

    Sure, just do: saveAll($yourData, array(‘validate’=>’first’));

  • Phally

    @teknoid

    Doesn’t matter… Keep on going! It is good that someone writes down solutions to common problems!

  • http://teknoid.wordpress.com teknoid

    @Phally

    Thanks for the encouragement and positive feedback, I really do appreciate it.

  • http://franktank.com Frank

    Nice post, reminds me that I should look into using the Set class more as it seems really handy. Cheers!

  • http://teknoid.wordpress.com teknoid

    @Frank

    Thanks. Yeah Set is full of goodies :)

  • http://nik.chankov.net Nik Chankov

    Nice approach, I didn’t know about saveAll() function, it’s good to know feature. But I have God’s feeling that later it would transform to $this->Model->save(‘all’, $data);

  • http://www.mysiteonline.org/ Brendon Kozlowski

    teknoid, although I didn’t need to format my data array/object for this specific case (actually wanted it reformatted to make it easier to work with JSON, specifically) and knew I’d need to look at the set class to determine which method to use. I can put that off a bit longer now thanks to this article…which may be a good thing or a bad thing, depending… ;) Thanks, as always, for your articles!

  • http://teknoid.wordpress.com teknoid

    @Brendon Kozlowski

    No problem, glad it inspires some ideas. That’s the most important thing :)

  • http://teknoid.wordpress.com teknoid

    @Nik Chankov

    Hmmm, interesting thought :) I don’t know… I can’t imagine that save() would have a lot of different types… but maybe it could become save(‘field’), update(‘all’)… etc. I guess we’ll wait and see.

    All that being said, saveAll() is actually very powerful, I have a few other posts showing how useful it can be to avoid nasty foreach() loops, etc. and of course to save multiple models at once.

  • http://www.fianchetto.org luke

    teknoid, am sur ethis was my question :) great post!

    boobyW

  • http://teknoid.wordpress.com teknoid

    @luke

    I think it came from alkemann, unless of course you are also him ;)
    At any rate, if it helped… I’m happy :)

  • rafaelbandeira3

    Is it a bird? Is it a plane? No, it’s TEKNOID saving the day again and again!
    thanks just saved me some precious time.

  • http://teknoid.wordpress.com teknoid

    @rafaelbandeira3

    No problem ;) Glad to help…

  • Pingback: Сохранение нескольких записей с помощью saveAll | Bunyan.Ru()

  • http://pepa.info/freelance/ Petr ‘PePa’ Pavel

    I would also like to thank you for this post. And because it wasn’t clear to me at first glance I would like to mention that your solution automagically fills the form input boxes with database values loaded using find(‘all’) and Set::combine().

    A truly cakish solution.
    Thanks again
    Petr

  • http://teknoid.wordpress.com teknoid

    @Petr

    I’m glad it helped out ;)

    Cheers

  • antispin

    Didn’t work for me but should have :) it just kept saving a single blank record instead of my data. Had to use a loop and saveField() instead… just an alternative in case this happens to someone else.

  • antispin

    ^^ should have pointed out that using saveField() is a HORRIBLE, INEFFICIENT, UGLY alternative but it does work. Teknoid’s solution is much better.

  • http://teknoid.wordpress.com teknoid

    @antispin

    Don’t give up so quick :) http://bin.cakephp.org/
    Post your before/after arrays and save() code.

  • Pat

    Hi All:

    Great article Teknoid. I’m curious to know how this might work with the example you used in one of your HABTM multiple-record save examples used in some of your other posts (example: Accounts->Contacts). Would there be a short-hand method of pulling up all the data for the parent record, along with all the children records and do a mass save after doing edits to those records/fields?

    Thanks, as usual, for all the helpful articles and active participation in the community.

  • http://teknoid.wordpress.com teknoid

    @Pat

    The approach would be the same, the important thing to keep in mind is that your data array must adhere to the right format and that field names in the view are properly named.

    … and thanks for the kind words ;)

    • Pat

      @Teknoid

      Thanks for the quick reply!

      So, then, would I be able to achieve this simply by doing a recursive find from the parent model? For example, doing the recursive find on Accounts; which would return the Accounts record with the desired id and all the Contacts records with the same foreign key id made in the find query.

      Would I be correct in that assumption? Or am I way off base? :-)

  • http://teknoid.wordpress.com teknoid

    @Pat

    No, you are correct.
    The only thing you might need to do is to “massage” your data array as in the example.
    Again, it depends on the returned array, which is formatted according to the associations between the models. (i.e. hasOne is a little different from hasMany or HABTM)

  • Rokas

    find(‘all’) also can format that array with

    # echo $form->create(‘Profile’, array(‘action’=>’edit’));
    #
    # foreach($profiles as $profile) {
    # echo $form->input(‘Profile.’.$profile[‘Profile’][‘id’].’.name’);
    # echo $form->input(‘Profile.’.$profile[‘Profile’][‘id’].’.id’);
    # }
    #
    # echo $form->end(‘Save All Profiles’);

  • http://teknoid.wordpress.com teknoid

    @Rokas

    Sure, but if your data array doesn’t match the field structure the fields will not be populated, unless there’s more magic in the core now since I’ve wrote this.

  • Aldo

    What happens if a the content of a field has been deleted?
    saveAll does not delete the empty record from the database.
    Any idea how to handle that?

  • http://teknoid.wordpress.com teknoid

    @Aldo

    How do you wind up with “empty” records in the first place?

  • Aldo

    Let’s say in your example the name “bob 3″ is deleted from the input field.

    [Profile] => Array
    (
    [21] => Array
    (
    [id] => 21
    [name] =>
    [created] => 2008-10-27 13:01:30

    )

    [20] => Array
    (
    [id] => 20
    [name] => larry 5
    [created] => 2008-10-27 13:01:30
    )

    After saving, the row in the DB still remains, but empty.
    I’m looking for a way to check this empty fields, so the record, corresponding to this field is deleted while the reamining entries are updated/saved.

  • http://teknoid.wordpress.com teknoid

    @Aldo

    That should be handled by validation, if the name is not present, the form should fail.
    This is accomplished with ‘validate’ => ‘first’ (see some other saveAll posts here).

    Or if someone tampers with the form data use the Security component to ensure form integrity (I have a few posts about that as well).

    That being said, if you are trying to delete the record on purpose, there is no automated way to do it with saveAll(). Nor, do I think it should be handled in same manner as editing/saving anyway.

  • Joe Cabezas

    thank you, it helps me a lot!

    greetings from Chile, LA !!

    bye!

  • http://teknoid.wordpress.com teknoid

    @Joe Cabezas

    Good to hear. You’re welcome.

  • Nancy M

    What if you have new rows mixed in with existing ones? Lets say I want my edit form to pop up an empty form to add another row, but also save the existing ones. So far the only solution I’ve found is awful, I have to assign a random ID, and it could conceivably reuse one already out there.

  • http://godine.in/ Harsha M V

    http://harshamv.com/files/screenshots/2009-09-10_0201_001.png

    Is there any way to unset that marked element of the to so i dont get a blank entry in the database.

    Note: The entering of the Phone has an ajax feature where users can add extra field on the go to enter extra number. taking into consideration some one clicked add field by mistake and then submitted the form.

  • http://godine.in/ Harsha M V

    http://harshamv.com/files/screenshots/2009-09-10_0201_001.png

    Is there any way to unset that marked element of the to so i dont get a blank entry in the database.

    Note: The entering of the Phone has an ajax feature where users can add extra field on the go to enter extra number. taking into consideration some one clicked add field by mistake and then submitted the form.

  • http://godine.in/ Harsha M V

    http://harshamv.com/files/screenshots/2009-09-10_0201_001.png

    Is there any way to unset that marked element of the to so i dont get a blank entry in the database.

    Note: The entering of the Phone has an ajax feature where users can add extra field on the go to enter extra number. taking into consideration some one clicked add field by mistake and then submitted the form.

  • http://godine.in/ Harsha M V

    http://harshamv.com/files/screenshots/2009-09-10_0201_001.png

    Is there any way to unset that marked element of the to so i dont get a blank entry in the database.

    Note: The entering of the Phone has an ajax feature where users can add extra field on the go to enter extra number. taking into consideration some one clicked add field by mistake and then submitted the form.

  • http://teknoid.wordpress.com teknoid

    @Harsha M V

    In your beforeSave() you should “step” through the array, and look for any empty values. If one is found, unset the relevant key.

  • BiBi

    thanks very much, class Set is very cool.

  • Ishrar

    Dear teknoid,

    Thank you so much for the amazing code using Set and your detailed explanation.

    The problem I’m now facing is that instead of a single find(‘all’) query, I need to I have a union of two queries. I’m trying to do something like this:

    $result1 = Set::combine($this->MyModel->find(‘all’, array(…)), ‘{n}.MyModel.id’, ‘{n}.MyModel’);

    $result2 = Set::combine($this->MyModel->find(‘all’, array(…)), ‘{n}.MyModel.id’, ‘{n}.MyModel’);

    Any idea on how I can concatenate the two results into $this->data[‘MyModel’] properly?

    I looked at Set::merge(), hoping to do something like this:

    $this->data[‘MyModel’] = Set::merge($result1,$result2);

    But, after reading the doc, I understand that the array indices may not be properly serialized in that way. Let me know if you have any idea on how to solve this issue.

    Thanks again for the wonderful posts.

  • teknoid

    @Ishrar

    If you have matching keys, then any kind of array merging will be impossible.
    Sometimes a good ol’ foreach() loop is a simple and effective method.

    But it’s hard to guess what’s really happening unless exact array structure is shown. You can put in the paste-bin and others can chime in here or on the IRC channel.

  • Beginner In PHP

    Hi

    I have a doubt on the saveAll function
    I tried to save a set of records to a table
    that formatted array was in the format

    [SearchResult]=>Array
    (
    [0] => Array
    (
    [order_id] => 4ddf63df-bb58-4341-87ad-049e8fa129d9
    [employer_id] => 4d2e9c9c-0648-4e40-82f7-062e8fa129d9
    [candidate_id] => 4d2e9b33-3378-469c-8413-062f8fa129d9
    )

    [1] => Array
    (
    [order_id] => 4ddf63df-bb58-4341-87ad-049e8fa129d9
    [employer_id] => 4d2e9c9c-0648-4e40-82f7-062e8fa129d9
    [candidate_id] => C1
    )

    [2] => Array
    (
    [order_id] => 4ddf63df-bb58-4341-87ad-049e8fa129d9
    [employer_id] => 4d2e9c9c-0648-4e40-82f7-062e8fa129d9
    [candidate_id] => C10
    )
    )

    There are like 300 records in this table, but the saveAll is storing the results in some other order, which makes it difficult for me to understand it while viewing the records
    Can someone please help me with this issue

  • teknoid

    @Beginner In PHP

    Doesn’t matter how it stored them… it’s how you order by when viewing that matters.
    Typically the last record inserted will be the last one that shows, but that can also depend on your DB client.

  • Matt Weg

    If you would like your validation to run a little smarter and display the error messages for the specific record and field that is causing the error you can do something like this.

    $profileValidates = true;
    $profileErrors = array();
    foreach($this->data['Profile'] as $i=>$profile) {
      $this->Profile->set(array('Profile'=>$profile));
      if(!$this->Profile->validates()) {
        $profileValidates = false;
        $profileErrors[$i] = $this->Profile->validationErrors;
      }
    }
    if(!$profileValidates) {
      $this->Profile->validationErrors = $profileErrors;
      return false; //or whatever you want to do to send them back to the form
    }