Chris > I have never used an SQL view and would love to see a real-world > example of when a view should be used over, say, the CakePHP model > combined with associations. > > That's a pretty typical (and kinda sad) answer from someone with no real sql/ database background, and happy with MySQL, which just recently, (version 5) realised that Views, stored procedures, triggers, etc are MANDATORY in any serious database engine.( I use Postgresql exclusively with Cake, and am increasingly frustrated by the numerous limitations of cake versus a REAL database)
Let's see some REAL WORLD reasons to use views instead of (or beside) associations in cake: (1) The request is compiled in the DB engine instead of being interpreted each time. So optimization is done once and for all, hence faster, and results can be cached. So the first reason is performances. (2) Security, at different levels. Guest use can access views, or some views, and none of the underlaying tables. (3) Technicals reasons. The association have no ideas on how to handle a recursive relation. Example: SELECT a.suppliesactivityid, a.suppliesid, a.pending, a.date, a.qtyin, a.qtyout, a.productid, ( SELECT sum((sua.qtyin - sua.qtyout)) AS sum FROM sua WHERE ((sua.suppliesactivityid <= a.suppliesactivityid) AND ((sua.suppliesid)::text = (( SELECT suaview_id.suppliesid FROM suaview_id WHERE (suaview_id.id = 1) ))::text)) ) AS qtyonhand, a.setasbeginningbalance, a.tagged, a.seqno FROM sua a WHERE ((a.suppliesid)::text = (( SELECT suaview_id.suppliesid FROM suaview_id WHERE (suaview_id.id = 1) ))::text) ORDER BY a.pending, a.date, a.suppliesactivityid; It's a running total, very classical SQL. There are no other ways than a query in Cake. But it's a typically slow query. Using a view for this is THE way to do it. Justifying limitations in a product by doubting the usefulness of very standard SQL features is not giving confidence in the futures ameliorations to said product. In other words, I don't like this answer, it makes me doubt of my sanity in spending time with cake. Bernard --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---