Unexpected behaviour working with updatable view

Question

Unexpected behaviour working with updatable view

Alexey Telgerov Nov '13

Created Nov '13	Last Reply Jan '14	Replies 3	Views 1424	Votes 1

Alexey Telgerov 2.1k

Nov '13

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:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1471 The target table wide_exchange_rates 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('wide_exchange_r...', Array, Array, Array)
#4 [internal function]: Phalcon\Mvc\Model->_doLowInsert(Object(Phalcon\Mvc\Model\MetaData\Memory), Object(Phalcon\Db\Adapter\Pdo\Mysql), 'wide_exchange_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:

$this->modelsManager->executeQuery("update WideExchangeRates set rate = 1 where source_real = target_real");

Obviously, Phalcon tries to insert record, even the update method is used directly. But why? I didn't overwrite any standart Model methods..

1

Alexey Telgerov · Answer 1 · 2013-11-01T04:07:19-07:00

Alexey Telgerov
2.1k

Nov '13

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

Phalcon · Answer 2 · 2013-11-01T11:29:20-07:00

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.

https://docs.phalcon.io/en/latest/reference/models.html#manual-meta-data

Alexey Telgerov · Answer 3 · 2013-11-04T08:02:38-07:00

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:

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:

    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...

    public function testAction()    {
        $view =  TestView::find();
        foreach ($view as $row) {
            if ($row->id == 1) 
                $row->update(['value' => 50]);
        }
    }

I see the message:

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.

Herdian Yoki · Answer 4 · 2013-12-30T23:10:51-07:00

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'.

le51 · Answer 5 · 2014-01-01T13:51:44-07:00

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) ?