http://www.symfony-project.org/forum/index.php?t=msg&th=5106&goto=21955#msg_22057

Re: mysql operator hierarchy and propel

for commonly used queries I write a view, and then create the view in my schema with a read-only tag. It's a reasonably clean solution and saves a lot of messing around with Propel.

documented on: 20 February 2007, halfer

> i'm not sure i know what you mean when you say "i write a view"

OK, let's say you have a commonly used select statement involving three or more tables, perhaps with some left joins, or some features (such as subselects) that are tricky to arrange in Propel. My solution is to create a view in my database that performs the select that I require, and then I add this in as a "table" in my schema.?ml file. Then all I do is rebuild the model, and - hey presto - I have a Propel object that relates to a view.

In some databases, providing your view only obtains its columns from one table, you can even write to it (ie an updateable view). On other databases, or if the columns come from a variety of tables, it is a good idea to set the Propel read-only tag in the schema, to prevent saves being attempted.

> gotcha, i've done that kind of thing with sql server. is this possible with
> MySQL?

Apparently so, on MySQL 5 (I used Google to answer your question!). Also what I didn't know was that *some* multiple-table views can be updatable too:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

documented on: 02 May 2007, halfer

Halfer, have you figured out a way to generate the "create view" statement necessary based on the readonly "table" in your schema? The problem is that every time I do a propel-insert-sql or a propel-build-all, my view gets destroyed, and I end up recreating it.

documented on: 12 June 2007, east3rd