Unexpected behaviour working with updatable view

Hi all. I've got a "create" action instead of update trying iterate an updatable view:

CREATE or replace VIEW wide_exchange_rates AS
 SELECT R.id, R.source_currency_name, C.real_currency AS source_real,
  R.target_currency_name, D.real_currency AS target_real, R.rate, R.refresh_date
 FROM `exchange_rates` AS R
  LEFT JOIN currencies AS C ON (R.source_currency_name = C.name)
  LEFT JOIN currencies AS D ON (R.target_currency_name = D.name)

And neither model operations nor modelManager dont work as I want:

$concurrentPairs = WideExchangeRates::find();
        foreach ($concurrentPairs as $pair) {
            if ($pair->source_real == $pair->target_real) {
                $pair->rate = 1;
                $pair->update();
            }

throws exception: ```sh PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1471 The target table wideexchangerates of the INSERT is not insertable-into' in tasks/CurrenciesTask.php:75 Stack trace:

0 [internal function]: PDOStatement->execute()

1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array)

2 [internal function]: Phalcon\Db\Adapter\Pdo->execute('INSERT INTO `wi...', Array, Array)

3 [internal function]: Phalcon\Db\Adapter->insert('wideexchanger...', Array, Array, Array)

4 [internal function]: Phalcon\Mvc\Model->doLowInsert(Object(Phalcon\Mvc\Model\MetaData\Memory), Object(Phalcon\Db\Adapter\Pdo\Mysql), 'wideexchange_r...', false)

5 [internal function]: Phalcon\Mvc\Model->save()

6 ... tasks/CurrenciesTask.php(75): Phalcon\Mvc\Model->update()

7 [internal function]: CurrenciesTask->loadRateAction()

```

The same I've got with modelsManager: ```php $this->modelsManager->executeQuery("update WideExchangeRates set rate = 1 where sourcereal = targetreal"); ``` Obviously, Phalcon tries to insert record, even the update method is used directly. But why? I didn't overwrite any standart Model methods..

And what's wrong with sql and sh highlighter?:)



81.2k

It seems that the ORM cannot identity which column (or columns) are the primary key of the table. Without a primary key it can't know if an update or an insert must be performed. You can try to implement a custom meta-data for this model so this way the ORM can know which columns are the primary key.

http://docs.phalconphp.com/en/latest/reference/models.html#manual-meta-data

I'm sorry, but I haven't found out how to set primary key for the MySQL view manually. In simplest case I've created a table and derived view: ```sql CREATE TABLE IF NOT EXISTS test ( id int(11) NOT NULL AUTO_INCREMENT, value int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW test_view AS select test.id AS id,test.value AS value from test; ```

Manual meta-data: ```php public function metaData() { return array( //Every column in the mapped table MetaData::MODELS_ATTRIBUTES => [ 'id', 'value' ],

        //Every column part of the primary key
        MetaData::MODELS_PRIMARY_KEY => [
            'id'
        ],

        //Every column and their data types
        MetaData::MODELS_DATA_TYPES => array(
            'id' => Column::TYPE_INTEGER,
            'value' => Column::TYPE_INTEGER,
        ),
    );
}

``` Trying to update my view... ```php public function testAction() { $view = TestView::find(); foreach ($view as $row) { if ($row->id == 1) $row->update(['value' => 50]); } } ``` I see the message: ```sh php cli.php currencies test PHP Notice: Undefined index: 9 in app/tasks/CurrenciesTask.php on line 90 The meta-data is invalid or is corrupt ``` Is there any way to do it with Phalcon ORM? May be I should add in metaData something else? Pure SQL-queries and PDO-execute work fine. Thanks.

I have a same issue here when I'm trying to use manual meta-data. When I retrieve the data is not a problem, but when i create/update data it show the error 'The meta-data is invalid or is corrupt'.



25.3k

Hi,

just a noob question: you are using a mysql VIEW, wich is, from my point of view, just a sql SELECT statement. I know that you can build a Model from a VIEW but I ask myself if some kind of CRUD operations on that Model are possible (because of the SELECT nature of the view definition) ?