Code snippets for symfony 1.x

Navigation

update query using Propel

When you need to update several records in a row, you don't have to loop over the result of a doSelect() call and do a save() for each object (which would make way too many queries).

Instead, you can use the BasePeer method doUpdate() as follows:

BasePeer::doUpdate($select_criteria, $update_criteria, $connection);

For instance:

$con = Propel::getConnection();
 
// select from...
$c1 = new Criteria();
$c1->add(CommentPeer::POST_ID, $post_id);
 
// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);
 
BasePeer::doUpdate($c1, $c2, $con);

If course, if you are in a Peer class, you will need to use the self object:

$con = Propel::getConnection();
 
// select from...
$c1 = new Criteria();
$c1->add(self::POST_ID, $post_id);
 
// update set
$c2 = new Criteria();
$c2->add(self::RATING, 5);
 
BasePeer::doUpdate($c1, $c2, $con);
by Francois Zaninotto on 2006-07-03, tagged criteria  propel 

Comments on this snippet

gravatar icon
#1 Olivier Verdier on 2006-07-03 at 08:33

Excellent! That method comes in very handy! Just a remark: I think that the $connection parameter is optional, as in nearly all the other propel methods. So you can simply call:

gravatar icon
#2 Olivier Verdier on 2006-07-03 at 08:35

My code disappeared from the previous comment... 8-(

gravatar icon
#3 Olivier Verdier on 2006-07-03 at 08:36

What the F***! The code disappeared twice! Here is the code without syntax colouring then: BasePeer::doUpdate($c1, $c2)

gravatar icon
#4 Piotr Belina on 2006-07-12 at 01:45

DO you have idea how to do update like this usign Criteria?

UPDATE table SET col = col + 1

gravatar icon
#5 Sigurd Palladin on 2006-08-10 at 12:57

2Piotr Belina: $c2->add(self::RATING, "rating = rating + 1", Criteria::CUSTOM); note that this is true for Criteria v1.0 More info can be found on http://propel.phpdb.org/trac/wiki/Development/Criteria

gravatar icon
#6 sampq on 2007-12-05 at 06:05

If you have several databases be careful to specify the name of the database for the connection and the criterias.

Like this:

$con = Propel::getConnection(self::DATABASE_NAME); ... $c1 = new Criteria(self::DATABASE_NAME); ... $c2 = new Criteria(self::DATABASE_NAME); ...

Otherwise propel will use the default database and you will get an error if you don't use this one (this is because the code use a BasePeer static method and not a CommentPeer one).

gravatar icon
#7 Julien Moreau on 2008-03-03 at 05:04

You should also have a look to this: http://propel.phpdb.org/trac/wiki/Users/Documentation/1.2/BasicCRUD#UPDATE

gravatar icon
#8 Benoit Bonneville on 2008-11-06 at 12:16

BE CAREFUL, this method doesn't work : $c2->add(self::RATING, "rating = rating + 1", Criteria::CUSTOM); You will always get : UPDATE table SET rating = 0.

In order to do this you must use Criteria::CUSTOM_EQUAL, available if you are using propel 1.3. (http://propel.phpdb.org/trac/wiki/Users/Documentation/1.3/Criteria)

I'm open to any suggestion on Propel 1.2 (some solutions could be found there : http://www.symfony-project.org/forum/index.php/m/36523/?srch=doUpdate)

More of that, it's better to use self::RATING and not "rating" which will make harder refactoring table columns.