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
-~----------~----~----~----~------~----~------~--~---
