Yii don't support Model based on Database View?

I've created a view on PostgresSQL based on some fields of two tables. And i've defined a rule that make this view support the insert command.

When someone try to insert datas in the view the rule insert the datas into the two tables.

The problem: When I try to create the model of this view Yiic say:

Quote

>> model ClienteLogin

Warning: the table 'ClienteLogin' does not exist in the database.

  generate ClienteLogin.php

The 'ClienteLogin' class has been successfully created in the following file:

    /home/hoteltest/public_html/protected/models/ClienteLogin.php

If you have a 'db' database connection, you can test it now with:

    $model=ClienteLogin::model()->find();

    print_r($model);

And so it can't create the crud of the view…

ok my fault :D i typed the wrong name of table TT

Nope, Yii AR currently doesn't support views.

Are you sure? Couse I can create a Model of a View.

Are you saying that i can't use save() on that object model?

Actually, my conclusion may be wrong because we didn't design AR to support view from the very beginning. However, according to your result, it seems AR does work with view naturally. That is a very good news indeed. Thank you!

What would the advantage be of being able to model a database view?

I was under the impression that you cannot complete and insert function with a database view that contains a join, which is essentially what a database view is.

Again it might be a stupid question but interested to understand in more depth

You can use views to create reporting style queries, those that tend to include lots of aggregate functions (e.g. group by, count, sum), unions, etc, and to flatten the tables in to 2nf or 1nf via the view. This makes selects very simple and easy to use with AR. Of course, usually you can not update the data via the view.

However, is not good to generalize that views are supported by Yii. It is a surprise for me, but I guess that mysql excepcionally works, but I don't think so about the others DBMS.

AR will support view if the database give to AR the access to the view like a table. MySQL, PostgreSQL and (from my memories) MsSQL will give you these opportunities.

If you look at this code:

        /**


         * Returns the metadata for all tables in the database.


         * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema.


         * @return array the metadata for all tables in the database.


         * Each array element is an instance of {@link CDbTableSchema} (or its child class).


         * The array keys are table names.


         * @since 1.0.2


         */


        public function getTables($schema='')


        {


                $tables=array();


                foreach($this->getTableNames($schema) as $name)


                        $tables[$name]=$this->getTable($name);


                return $tables;


        }


Uhm … well, now we go for example in the PostgreSQL driver to see the function getTableNames():

"db/schema/pgsql/CPgsqlSchema.php"



/**


         * Returns all table names in the database.


         * @return array all table names in the database.


         * @since 1.0.2


         */


        protected function findTableNames($schema='')


        {


                if($schema==='')


                        $schema=self::DEFAULT_SCHEMA;


                $sql=<<<EOD


SELECT table_name, table_schema FROM information_schema.tables


WHERE table_schema=:schema


EOD;


                $command=$this->getDbConnection()->createCommand($sql);


                $command->bindParam(':schema',$schema);


                $rows=$command->queryAll();


                $names=array();


                foreach($rows as $row)


                {


                        if($schema===self::DEFAULT_SCHEMA)


                                $names[]=$row['table_name'];


                        else


                                $names[]=$row['schema_name'].'.'.$row['table_name'];


                }


                return $names;


        }


OK, this function performs a query on the database, specifically:

SELECT table_name, table_schema FROM information_schema.tables


WHERE table_schema=:schema
where  ":schema" will be replaced with "public", the default schema in PostgreSQL.

Now you can try to create some views and execute the query above, so you will see the views in your resultset.

Views in PostgreSQL does not have write support natively, but there may be associated with the rule that simulate write access by performing a series of commands to the occurrence of events of INSERT, UPDATE, DELETE.

http://www.postgresq…les-update.html

So, in this way you can create a view that can be used to create the model and the crud, use AR to insert/modify/delete a record, doing a query on a single object, but executing all command provided by the rule.

MySQL has a partial support of the view in writing, so i don't know if you can define a workaround like RULE on PostgreSQL:

http://dev.mysql.com…datability.html

For the other DB check the relative documentation, however i think that there is many advantages in using the view if the database allows certain operations.

IMHO using PostgreSQL this is the better solution, because it keeps the application tiny.

Instead in case this is not possible … beh, a more flexible AR that give you the opportunity to get multiple queries from a single save() is the unique solution.

I hope I have written in a comprehensible manner, I don't write very well in English:)

Great job made :)

Talking about defining rules on a view a person on #postgresql chan in freenode.net said this:

why do you think you have to do it? it really is a BAD IDEA

firstly, it can't be made concurrency-safe. secondly, it can't ever work for multiple-row inserts.

the RULE system is like a macro pre-processor in C. extremely handy for the cases it covers, totally the wrong tool much of the time.

thirdly, you'll find it never works exactly as you expect (even experts can't predict the behaviour of nontrivial rules reliably)

Theese are bad things. Very very bad. I think that the best way is to define the model and use raw sql ( with transaction, couse we have to retrive the ID of the first record inserted and use it like FK in another table record insert ) to insert datas.

This my personal idea at the moment. Someone have better suggests?

Interesting reading people. Thanks for the info. I have a much clearer understanding now

I will try to enter a transaction in a rule, to assess what is the capacity to ensure effective competition, then going to perform some test … I understand that perhaps the rule are not born for that, however I really seem so comfortable …

Uhm I thought about it. I think that the best thing to do is to create a function that use a transaction, and in the rule call the function.

I think that the problem is that in the rule you have to use simply query and not complex.

I agree with the opinion that is a bad idea to use a view instead table on models. I think it is unsafe, since (I guess) you can't do crud operations (just the read).

One idea is Yii implementation of a CView class, that could read especifically just views, with no options to insert, update or delete. Just a queriable class.

Sorry, for answering to very old post!

I was informed (though wasn’t able to confirm this yet) that when using Oracle RDBMS you can use view as normal table in any operation, including whole CRUD. Oracle itself presents view as normal table and supports operations like update on view, only developer must code all the logic of such operations on aside DB with PL/SQL stored procedures.

Will post more if I’ll be able to confirm that.

Sorry, about what class you are talking about? Haven’t found such in class reference. Only CViewAction and CViewRenderer.