Increment value in database using Propel

Question

I'm new to propel, and I'm looking for a way to increment a value in my MySQL database, without having to do a full read-update-write cycle. Such as this:

UPDATE books SET popularity = popularity + 1 WHERE id = 123

Of course I can do:

$book = new BookQuery::create()->findPk(123);
$book->setPopularity($book->getPopularity() + 1);
$book->save();

But that would result in 2 queries (the SELECT and the UPDATE).

Is there a neat way to do this in Propel?

Buffon

You can do straight SQL in propel; otherwise, you could use a combination of create()->filterBy()->update() to accomplish what you want. – Situate

I know I can do straight SQL in Propel, but I just want to understand how to solve this with Propel. Also if I use update(), what would the syntax look like to increment a value? – Curkell

Answer 1

Is there a neat way to do this in Propel?

No, but there is a way. ;) You can use the Criteria::CUSTOM_EQUAL parameter with Criteria->add():

$con = Propel::getConnection( BooksPeer::DATABASE_NAME, Propel::CONNECTION_WRITE );
$whereCriteria = BooksQuery::create()->filterById( 123 );
$valuesCriteria = new Criteria( BooksPeer::DATABASE_NAME );
$valuesCriteria->add( BooksPeer::POPULARITY, BooksPeer::POPULARITY . " + 1", Criteria::CUSTOM_EQUAL );
BasePeer::doUpdate( $whereCriteria, $valuesCriteria, $con );

This is how the sortable behavior implements rank shifting.

Pregnancy

Answer 2

BasePeer::doUpdate( $whereCriteria, $valuesCriteria, $con );

doUpdate() is a private function that can not be used outside.

Salade