Create a Model and CRUD from Mysql View (instead of table)

Hello, I want to create a Yii Model (and likely CRUD actions) from a MySQL view instead of a table but I’m having problems with code generation trying to search for primary key.

Could any Yii guru try it and get it?

Doesn’t Yii framework support creating a model from a MySQL View?

From what I know and From the point of view of MySQL, a View generated from a table without JOINS or UNIONS beheave almost the same (with inserts, updates and deletes) as a table with the advantage of MySQL results cache. But even with JOINS or UNIONS we should be able to create models, after all it’s a Model.



I understand your problem and i wanted the same as you.

Look here!

If you find an answer or if you have the code please post it here. I try to find it out too.

Sorry, I haven’t checked this post until today,

the problem with views is quite resolvable,

what we have to do is to declare the primary_key function in model class.

class myView extends CActiveRecord{


        public function primaryKey(){

            return 'my_view_id';




But what is not resolvable is another problem, I have used views and its working fine on the development machine.

But when uploaded, it gives this strange error.

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1142 SHOW VIEW command denied to user 'xxx_xxx'@'localhost' for table my_view'

This problem is not resolved yet

Big thanks for pointing this out!

Using this approach, one can quite easily generate the code necessary for Yii-based free-text search which also searches in related database tables. (would supply the link to a working example with source-code, but am not allowed to publish links on the forum…)

That is indeed a system-specific database access violation, and does not relate to the Yii code at least :)

Have your webhost equip your MySQL username with SHOW VIEW privileges for the database.

Has “Yii’s” stance on supporting CRUD operations on a MYSQL view changed since 2010?

I use views to easily have one query that returns numerous rows of properties that would otherwise require 10 or more joins. I just find it it tedious to generate a model/controller/view for each table.

I tried adding the below to the model…

public function primaryKey(){

            return 'my_view_id';


…but i get the following error "Cannot make static method non-static"

note: I’m using Yii2


Hi All,

  1. First make your Create View. Please be alert that first column has id field that can be used primary key.

I make it by concat the id from some table that i use in create view.

example :

CREATE VIEW view08_percabang_perpalet AS select

	concat(jenisbarang_idaa01, cabangidbb04, areaidbb00) AS id,












	sum(jumlahmasuk) as totalmasuk,

	sum(jumlahkeluar)as totalkeluar,

	sum(jumlahmasuk)-sum(jumlahkeluar) as totalstock from 



	Group by cabangidbb04, areaidbb00, jenisbarang_idaa01

	order by cabangidbb04, areaidbb00, jenisbarang_idaa01 ;
  1. Make dummy table that has same structure with your Create View. Make sure the id field is primary key.

  2. Fill your table with dummy data.

  3. Generate Model with gii.

  4. Generate CRUD.

  5. Delete that dummy table.

  6. Rename your Create View same with that dummy table name.

  7. Finish.

  8. Note : I use it,just to get use of the search tools in Manage Admin View.

  9. It can not be use to add, delete or edit/update data.

Hopefully it helps.

Your improvisation is great, thx skunk, will implement it to my project.

Hmmm dummy table, why i dont think that before.

Salam from karawang jabar

Try to override the method primaryKey() like this:

public static function primaryKey()


        return array('my_view_id');


See also:

public static function primaryKey()
        return array('my_view_id');

works fine with Yii2