PHP RedBean store bean if not exist one

Question

I am a little confused. I actively use PHP RedBean as ORM within my direct mail service and I run into curious situation - I have a table with unique key constraint (i.e. subscriber_id, delivery_id) and two scripts that is writing data into this table. There is source code that is inserting or updating table:

public static function addOpenPrecedent($nSubscriberId, $nDeliveryId)
{
    $oOpenStatBean = \R::findOrDispense('open_stat', 'delivery_id = :did AND subscriber_id = :sid', array(':did' => $nDeliveryId, ':sid' => $nSubscriberId));

    $oOpenStatBean = array_values($oOpenStatBean);
    if (1 !== count($oOpenStatBean)) {
        throw new ModelOpenStatException(
            "Ошибка при обновлении статистики открытий: пара (delivery_id,
            subscriber_id) не является уникальной: ($nDeliveryId, $nSubscriberId).");
    }

    $oOpenStatBean = $oOpenStatBean[0];
    if (!empty($oOpenStatBean->last_add_dt)) {
        $oOpenStatBean->precedent++;
    } else {
        $oOpenStatBean->delivery_id   = $nDeliveryId;
        $oOpenStatBean->subscriber_id = $nSubscriberId;
    }

    $oOpenStatBean->last_add_dt = time('Y-m-d H:i:s');
    \R::store($oOpenStatBean);
}

It is called both from two scripts. And I have issues with corruption unique constraint on this table periodically, because race conditions occurs. I know about SQL "INSERT on duplicate key update" feature. But how can I obtain same result purely using my ORM?

What you're trying to do is called an "upsert". Try googling for that. I've found this discussion about it: https://github.com/gabordemooij/redbean/issues/160 — Botond Balázs, Nov 17 '12 at 09:44

Reid Johnson · Accepted Answer · 2014-09-03T18:29:37.197

Current, that I know if, Redbean will not issue an

INSERT ON DUPLICATE KEY UPDATE

as the discussion of this cited in the comments above indicates that Redbean's developer considers upsert to be a business logic thing that would pollute the ORM's interphase. This being said, it is most likely achievable if one were to extend Redbean with a custom Query Writer or plugin per the Documentation. I haven't tried this because the method below easily achieves this behavior without messing with the internals and plugins of the ORM, however, it does require that you use transactions and models and a couple of extra queries.

Basically, start your transaction with either R::transaction() or R::begin() before your call to R::store(). Then in your "FUSE"d model, use the "update" FUSE method to run a query that checks for duplication and retrieves the existing id while locking the necessary rows (i.e. SELECT FOR UPDATE). If no id is returned, you are good and just let your regular model validation (or lack thereof) continue as usual and return. If an id is found, simply set $this->bean->id to the returned value and Redbean will UPDATE rather than INSERT. So, with a model like this:

class Model_OpenStat extends RedBean_SimpleModel{
  function update(){
     $sql = 'SELECT * FROM `open_stat` WHERE `delivery_id`=? AND 'subscriber_id'=? LIMIT 1 FOR UPDATE';
     $args = array( $this->bean->deliver_id, $this->bean->subscriber_id );
     $dupRow = R::getRow( $sql, $args );
     if( is_array( $dupRow ) && isset( $dupRow['id'] ) ){
        foreach( $this->bean->getProperties() as $property => $value ){
          #set your criteria here for which fields
          #should be from the one in the database and which should come from this copy
          #this version simply takes all unset values in the current and sets them
          #from the one in the database
          if( !isset( $value ) && isset( $dupRow[$property] ) )
            $this->bean->$property = $dupRow[$property];
        }
        $this->bean->id = $dupId['id']; #set id to the duplicates id
     }
     return true;
  }
}

You would then modify the R::store() call like so:

\R::begin();
\R::store($oOpenStatBean);
\R::commit();

or

\R::transaction( function() use ( $oOpenStatBean ){ R::store( $oOpenStatBean ); } );

The transaction will cause the "FOR UPDATE" clause to lock the found row or, in the event that no row was found, to lock the places in the index where your new row will go so that you don't have concurrency issues.

Now this will not solve one user's update of the record clobbering another, but that is a whole different topic.

No, unfortunately, as per the cited discussion, true upsert functionality was deemed to be business logic and is not integrated into the Redbean core. You can, however, write your own custom query writer or plugin that will move this kind of thing into the orm. Information about writing these is in the [documentation](http://www.redbeanphp.com/plugins/create_your_own) — Reid Johnson, Sep 02 '14 at 18:08
I know. I had the same issue and the cited discussion commented to your original question was the only source. I have kept searching on and off for an answer and figured, by providing the only one I have been able to find here, those with this question could get a little more than just the "it isn't supported" answer and see a working, if slightly less elegant, solution. — Reid Johnson, Sep 02 '14 at 18:17
I think if you update your answer and write about impossibility of doing it directly through ORM answer will became correct. — Ivan Velichko, Sep 02 '14 at 18:28
I updated the answer to clarify that the solution offered is not inside the ORM and also to include the information about the possibility of making a plugin or custom query writer. Thank you for accepting this answer so long after the fact. I hope that having it here will help others on their quest to use Redbean. — Reid Johnson, Sep 03 '14 at 18:30

PHP RedBean store bean if not exist one

1 Answers1