I think we have beat this to death... but I will add a couple more
things.
> I agree on general principle. My question is about the wisdom of using
> db views within cake because it breaks the relationship model (for the
> want of a better term). And I'm not sure how good or bad that is.
Using a view does not break the relationship model, it simply as
another model to the relationship.
> 4) It is not possible insert records into the VIEW, and I don't think
> updating the VIEW would ever be possible or even make sense.
It is possible to update/add records in some views. It depends on the
database, and the view. However, I would highly discourage this!
One very compelling reason to use (or not to use) a view, is
performance. Using Ralph's view example; It is probably faster to use a
view and let MySQL do the 5 joins and return the result, as opposed to
cake making 6 database calls. However, keep in mind that you might not
need all 5 joins every time. Using cake, you can unbind your
relationships at run time, reducing extraneous database work. But the
view will have the overhead of all five joins every time it is
accessed.
Of course my blind performances comparisons may not apply to your
situation. It would depend on the number of records, number of indexes,
types of data, hardware architecture, and the database server.
Finally, in as much is a view is only a convenience, it should only be
used if it simplifies your project. If you find your self writing a
complex join, and using that sql through out your project, then it
might be a good idea to take that complexity and put it in a convenient
central location (the database).
sc
On Nov 19, 2:24 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
> I used a sql VIEW as the basis of an ordinary model in my first Cake
> project. It turned out to be helpful in reducing the complexity of some
> queries, and just generally making things easier to understand. I think
> VIEWs are a useful tool to use, but not a panacea for anything in
> particular. Whatever can be accomplished with a VIEW can also be
> accomplished without a VIEW, as far as I can see.
>
> I don't agree that sql VIEWs would necessarily be non-generic. An
> unadorned VIEW is pretty standard, I think.
>
> Here's an example of a VIEW definition in Mysql. I've added extra blank
> lines to clarify the structure. This particular VIEW is conceptually
> quite simple -- just a multi-table natural join -- but unfortunately
> SQL has a way of making things complicated.
>
> In fact, that's a good reason to consider using VIEWs. Complexity is
> hidden inside them, potentially making other parts of the the
> application simpler.
>
> ---------------------------------------------------------------------------------------------------
>
> CREATE VIEW courses_students_teachers_scheduleblocks
>
> AS
>
> select concat(courses_students.course_id,courses_students.student_id,
> courses.teacher_id,courses.scheduleblock_id) AS id,
>
> courses.scheduleblock_id AS scheduleblock_id,
> courses.id AS course_id,
> courses.subject AS subject,
> teachers.id AS teacher_id,
> teachers.teacher_fname AS teacher_fname,
> teachers.teacher_lname AS teacher_lname,
> students.id AS student_id,
> students.lname AS student_lname,
> students.fname AS student_fname from ((((courses_students join
> students) join courses) join teachers) join scheduleblocks)
>
> WHERE ((courses.id = courses_students.course_id) and
> (courses_students.student_id = students.id) and (courses.teacher_id =
> teachers.id) and (scheduleblocks.id = courses.scheduleblock_id));
>
> ---------------------------------------------------------------------------------------------------
>
> And here is the corresponding model:
>
> ---------------------------------------------------------------------------------------------------
>
> class CoursesStudentsTeachersScheduleblock extends AppModel
> {
> var $name = 'CoursesStudentsTeachersScheduleblock';
>
> var $belongsTo = array(
> 'Scheduleblock' =>
> array('className' => 'Scheduleblock',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Course' =>
> array('className' => 'Course',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Teacher' =>
> array('className' => 'Teacher',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> 'Student' =>
> array('className' => 'Student',
> 'conditions' => '',
> 'order' => '',
> 'foreignKey' => '',
> 'counterCache' => ''),
>
> );
>
> }------------------------------------------------------------------------------
>
> Notes:
>
> 1) Mysql version 5 is necessary for using VIEWs. Version 4 doesn't
> support them.
>
> 2) One has to create a primary key (preferably named id) in order for
> the usual Cake model apparatus to work. For a sql VIEW, this has to be
> done by including one or more primary keys in the field list for the
> VIEW. In the example, note that we concatenate several individual
> foreign keys to form a single primary key named id for the VIEW:
>
> ------------------
> select concat(courses_students.course_id, courses_students.student_id,
> courses.teacher_id, courses.scheduleblock_id) AS id
> ------------------
>
> 3) As we did above for the primary key, we use field name aliases to
> specify the VIEW's interface to the outside world. Thus in the above
> definition of the primary key, other code that uses the VIEW will not
> be able to see courses_students.course_id or any of the other three
> components of the key. Only the composite key named id will be visible
> to the outside world, and it will be unique over all the possible
> (pseudo) rows of the VIEW.
>
> 4) It is not possible insert records into the VIEW, and I don't think
> updating the VIEW would ever be possible or even make sense.
>
> I hope the above is helpful. Any comments, especially corrections,
> would be appreciated.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
