We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

automatic select before executing update on primary

I have a mysql table like this:

CREATE TABLE testtbl (
id INT(11) NOT NULL AUTO_INCREMENT,
field1 INT(11) NOT NULL DEFAULT '0',
field2 INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
);
INSERT INTO testtbl (field1, field2) VALUES (1, 1);

My Model is :

class Testtbl extends \Phalcon\Mvc\Model
{
    public $id;
    public $field1;
    public $field2;

    public function initialize() {
    $this->setConnectionService('db');
    $this->skipAttributes(array('field1'));
    }

    public function columnMap() {
        return array(
            'id' => 'id', 
            'field1' => 'field1', 
            'field2' => 'field2'
        );
    }
}

In my controller i have

    $model = new Testtbl();
    $model->id=1;
    $model->field2 = 2;
    if ($model->update() === false) {
        $msg = $model->getMessages();
        print_r($msg);
    }

The db service is set to log the queries in a file. After executing the controller's code the log file contains:

[Sun, 19 Jan 14 13:05:47 +0000][INFO] DESCRIBE testtbl
[Sun, 19 Jan 14 13:05:47 +0000][INFO] SELECT COUNT(*) "rowcount" FROM testtbl WHERE id = ?
[Sun, 19 Jan 14 13:05:47 +0000][INFO] UPDATE testtbl SET field2 = ? WHERE id = ?

Why is there a select before the update ? How can i make the update without any prior select ? (if i update a model received from findFirst, there wont be any SELECT COUNT(*) "rowcount" .. in the log, it appears only when i make an update directly.)

I am using setReadConnectionService('slave server') and setWriteConnectionService('master') in the model. Am i correct to assume that if the slave server is behind the master (lags for whatever reason), the updates will be forever lost as they won't be in the replication binlog ?



2.7k

i really need this :( Besides using raw sql, is there really no solution ? :(



8.1k
edited Jan '15

Try

  //$model = new Testtbl();
    $model = Testtbl::fidfirst(array(
                                    "conditions"    => "id = ?0",
                                    "bind"          => [0 => $id]
                                    ));
  if ( $model !== \FALSE ) { 
       //$model->id=1;
       $model->field2 = 2;
  } else {
       // create record...
  }


2.7k

@Oleg : this will still make a SELECT query on ReadConnectionService and then the UPDATE. on WriteConnectionService.
I do not need/want to validate the existing ID when updating it. I just want to skip the validation which produces "InvalidUpdateAttempt" : Produced when a record is attempted to be updated but it doesn’t exist ( from https://docs.phalcon.io/en/latest/reference/models.html#validation-messages )

I don't see a reason why this validation should be required and unskippable.

I see this as a big problem when you have different servers for read / write. The InvalidUpdateAttempt validation should use the same connection as the write query, otherwise if the read (slave) server is behind the master, updates will be lost because the primary keys don't exist YET on the slave server. You think that $mode->update will do an update, but without checking the actual queries you will have no idea to change the ReadConnection to master first, then do the update, then change ReadConnection to slave again just because there is an unskippable check that you do not need.



2.7k

Nothing for over a year :(

Not really an answer but if someone else has this problem, you may want to check this : https://stackoverflow.com/questions/21217534/phalcon-automatic-select-before-update "the only way to issue one single update query is to provide full metadata and use raw SQL statements with pdo."

This was the reason why i stopped using phalcon as it makes working properly with master-slave servers impossible if you care about performance.