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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
