Increment a value in a model

Question

Increment a value in a model

de-coy Jul '13

Created Jul '13	Last Reply Jul '13	Replies 2	Views 2221	Votes 1

de-coy 11.3k

Jul '13

Top work, Phalcon is a great framework, looking forward to trying out 1.2.0.

I have a question regarding the best way to increment a value in a model (currently using 1.1.0).

I have a table with a count column:

MyTable: | id | count | | 1 | 23 |

I was originally using MySQL as the database and had a custom UPSERT SQL query: $sql = "INSERT INTO mytable (id, count) VALUES ($id,1) ON DUPLICATE KEY UPDATE count = count + 1";

For a number of reasons I need to migrate to Postgres from MySQL and wondered what is considered to be the best approach now, (Postgres does not support "ON DUPLICATE KEY UPDATE").

Is there an elegant way to achieve this using the Phalcon Model and PDO that will work across databases or would it be more performant to use a Postgres Trigger of some sort?

1

jeffreycahyono · Answer 1 · 2013-07-10T00:02:18-07:00

class Counter extends \Phalcon\MVC\Model{
    public $id;
    public $count;

    public static increment($id){
        $x = self::findFirst($id);
        if(!$x){
            $x = new self();
            $x->count = 1;
        }
        else 
            $x->count++;
        $x->save(); 
    }
}

de-coy · Answer 2 · 2013-07-11T13:51:32-07:00

nice, I like it's elegance, but is there a (slim) possibility of a race condition using this approach?

jeffreycahyono · Answer 3 · 2013-07-12T00:20:39-07:00

jeffreycahyono
27.7k

Jul '13

Hmmm... interesting question. @phalcon, does phql support read lock on table?

de-coy · Answer 4 · 2013-07-12T01:23:28-07:00

I'd rather avoid locking the table as it needs to be written to by multiple users that could be accessing the same or different rows, so table locking would reduce performance as the load increases. The count column holds the number of times the current user has viewed an image so it's likely to be a high workload table.

So the more complete example of what I'm doing is this: |userid|imageid|count| Stack Overflow has a couple of discussions and one points to this discussion about the complexity involved in achieving this in Postgresql... I wish I had known this before I had switched... https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

I'm considering changing the DB design by dropping the count column in favour of a denormalised approach but this could lead to a seriously high number of rows unless I total these values and periodically move them into a second table with a count column every 24 hours or so. Before I go down this route I thought I'd call on the wisdom of the Phalcon Community to see if any had solved this themselves.

de-coy · Answer 5 · 2013-07-17T03:50:42-07:00

Ok, finally found this in the Model docs.

findFirst can take a "for_update" => TRUE parameter, which according to the docs locks the row.

In my case I'm worrying about race conditions unnecessarily as the table is based on the userid so unless the user is logged into two browsers at the same time and hammering the same image it shouldn't ever happen.

Note to self: performance test acripts should not use unique users.