http://forums.mysql.com/read.php?100,164811,164999#msg-164999

http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/

One can avoid those gotchas if one is careful. And, of course, I expect MySql to get better about handling views.

I take this approach, of using views, for 4 main reasons:

1.) Development time - it is quick to let “symfony propel:build-model” write all of my model code.

2.) Documentation - I’ll be handing this project over to other programmers. Rather than having a dozen custom JOIN methods scattered through a dozen model classes, future programmers can look in schema.yml and see every JOIN that exists in the application.

3.) Optimization - for reasons of performance, it’s important to build an index on every column that you use in a JOIN, or in a WHERE clause. In the past, I’ve found it difficult to discover which database table columns were used in WHERE clauses. That is, for years, I would write software, and then try to optimize database performance, which then entailed research into how the database is actually being used. This sometimes gets complicated on projects where multiple programmers were working. Keeping all such data in schema.yml makes things easy (you may regard this as a repeat of #2).

4.) Portability - repeating part of #2 - I understand what you are trying to say about portability. Your point is “What if we later move to a data storage technology that doesn’t support JOIN syntax?” For instance, what if in the future we decided we wanted to keep all of our data in a big XML flat file? XML files do not have VIEWS, so what are we going to do then? But I see this issue exactly the opposite way that you see it. Our software is expecting some kind of view that holds a particular kind of data. The schema.yml is the ideal place to record what kind of data the software is expecting, or rather, the views which the software assumes it can rely upon.

Suppose that 4 years from now the decision is made to move to a XML flat file. Suppose I’m no longer around, and different programmers are in charge of the transition. They should be able to take one glance at schema.yml and see exactly what views of data the software is expecting. Keeping this data all in schema.yml seems much more portable to me than having a dozen or so custom JOIN methods, in a dozen different models, which might need to be re-written.

Of course, no one is putting a gun to our head and telling us to move to a flat XML file. That is the worst case scenario, and I use it only to address your concerns. What is more likely is that, if the site scaled up and became huge, we might move to a RDBMS that had a more robust handling of VIEWs. For instance, maybe the decision would be made to move to Oracle. Then using a VIEW would offer a big performance advantage over using a JOIN.

You’ve probably heard the idea that JOINs Are Evil
If the site got really huge, then we would look for ways to de-normalize our data in ways that allowed for better performance. We might end up relying on temporary, pre-joined table for everything, possibly using an RDBMS that makes this easy (again, Oracle comes to mind). Or, maybe, we would look into the more radical approaches now being explored by technologies like CouchDb or Hadoop. However, this is not a problem that we would try to solve in a method in our model class. The problem eventually becomes bigger and more fundamental than anything you can address in your model class.

Rock on.
Daniel

Best of all, queries like that should even be abstracted to the AnswersTotalsUsersPeer class, just to have a AnswersTotalsUsersPeer::doSelectByHighestPrizeTotal($request->getParameter('limit', 25))

Better action logic. Better reusability.

Symfony is great for sure and like you, I don’t think WP is flexible enough, with good performances, to do such things.

However, it’s quite strange you create a view, and add some logic in database against managing logic within the code. Which is easier to deploy and maintain.