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.
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'
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.
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,
jenisbarang_idaa01,
cabangidbb04,
areaidbb00,
namacabangbb04,
areabb06,
kodebarangbb00,
namabarangbb00,
tipebb00,
warnabb00,
sizeboxbb00,
satuanbb00,
sum(jumlahmasuk) as totalmasuk,
sum(jumlahkeluar)as totalkeluar,
sum(jumlahmasuk)-sum(jumlahkeluar) as totalstock from
view06_hasilunion
aa07
Group by cabangidbb04, areaidbb00, jenisbarang_idaa01
order by cabangidbb04, areaidbb00, jenisbarang_idaa01 ;
Make dummy table that has same structure with your Create View. Make sure the id field is primary key.
Fill your table with dummy data.
Generate Model with gii.
Generate CRUD.
Delete that dummy table.
Rename your Create View same with that dummy table name.
Finish.
Note : I use it,just to get use of the search tools in Manage Admin View.
It can not be use to add, delete or edit/update data.
