Hello all,
As I exposed in the last post I’m working with SQL VIEWS for security reasons fir a multitenant application (to know more about the reasons http://www.yiiframework.com/forum/index.php?/topic/9474-multi-tenant-appication-doubts/).
The views created are very simple, and have the same structure than the table it comes from but with a where clause (CREATE VIEW v_product AS select * from tbl_product where user()=tenant_identifier).
I’m working with MySQL 5.1.36 and MySQL works with the commands SHOW COLUMNS and SHOW CREATE TABLE for SQL VIEWS, but unfortunately it doesn’t fill the column key properly (it’s always blank), and when is asked for SHOW CREATE TABLE it answer with the commando to create the SQL view and doesn’t appear the foreigns keys of the table where the view is extracted.
When I tried to generate the model with Yii tools I had the problem that it couldn’t generate from the view and I had to generate from the table, and after change the tableName() method. And for list all the records of the view works properly, but the problem comes when I want to view one particular that findByPK method is needed and Yii calls to SHOW COLUMNS to extract the primary key field and gives me an exception.
I’m sure I will have some similar problem when Yii needs to use the foreigns key, to perform better SQL queries.
I would be pleased if someone can answer of these questions:
1- Is possible to work with SQL views in Yii?
2- What solution should I develop to make it works?
3- Is efficiente to ask for each query the primary and foreigns keys to the database, would be possible to improve performance store it in the model?
Than you very much in advance.
Regards:
Kike