Active Record with PostgreSQL view

Hello,

I am new to the Yii framework and am walking through some tutorials but have come to a stumbling block. I used Gii to create a class for a customer database view that I have in PostgreSQL. This view joins a customer table with another table holding some purchase information.

That view has column names such as: customer_id, email, password, etc… with customer_id being used as the primary key. I already defined the customer_id as a primary key in the CustomerView class that was auto generated as follows:

//override primaryKey()

public function primaryKey() {

return 'customer_id';

}

I can successfully INSERT a record into the database using this AR class that was generated. However, after record insertion, I am getting a null value returned when I do something like this:

$newCustomer = new CustomerView;

… do my insert into the database

$newCustomer->save();

$retrieveCustomer=CustomerView::model()->findByPk($newCustomer->customer_id);

$retrieveCustomer is empty after this.

I tried doing a print($newCustomer->customer_id) and verified that this value is returned as null.

I can visually verify that the record has been inserted into the 2 tables joined by my customer view table, and customer_id gets auto-incremented as expected on insert per my table rules. So, obviously the value is there in the view when I query it.

Anyhow, I am not sure if I am missing something here. Why wouldn’t $newCustomer->customer_id return the customer_id that shows when I look at the record I just inserted in my database when querying the customer view?

Any help is appreciated.

Thanks!

First of all - did u really need to use view?

I think trouble because view havn’t related sequences and Yii can’t find sequence for retrieve last insert ID. As result, after save of new record primary key ID is unset.

AR model have property $sequenceName - try to set it manually in you CustomerView model to name of sequence of customer table.

First, thanks for the response. I tried overriding getTableSchema and setting sequenceName to the sequence of customer table, but still no luck. Is below the best approach?

As per using a view, I inherited that database structure…so I’d prefer to see if there is a workaround without changing things first.

//override getTableSchema()

public function getTableSchema() {

$table = parent::getTableSchema();

$table->sequenceName = ‘customer_customerid_seq’;

return $table;

}

//override primaryKey()

public function primaryKey() {

return ‘customer_id’;

}

Well, look like on insert $table->sequenceName is reseted to default value (in method ensureTable of CDbCommandBuilder).

I think the faster way to do what you want is


protected function afterSave()

{

  if ($this->getIsNewRecord())

  {

    $pk = $this->_pk;

    $this->$pk = $this->dbConnection->getLastInsertID('sequence_name_here');

  }

}

Thanks so much! Adding a modified afterSave() to my CustomerView model worked like a charm.